Performance Tuning Using SQL Profiler

By K. Brian Kelley

Performance Monitor, a free tool with the Microsoft Windows operating systems, is an invaluable tool when locating performance bottlenecks. While this may be the primary tool of the system administrator and a valuable resource for the SQL Server DBA, there are other tools a SQL Server DBA should be aware of which go beyond hardware bottlenecks. Being able to see query execution plans with Query Analyzer, SQL Server Management Studio, or our favorite 3rd party product can be equally useful to tune poorly performing queries. Traces, however, whether server-side or through a tool like SQL Profiler, can be the most important tool of all. This article introduces the use of SQL Profiler and serverside traces for performance monitoring/tuning.

Long Running Queries

One of the first things for which most DBAs use SQL Profiler is to capture what queries are running too long on SQL Server. These queries can often point us to where we need to tune our databases. For instance, Figure 1 shows SQL Profiler (SQL Server 2000 version) being used with the SQLProfilerTSQL_Duration trace template. I’ve also specified a filter which only shows those SQL batches which last over 500 milliseconds.

Figure 1: SQL Profiler Trace for Long Running Queries Queries Which Run Repeatedly

While finding long running queries is important, we can use SQL Server Profiler for much more. There’s an old adage - to put the most effort into where we’ll get the biggest impact. A query may take a while to run but if it only runs once a day while another query runs relatively quickly, but thousands of times an hour, we may improve performance more by concentrating on that second query.

Figure 2: SQL Profiler Trace of All Queries

Figure 2 shows a trace which uses the SQLProfilerTSQL trace template. What is shown is when a batch starts and what that batch contains. We now know what queries are being run and how often they occur. Again, this is all very useful information. However, we’re not done with using Profiler to research performance issues. In my experience, one of the biggest banes to performance has been where locks on database objects prevent other data operations from being carried out. Profiler can help us see these issues, too, but first we must be able to construct our own Profiler trace templates.

Building Your Own Trace Template

I’ve used two different trace templates to show two different things: long running queries and all queries which do run. While each of these templates records information of value, neither of them (or any of the other prepackaged trace templates) may capture the right mix of events and the appropriate columns for those events for your purposes. All is not lost, as SQL Profiler gives us the ability to build our own trace templates. There are two ways to approach building your own trace template. You can start from scratch with nothing selected or you can begin by modifying an existing template. Once you’ve built your trace template, you can save it to be re-used again. Creating a trace template is different between SQL Profiler for SQL Server 2000 and for SQL Server 2005. For SQL Server 2000, to start one from scratch, begin with File | New | Trace Template. To copy from an existing one, start with File | Open | Trace Template. Be sure to click the Save As button before starting your edits, however. When working with SQL Server 2005, start with File | Templates and then, depending on whether you want to start from scratch or start from an existing template, chose New Template or Edit Template, respectively. Again, if you choose Edit Template and you aren’t editing one of your own, click the Save As button on the General tab.

Looking for Blocking and Deadlocking

Consider the following scenario: an end user issues a SELECT * FROM SomeTable against a large table used for transactional purposes. This SELECT statement puts a shared table lock over the whole table which, while it’ll allow other SELECT operations, stops INSERT, UPDATE and DELETE statements in their tracks until the SELECT statement completes (this assumes the default of pessimistic concurrency control). SQL Profiler can help diagnose these types of blocking issues as well as deadlock issues. A deadlock is when two SPIDs have locks on a particular resource that the other SPID needs. Since neither operation can go forward, SQL Server will roll back one of them. Needless to say, this can cause unpredictable results for your applications. In order to track blocking and deadlocking, we need to create our own custom trace template. Key events we’re looking for are: Lock:Deadlock, Lock: Deadlock Chain, and Lock: Timeout. If we’re using SQL Server 2005, we have the option of adding Lock: Deadlock Graph (which produces an XML output of the deadlock) and Lock: Timeout (timeout > 0) for those lock timeouts where the timeout is greater than 0. The Lock: Timeout is good in conjunction with SQL:StmtStarting because we can associate what statements are seeing lock timeouts (using SPID to tie the two events together), which gives us an idea of what objects we’re having blocking issues on. This allows us to backtrack and see what statements are using those objects and holding the locks, which tells us what our blocking problems are being caused by. We can also add Lock: Acquired, but chances are likely we’ll receive too much information, even if we filter for the specific object, such as by using the ObjectID filter. Figure 3 shows just such an example with a single query against a table.

Figure 3: Lock:Acquired Events firing from Querying a Single Table

Therefore, monitoring Lock:Acquired is of very limited value unless we can carefully control what’s being executed on the SQL Server. While SQL Profiler is good for profiling deadlocks and lock timeouts, monitoring the locks themselves is probably better done as described in Microsoft KB article 271509, How to Monitor Blocking in SQL Server 2005 and in SQL Server 2000. This article describes using a stored procedure to monitor and report blocking periodically (sp_blocker_pss80), taking snapshots in time. While the information to parse through provided by this method can still be substantial, it is far smaller than if we tried to monitor individual locking with SQL Profiler or a server-side trace. Combining a SQL Profiler trace focusing on deadlocks and lock timeouts along with the steps in 271509 can help identify the source of blocking issues quickly. Figure 4 shows just a small excerpt of the sp_blocker_pss80 stored procedure when a blocking situation is captured:

Figure 4: Output from sp_blocker_pss80

As you can see, the script even tells us what queries are causing the blocking. Compare this with the results from SQL Server Profiler in Figure 5:

