SQL Server Profiler and traces are two of the most feared and misunderstood parts of SQL Server.
SQL Server Profiler is an application -- included in the Workgroup, Standard and Enterprise editions of SQL Server -- that provides a user interface to configure and run a trace. (While it can also be used to connect data from the Web and SQL Express editions, the application isn't included with their installers.)
A trace lets you look at the commands that have been executed against the database server. You can set filters to only receive the data relevant to your problem.
SQL Server Profiler has a large number of columns that can be returned when a trace is setup. The specific columns vary depending on the events monitored. For example, events that specify a query has started do not include the CPU and duration values, as those values can only be included when a query is complete.
When should I use SQL Server Profiler?
There are many instanc...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
es for using SQL Server Profiler.
The first is when you need to see exactly what commands and parameters an application is running against a database. Although you can guess what is being sent to the database, until you see what the database sees, you can't be sure. This works just as well for dynamic SQL embedded in the application, or parameterized stored procedures.
SQL Server Profiler can also be used to determine which commands are taking the most resources or time to run. To do this, set up a filter on the runtime of the query (actually the duration column) for any queries that take more than 20 seconds (since the inputs for time are in milliseconds, it is technically 20,000 milliseconds). This will give you a good idea of the longest running queries. If you get too much data back, this means you have a lot of work to do. Increase the filter to a higher number (use the run times that came back to decide how high to start) and use these long running queries to figure out what to attack first.
Another place to use SQL Server Profiler is with SQL Server Service Broker. The Service Broker doesn't log many error messages into the ERRORLOG, and since everything happens outside the user's connection, nothing can be returned back to the user when there is a problem. By monitoring the Service Broker events, you will know pretty quickly if something is wrong.
These are only a few examples of when SQL Server Profiler should be used. Listing all the times the application would be helpful could be a book in itself.
What is SQL Server Profiler showing me?
An example of a trace that returned a lot of information is shown in Figure 1. For this trace, I started with the Standard template (the default for a new trace) and then I removed all the selected events except for RPC:Completed and SQL:BatchCompleted. Next, I checked the TextData field for RPD:Completed and set a filter looking for the string value %1245305%. (Remember to include percent signs (%) if you want to use the LIKE operator in your filter.)
Figure 1: Data returned from a trace in SQL Server Profiler (Click to enlarge)
[IMAGE]
As you can see, this filter returned several records. The Duration column shows that two queries took more than one second to execute, which means that these queries may need to be looked at.
Since I am familiar with the production database, I know that the second query (starting with declare @p18 bigint) is complex with many large joins, so 1.1 seconds of runtime is acceptable.
The query that took 2.2 seconds, however, is very small query and shouldn't take that long. To get the execution plan for this query, copy and paste it into Management Studio and then run the query. (You can get text execution plans in SQL Server Profiler by monitoring the Showplan Text or Showplan XML events.)
The execution plan performs many index scans, as shown in Figure 2. Since the lines to the left are much thicker than the ones to the right, index adjustment may need to be made. This may also mean some statistics are out of date.
Figure 2: Index scans in an execution plan (Click to enlarge)
[IMAGE]
Without SQL Server Profiler, it would have been difficult to find this poorly running query. The screen that this query is a part of fires off eight different stored procedures. I would have had to run this ASP.NET application in a debugger, looked at all the parameters and then put together what I thought the website was sending to the database. I would have had to do this with every procedure to ensure I got the right parameters for all of them, and then I would have had to run the procedures several times to get an idea of what the customer saw. But by running a SQL Server Profiler trace, I was able to find a problem query in my database within a few seconds.
While there are many horror stories about people firing up SQL Server Profiler and having it crash the production SQL Server, this is simple to prevent -- just avoid running SQL Server Profiler on the production server. To avoid further complications with the application, don't monitor all transactions on a high transaction server. This impacts the servers' performance, the network between you and the server, and kills the machine running SQL Server Profiler.
ABOUT THE AUTHOR:
[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.
Check out his blog:
SQL Server with Mr. Denny
