Unanswered: Hunting down bad SQL query performance
Once you’ve fine-tuned your databases’ indexes, maxed out your hardware and gotten the fastest disks money can buy, you’ll hit a limit on how much performance you can squeeze from your SQL Server machines.
At some point you have to focus on fine-tuning the applications rather than beefing up SQL Server itself. That takes you into the somewhat tricky world of query analysis, where you try to identify database queries that -- because of the way they are written --aren’t performing as well as they could be.
SQL Server comes with an excellent tool, SQL Profiler, that’s designed to capture traces. Think of these as similar to a network packet capture: You’re actually capturing the raw queries being fed to SQL Server, along with information about their execution time. Using this raw data, you can spot bad SQL query performance, and then offer advice to the application developers on how to improve them.
When you open SQL Profiler, you’ll start by creating a new trace. Part of the trace definition is a list of the events that you want to capture. You’ll usually want to capture remote procedure call (RPC) events as well as Transact-SQL events, as these two event types represent the two ways queries can be submitted to SQL Server or stored procedures can be executed. I usually include the following event classes in my traces:
RPC:Completed. This is generated after a stored procedure is executed from an RPC and includes information about such parameters as the duration of the execution, the CPU utilization and the name of the stored procedure.
SPtmtCompleted. This is fired whenever a statement within a stored procedure finishes running and also includes data on metrics such as execution duration and CPU use.
SQL:BatchStarting. You’ll see this whenever Transact-SQL batches begin, including those inside and outside stored procedures.
SQL:BatchCompleted. This occurs when a Transact-SQL batch finishes; it provides data similar to the RPC and stored-procedure examples listed above.
Showplan XML. This gets you a graphical execution plan for a query—key to understanding how the query was executed and spotting performance problems.
Once your trace is set up, start capturing data. You’ll want to capture representative data, and often that means running the trace during production workloads. Be sure to capture to a file or SQL Server table that’s on a machine other than the one you’re analyzing so that the analysis itself doesn’t affect performance.
You’ll need to tell SQL Profiler which data columns you want to view; I usually start with this list:
These columns give me good insight into how long each query took to run, and I can often just skim through the Duration column looking for especially large values. You’ll want to focus on longer-running queries to see if you can improve execution time. The duration is shown in milliseconds (although it’s stored internally in microseconds), so don’t be alarmed if all of the values seem large at first.
Good Post Paul. Just my two cents to it
1. We should script the trace defination and run it as server side trace so as to minimize processing overhead by the application ( eg profiler).
2. We should write the trace data to file instead of a table. This would reduce overhead when trace writes to the table by eliminating the need to log each inserts in the trace table to the transaction log
And of course, it should be run on the server as you said.
Running SPtmtCompleted on a production server taking even 100 requests per second could generate a lot of output. Maybe even enough to begin impacting performance on the database server. I generally start with just RPC:Completed and SQL:BatchCompleted. this will give you an overview of what is happening, without bogging you down in too many details.
The duration column will tell you how long a stored procedure or batch ran for, but that number can be misleading in that if the client takes a significant amount of time processing a recordset, the duration will reflect that. In other words, a batch's duration is not complete, until the last record is sent to the client, and that client closes that request. A better measure of how badly a procedure or batch is running is the Reads column. If a batch uses say 100 reads, but took 10 seconds to complete, I suspect it is a front end problem of some sort. If a batch uses 10,000 reads, and is relatively consistent from run to run, then you are likely looking at a table scan.
Another thing to note along those lines, if you see one batch that uses 1,000,000 reads, and a second procedure that uses 10,000 reads, but the second batch is run 100 times more often than the first one, spend more time with the second one. The first is likely some CEO's report. If it runs once per day, it will not impact performance very long. Most people may refresh their screens, or curse at a delay in processing, but it won't happen again for a day. The second procedure is sapping performance all day long. Fixing that may only save each user half a second per transaction, but the throughput of the whole server could increase dramatically not just because that one procedure is faster, but because there are more resources to go around to the other procedures.