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.
Written by Ian Stirk
Do your SQL queries run too fast?
I thought not!
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.
DMVs at a glance
Dynamic Management Views (DMVs) give you a way to access information about the way your database is working within SQL Server. When you run a query on a SQL Server database, various pieces of information about the query and its performance are stored by SQL Server, and can be accessed by you. DMVs are basically SQL views that can be used to analyse queries with the aim of improving performance, troubleshooting problems, or gaining a better insight into how SQL Server works.
To give you a better idea of just how useful DMVs can be, when you run a query SQL Server records the following information:
■ The query’s cached plan (this describes at a low level how the query is executed)
■ What indexes were used
■ What indexes the query would like to use but can’t, because they’re missing
■ How much I/O occurred (both physical and logical)
■ How much time was spent executing the query
■ How much time was spent waiting on other resources
■ What resources the query was waiting on
If you have a query that isn't running quite how you’d like it to, finding out that it would have liked to use the Product index but couldn't could save you hours of investigation. DMVs give you a great view of what really happened rather than what you thought would happen.
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
Figure 1: Identify the slowest SQL queries on your server, sorted by duration.
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...
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.