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.
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.
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.