Hello Geeks and welcome to the Day 36 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

In the last post I have written on how to use sys.dm_exec_procedure_stats. This covers how to get procedure stats. The DMV I am going to talk about today gives you more granular information. Sys.dm_exec_query_stats gives the stats of statements executed inside the procedures. It also includes individual ad hoc statements. The details are saved in sys.dm_exec_query_stats till the plan exists in the cache.

I will edit the procedure I used yesterday. I will just add two select statements instead of one.

To collect the stats let me follow the same steps as I did for sys.dm_exec_procedure_stats. The collection table will hold all necessary information form sys.dm_exec_query_stats. We can use this when needed for troubleshooting.

Create collection table:

Create the procedure to collect stats. This should be run at regular frequency to collect data from sys.dm_exec_query_stats.

Now let us run the procedure couple of times. We will collect the stats in between from sys.dm_exec_query_stats.

To check the stats run the below query on collection table which has data collected from sys.dm_exec_query_stats. To get to see what we are interested in I filtered on sqlHandle for the procedure I ran.


This output shows stats for individual statements in my procedure. I can also get the plan_handle and see the individual statements plan using sys.dm_exec_text_query_plan. You can do more than you think you can with DMVs. So, continue to follow my series. Stay tuned. Till then

Happy Learning,

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook