How to Identify Slow Running Queries
With SQL Server 2005 you have a couple of different options. The first option, which is also available in other versions of SQL Server is SQL Server Profiler. Additionally with SQL Server 2005 you have the “sys.dm_exec_query_stats” DMV. Let me review how both of these options can be used to identify your slow running queries.
SQL Server Profiler is a GUI tool that is easy to use. Profiler can be used to display CPU, I/O and duration of SQL Server TSQL statements being processed on a SQL Server instance. In order for Profiler to be used to find your slow running queries you need to create and start a profiler trace. This can easily be done using the “New Trace” item. Profiler has a “TSQL Duration” template that can be used to find your long running queries. But if you want to display CPU and I/O with those long running queries you will need to create your own Profiler trace, or modify the columns selected with the “TSQL Duration” template. You can modify the “TSQL Duration” template by click on the “Event Selection” tab, then to select CPU, and I/O by just click on the check box under the “CPU”, “Reads”, or “Write” columns. For more information about how to use SQL Server Profiler please refer to Books Online.
If you have SQL Server 2005 you can use the “sys.dm_exec_query_stats” DMV to also identify the long running queries. This DVM can be used to return performance statistics for cached query plans. Below is an example of how to return a number of different performance measurements for each cached plan.
SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
– qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;
With this query I have calculated the average elapsed time then ordered the output so the query with the worse average elapsed time is displayed first. Elapsed time is a good measurement to use for identifying poor performing queries because this is the actual clock time it takes to process a query.
By using profiler or the DMV query you can determine your worst performing queries based on duration/elapsed time. Once you have identified the worse performing queries you can then focus your work on optimizing these queries. By optimizing the worse performing queries you will get the biggest bang for your effort and improve the overall performance of your application with the least amount of effort.
Tools to help optimize query
When optimizing a query you need to go through a repetitive process of reviewing performance information after each change. Doing this review allows you to determine if your enhancement has improved, or made your query’s performance worse. There are three measurements that are useful in determining the performance of your query: Elapsed Time, I/O and CPU. Let’s review different methods to measure each of this for a given query.
Elapsed Time
The length of time a query takes to run is known as elapsed time, also known as clock time. Many things can affect the elapsed time of a query. Elapsed time is a excellent benchmark measurement to gauge how will a query performs because it measures how long a user would have to wait while SQL server processes a specify query. So what are the different ways you can measure the elapsed time of a TSQL statement?
The first method you can use is the “elapsed time” that is displayed in the query window within SQL Server Management Studio when a batch is executed. This would be the time that I’ve circled in the screen shot below:
Here I have executed a loop that adds 1 to a variable 100 million times. Note the elapsed time in the lower left hand corner that I have circled in red. If you are running your slow queries in SSMS you can use this elapsed time display as a measurement to identify how long your query ran. Each time you change your query and re-run it you can look at this number to see if your query ran faster.