From the development perspective I often have to perform an analysis of a database application. More often than not this entails looking at a running system and ensuring that the application queries are behaving as expected. As such, I thought it would be worthwhile sharing some TSQL scripts that I have been using over the years for tuning SQL Server queries. So what I am generally looking for are:
- Long Running Queries – which queries are taking the longest to run
- CPU Intensive Queries – which queries are consuming the most CPU per execution
- Worst Case Total CPU – which queries are consuming the most CPU across all executions
- Most IO Intensive Queries – which queries consume the most read/write operations
The idea behind looking at these queries is to analyse the execution plan and review whether the query would benefit from being refactored or whether there are indexes that would enable the query to perform better.
The basis of this analysis are some queries based on the SQL Server Dynamic Management Views (DMVs). For performing CPU based analysis, I have been using the following DMV based query:
The basis of this query is that it shows the top 20 queries that consume the most CPU resources. The query can be modified to show queries that have consumed the most worker time or the most worker time per execution. It is also sometimes useful to see which queries have been executed the most, as you will want this to be highly performant. Just changing the ORDER BY allows one to tweak the queries one wants to analyse.
This query provides 2 critical pieces of information for query analysis. Firstly the query_text is returned so one can see what has been executed. The other critical piece of information is the actual query plan. This is returned as an XML document. Having the XML is useful in itself, however the fact one can save the XML as a file with a .sqlplan extension makes this XML invaluable.
Once the .sqlplan file has been saved, opening this file in SQL Server Enterprise Manager provides one with a graphical interpretation of the executed query. This enables one to easily analyse the actual query execution plan.
The other useful, but similar query, is the one to analyse IO based metrics:
Once again one can tweak the ORDER BY to analyse queries which consume the most read or write operations. For performing a quick IO based query analysis I have found it useful to calculate the total query IO based on a weighting of reads to writes; weighted IO = (writes * weighting) + reads. In this case I have provided a weighting that assumes a read is 5 times more expensive than a write.
Hope you find these DMV based queries useful, as they have held me in good stead over the years.
Written by Carl Nolan