For Day 20 of this series, we start out with Query #46, which is Query Execution Counts. This query retrieves information from the sys.dm_exec_query_stats dynamic management view, the sys.dm_exec_sql_text dynamic management function, and the sys.dm_exec_query_plan dynamic management function about the most frequently executed cached queries in the current database. Query #46 is shown in Figure 1.
1: -- Get most frequently executed queries for this database (Query 46) (Query Execution Counts)
2: SELECT TOP(50) LEFT(t.[text][/text], 50) AS [Short Query Text], qs.execution_count AS [Execution Count],
3: qs.total_logical_reads AS [Total Logical Reads],
4: qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
5: qs.total_worker_time AS [Total Worker Time],
6: qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
7: qs.total_elapsed_time AS [Total Elapsed Time],
8: qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
9: qs.creation_time AS [Creation Time]
10: --,t.[text][/text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
11: FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
12: CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
13: CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
14: WHERE t.dbid = DB_ID()
15: ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
Figure 1: Query #46 Query Execution Counts
This query shows you which cached queries (which might be part of a stored procedure or not) are being called the most often. This is useful as a part of understanding the nature of your workload. Keep in mind that just because a query is called a lot does not necessarily mean that it is a key part of your workload. It might be, but it could be that it is not actually that expensive for individual calls or cumulatively. You will need to look at the other metrics for that query to determine that.
You may notice that I have one line of this query commented out. This is because Excel does not deal very well with large quantities of text or XML. If you are working with this in real time, you should probably uncomment that line, so you see the extra information that it retrieves.
Query #47 is SP Execution Counts. This query retrieves information from the sys.procedures object catalog view and the sys.dm_exec_procedure_stats dynamic management view about the most frequently executed cached stored procedures in the current database. Query #47 is shown in Figure 2.
1: -- Top Cached SPs By Execution Count (Query 47) (SP Execution Counts)
2: SELECT TOP(100) p.name AS [SP Name], qs.execution_count,
3: ISNULL(qs.execution_count/DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute],
4: qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.total_worker_time AS [TotalWorkerTime],
5: qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time],
6: qs.cached_time
7: FROM sys.procedures AS p WITH (NOLOCK)
8: INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
9: ON p.[object_id] = qs.[object_id]
10: WHERE qs.database_id = DB_ID()
11: ORDER BY qs.execution_count DESC OPTION (RECOMPILE);
12:
13: -- Tells you which cached stored procedures are called the most often
14: -- This helps you characterize and baseline your workload
Figure 2: Query #47 SP Execution Counts
This query shows you which stored procedures with cached query plans are being called the most often. This helps you understand the nature and magnitude of your workload. Ideally, you should have a general idea of what your normal workload looks like, in terms of how many calls/minute or per second you are seeing for your top stored procedures.
If this rate suddenly changes, you would want to investigate further to understand what might have happened. Understanding which stored procedures are called the most often, can also help you identify possible candidates for middle-tier caching.