In this Blog post, we are going to see How to find the Top 20 Worst Performing Queries in SQL Server. We can use the below query as Query Performance Insight/Monitor that is common for both On-premises SQL Database and Azure SQL Database.
SELECT TOP 20 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,DBID) + '.' + OBJECT_NAME(qt.objectid, qt.DBID) ,StatementText = SUBSTRING(qt.TEXT, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) ---- Query within the proc ,TextData = qt.TEXT ---- The SQL Text that was executed ,DiskReads = qs.total_physical_reads ---- The worst reads, disk reads ,MemoryReads = qs.total_logical_reads ---- Logical Reads are memory reads ,Executions = qs.execution_count ---- the counts of the query being executed since reboot ,TotalCPUTime_ms = qs.total_worker_time/1000 ---- The CPU time that the query consumes ,AverageCPUTime_ms = qs.total_worker_time/(1000*qs.execution_count) ---- the Average CPU Time for the query ,AvgDiskWaitAndCPUTime_ms = qs.total_elapsed_time/(1000*qs.execution_count) ---- the average duration to execute the plan - CPU and Disk ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.DBID) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.SQL_HANDLE) AS qt WHERE DB_Name(qt.dbid) = 'TESTDB' ----connect and give your db name here (cross db works fine in on-premises and not works on Azure) --ORDER BY qs.total_worker_time DESC ---- (Most CPU usage = Worst performing CPU bound queries) --ORDER BY qs.total_worker_time/qs.execution_count DESC ---- highest average CPU usage --ORDER BY qs.total_elapsed_time/(1000*qs.execution_count) DESC ---- highest average w/ wait time ORDER BY total_logical_reads DESC ---- (Memory Reads = Worst performing I/O bound queries)
In the above code, the Columns used are below mentioned with short description-
- ObjectName = SP_Name (mostly)
- StatementText = Query within the SP
- TextData = The Whole SP where Query works
- DiskReads = The Worst Reads, Disk Reads
- MemoryReads = Logical Reads are Memory Reads
- Executions = The counts of the query being executed since reboot
- TotalCPUTime_ms = The CPU time in milliseconds that the query consumes
- AverageCPUTime_ms = The Average CPU Time in milliseconds for the query
- AvgDiskWaitAndCPUTime_ms = The average duration to execute the plan – CPU and Disk
- MemoryWrites = Logical Writes are memory writes
- DateCached = Query cached time since reboot
- DatabaseName = Name of the given Database
- LastExecutionTime = Query last executed time after reboot
We can change the ORDER BY clause to order the query with different parameters like
1. Most CPU usage/Worst performing CPU bound queries,
2. Highest Average CPU usage,
3. Highest Average wait time,
4. Memory Reads/Worst performing I/O bound queries.
DMV – sys.dm_exec_query_stats
For finding poorly performing queries, we need to analyse one of the most important DMV (Dynamic Management View) named ‘sys.dm_exec_query_stats‘ where SQL Server stores run time statistics about our execution plans.
The functions of DMV is to return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
For every cached execution plan, SQL Server gives us information about the metrics like
how much CPU time and how much I/O, the specific query consumed etc.,
Thus, we saw how to find the Top 20 Worst Performing Queries in SQL Server. As already mentioned, We can use the above query as Query Performance Insight/Monitor since it is common for both On-premises SQL Database and Azure SQL Database. The above query is also Unit tested in both Azure SQL and On-Premises Databases.