Dynamic Management View · Performance tune · Query Performance · Real Scenario Logic's

Top 20 Poor Performing Queries in SQL Server

Introduction

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.

SQL Script

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)

Columns

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

ORDER BY

We can change the ORDER BY clause to order the query with different parameters like
ORDER BY
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.,

Conclusion

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.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

2 thoughts on “Top 20 Poor Performing Queries in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s