Figure 5: Blocking in SQL Server Profiler

As the results show, unless we’re dealing with a deadlock situation or the client has set a lock timeout value (by default this value isn’t set), we’re not going to see as much information in SQL Server Profiler as we would with the stored procedure and methodology provided in 271509.

Tracking Execution Plans

One thing SQL Profiler or a server-side trace can provide is the execution plans which were used at the time a query was executed. In order to do so, we have to create a custom trace and specify one of the following Events: SQL Server 2000:

• Execution Plan
• Show Plan All
• Show Plan Statistics
• Show Plan Text SQL Server 2005:
• Showplan All
• Showplan All For Query Compile
• Showplan Statistics Profile
• Showplan Text
• Showplan Text (Unencoded)
• Showplan XML
• Showplan XML For Query Compile
• Showplan XML Statistics Profile

In the case of the SQL Server 2000, these are the available events, whether using the SQL Profiler from SQL Server 2000 or 2005. Of course, the SQL Server 2005 events are available only with the SQL Profiler available from SQL Server 2005. If we try to connect with the SQL Profiler from SQL Server 2000, we get an error. If we are connecting to a SQL Server 2000 server, in order to obtain execution plan (show plan) information we must include the BinaryData column in order to get anything back. However, in doing so, the data is stored in a format which is unusuable outside of SQL Profiler. If we convert it to a trace table the BinaryData column is typed as an image column and so far as I am aware, Microsoft has not published publicly how to translate this information into a usable form. This is where the SQL Server 2005 options with XML are a huge boon. For instance, using the Showplan XML Statistics Profiler allows us to see the execution plan along with the values related to cost, etc. Since the results are in XML we can take this information and transform it as we need to in order to evaluate the various execution plans as they occurred. We can also take the whole conglomerate of execution plans and look for key objects which would potentially indicate poor performance, such as table or index scans. By collecting all the results and filtering through the data in this fashion, we’re not stuck going through each potential query one-byone to see where the execution plans indicate performance tuning is needed.

Converting SQL Profiler Traces to Server Side Traces

One of the hardest things to learn how to do is take the settings in SQL Profiler for events and columns and write a server-side trace that does the same thing. There are certainly benefits to a server-side trace. First and foremost, we don’t have to have a client actively running to capture such information. Second, we’re not going to miss events because of too much activity on the server. We can configure Server processes SQL Server trace data (SQL Server 2000) or Server processes trace data (SQL Server 2005) which forces the trace server side. Without this setting events may not be passed to the SQL Profiler client when the SQL Server is under heavy stress. This setting forces the trace handling back to the server, and if that’s what’s required we might as well build a server-side trace, in any case. In actuality, this is what SQL Profiler is doing, except it has a mech- anism to get the information from SQL Server to be able to display it visually. Extracting a server-side trace isn’t all that difficult because we can let SQL Profiler do most of the work for us. Once we get a trace set up just the way we want it, we can export the trace to a SQL script. This script can be the run on SQL Server to setup the trace so that we don’t have to have Profiler up and running. In SQL Server 2000’s version of SQL Profiler, this can be accomplished by File | Script Trace | and either For SQL Server 2000 or SQL Server 7.0. With SQL Server 2005’s version, we can script a trace by File | Export | Script Trace Definition | and either For SQL Server 2000 or For SQL Server 2005. Here is an excerpt from a trace definition of the Standard template (SQL Server 2005):
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 04/22/2007 10:56:08 PM */
/****************************************************/
— Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 — Please replace the text InsertFileNameHere, with an appropriate — filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension — will be appended to the filename automatically. If you are writing from — remote server to local drive, please use UNC path and make sure server has — write access to your network share exec @rc = sp_trace_create @TraceID output, 0, N’InsertFileNameHere’, @maxfilesize, NULL if (@rc != 0) goto error — Client side File and Table cannot be scripted — Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 14, 1, @on exec sp_trace_setevent @TraceID, 14, 9, @on As you can see, this trace definition includes the appropriate variable declarations that are needed for the trace, the actual commands to be run, as well as guidance on what we need to change in order to customize the script for our purposes.

Concluding Thoughts

SQL Profiler is an excellent tool for helping to diagnose performance issues with our SQL Servers. We can go a step further to run server-side traces, eliminating the need for this client tool to be up and open all the time. This article touches on how to begin using SQL Profiler and traces to help in our performance tuning, but due to the flexibility of traces in general there’s a lot more I didn’t cover. If you’re new to using this tool, I suggest breaking it out in a development or test environment and trying to cause the types of performance issues DBAs are called on to diagnose. Take the time to get SQL Profiler set up to show those events and get accustomed with how the results will appear. If you support both SQL Server 2000 and SQL Server 2005, spend some time with both versions of SQL Profiler, as there are some substantial differences between the capabilities of the two tools based on how the SQL Server database engines are instrumented in the two versions. Finally, when you are comfortable with using SQL Profiler, use it to generate your first few sets of server-side traces. As you get more comfortable with the stored procedures and functions related to traces, you may find you won’t need SQL Profiler very often, except to view data. However, in a crunch, remember how to let SQL Profiler generate the guts of a trace for you. It can be a great time saver.

Author Bio

Brian Kelley is a Systems Architect and DBA with AgFirst Farm Credit Bank and the regular security columnist for SQLServerCentral.com. He is also the author of “Start to Finish Guide to SQL Server Performance Monitoring” and the President of the Midlands PASS Chapter for South Carolina. You can contact him at brian_kelley@sqlpass.org.