How would you like to quickly and easily identify your slowest SQL queries? It's one of the things that you can do with Dynamic Management Views and their associated Dynamic Functions.

Quick question

Do your SQL queries run too fast?

I thought not!

Introduction

How would you like to quickly and easily identify your slowest SQL queries?

This will allow you to focus your attention on those areas that will have the biggest impact on improving performance. Or perhaps you want to identify any missing indexes that could significantly improve the performance of your SQL queries? Or maybe you want to identify what SQL queries are currently running to determine if any troublesome blocking is occurring?

All these things and more are possible with the use of Dynamic Management Views (DMVs), and their associated Dynamic Functions (DMFs). DMVs have existed since SQL Server 2005, they work by examining SQL Server's internal data, and are typically used to diagnose problems, help with monitoring and aid in suggesting improvements to performance problems. For a fuller explanation see DMVs at a glance.

As an example, when you run a query, SQL Server records internally data relating to the total duration of the query, how much time was spent on the CPU, how much time was spent waiting or being blocked, the number of reads and the number of writes, and much more. Examining this internal data, we can determine which lines of SQL are taking the longest time to run. In this article I will provide a script that will allow you to determine your slowest queries, thus providing a starting point from which you can start to improve the performance of your SQL queries.

Finding your slowest queries

The following SQL (also available in the Codebin) will identify the 20 slowest queries on your server:

Click to enlarge

The script starts with the statement SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. The purpose of this is to ensure the SQL that follows does not take out any locks and does not honour any locks, allowing it to run faster and without hindrance.

Each time a stored procedure, function or a batch of SQL is executed, metrics about each individual query within it (the parent query) is recorded. These metrics can be accessed by the DMV sys.dm_exec_query_stats.

These metrics include total duration (total_elapsed_time), number of times the query has executed (execution_count), together with details of the offsets of the individual query within the parent query. These offsets can be used to extract the text of the individual query from within the parent query.

The DMV sys.dm_exec_query_stats is joined with its associated DMFs via the CROSS APPLY statement. The CROSS APPLY statement can be thought of as a join to a table function that in this case takes a parameter. Here, the first CROSS APPLY to the DMF sys.dm_exec_sql_text, takes a parameter (sql_handle) and retrieves the text of the parent query. The second CROSS APPLY to the DMF sys.dm_exec_query_plan, takes another parameter (plan_handle) and retrieves the cached plan associated with the parent query.

To extract the text of the individual query from within the parent query, which the metrics relate to, the offset values are applied to the text returned from the DMF sys.dm_exec_sql_text.

The reason I've included the cached plan in the output is it provides a great resource for discovering why a query is running slowly. The cached plan contains low level details of how a query is executed, for example, is a table scan or index seek being used? Are there any missing indexes? Are the statistics out of date? The query's cached plan is output as XML.

The results of running this DMV query are sorted by the total_elapsed_time in descending order, thus reporting on the slowest queries first. To limit the amount of output, the TOP 20 statement is used to report the slowest 20 queries only. Running the slowest-queries query on my server gives the results shown in Figure 1.

Click to enlarge

The results show the cumulative impact of the slowest individual queries, within a stored procedure, function or batch of SQL. Knowing the slowest queries will allow you to make targeted improvements, confident in the knowledge that any improvement to these queries will have the biggest impact on performance.

Slow queries can be a result of having incorrect or missing indexes. It is possible to use the missing indexes DMVs to discover these missing indexes, but that's another article...

Conclusion

DMVs provide a quick and easy way to investigate performance problems, such as identifying your slowest queries. There really are a great number of SQL Server secrets that DMVs can reveal. For further information, see my recent book SQL Server DMVs in Action which contains more than 100 useful scripts.