Hello Geeks and welcome to the Day 35 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.

Today I am going to talk about how you can collect execution statistics of a particular procedure. Sys.dm_exec_procedure_stats is a DMV which is very useful to track trends of execution for procedures. As the name suggests it is a stats DMV. We have observed in my past blogs that most of these stats DMVs are cumulative. So to make sense out of the data in these DMVs we need to see the trend.

We should collect data from sys.dm_exec_procedure_stats at regular intervals to see how a procedure is executing. This helps in correlating to the problems on the server/database. Let me use this DMV to see how a procedure is behaving and collect its stats.

To make the collection of data from sys.dm_exec_procedure_stats I will be dumping the data first. Then I will update the object name off the collection part. This way I would make this more efficient. One common mistake with the stats collection is not having the object name saved. When an object is dropped and recreated the object ID changes. The previous ID from the stats is no longer valid. You cannot compare the performance before and after you fix anything in the procedure code. So saving the object name is always preferable.

As a first step let’s create the collection table.

Now create a procedure to collect the stats at a predefined frequency and run it through a scheduled job.

I have a procedure named usp_getPubLogger_prc which I will execute in below sequence to collect data from sys.dm_exec_procedure_stats. This is just a simple procedure to select all records from a table.

Now I have my data collection on procedure stats from sys.dm_exec_procedure_stats. Let me run the below query to see how my procedure executed in between each collection.


From the above out put you can observe the physical reads increased in my execution between 42nd and 45th second. So I can say something has cleared my table data from the buffer pool. You can track such trends and see for any pattern in a procedure behavior using sys.dm_exec_procedure_stats.

Tomorrow I will be covering another execution related DMV. So, 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