Being able to quickly analyze the workload and possible bottlenecks on a busy, mission-critical SQL Server is an extremely valuable skill, especially in a crisis. As I have blogged about before, both SQL Server 2005 and 2008 expose a wealth of very useful metrics that were difficult if not impossible to be aware of with previous versions of SQL Server.
In order to get the best results from these queries, you should run DBCC FREEPROCCACHE on your server a few minutes before you run them. Otherwise, the Age in Cache values will not be the same, which will skew the results of these queries. Of course, you should be aware for the effect of running DBCC FREEPROCCACHE on a production server before you do it.
The first query below tells you which stored procedures are being called the most often, which is good to know for baseline and troubleshooting purposes. Don’t be fooled into assuming that the SP that is called the most often is the most costly though. It may well be that you have other stored procedures that are not called as much, which are much more costly (in different ways) than the most frequently called SPs.
Query 2 shows the top 20 stored procedures sorted by total worker time (which equates to CPU pressure). This will tell you the most expensive stored procedures from a CPU perspective.
Query 3 shows the top 20 stored procedures sorted by total logical reads(which equates to memory pressure). This will tell you the most expensive stored procedures from a memory perspective, and indirectly from a read I/O perspective.
Query 4 shows the top 20 stored procedures sorted by total physical reads(which equates to read I/O pressure). This will tell you the most expensive stored procedures from a read I/O perspective.
Query 5 shows the top 20 stored procedures sorted by total logical writes(which equates to write I/O pressure). This will tell you the most expensive stored procedures from a write I/O perspective.
In an upcoming post, I will explain how to interpret the results of these queries, and more importantly, some steps to improve the queries that show up at the top of your lists.
Query 1
-- Get Top 100 executed SP's ordered by execution count SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.execution_count DESC
Query 2
-- Get Top 20 executed SP's ordered by total worker time (CPU pressure) SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.execution_count AS 'Execution Count', ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.total_worker_time DESC
Query 3
-- Get Top 20 executed SP's ordered by logical reads (memory pressure) SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads, qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.total_logical_writes, qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY total_logical_reads DESC
Query 4
-- Get Top 20 executed SP's ordered by physical reads (read I/O pressure) SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.total_physical_reads DESC
Query 5
-- Get Top 20 executed SP's ordered by logical writes/minute SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites', qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.total_logical_writes DESC
Hi Glenn,
I noticed that Query 1 returns a row for every statement in a stored procedure, and because of different execution paths within the stored procedure, it makes it difficult to tell the exact count for the stored procedure. Greg Larson provides a query that counts the number of executions (via the max use count from dm_exec_cached_plans, grouped by object name) at http://www.databasejournal.com/features/mssql/article.php/3687186.
If your stored procedures only have one statement in it, your Query 1 would be perfect! 🙂
Cheers,
Jim
Pingback: Five DMV Queries That Will Make You a Superhero in 2011! | Glenn Berry's SQL Server Performance
Pingback: Merrill Aldrich : Top Tools I Didn’t Know I Needed as a DBA
Pingback: Five DMV Queries « Manoj Negi