This morning I was writing a script to identify some stored procedures that I felt may benefit from tuning as part of this script I referenced the very useful DMV sys.dm_exec_procedure_stats. (I will post the script later)
If you have not used this DMV before I suggest that you take a look in BOL as it returns some really great information that can help you identify issues with IO and CPU. As a DBA you may want your developers to be able to view this information, if this is the case then you are going to need to grant the “VIEW SERVER STATE” permission. Alternatively as a conscientious developer you may want to ask your DBA to grant you these permissions so that you can ensure your code continues to perform well.
The real reason for this post is that I appear to have found either an error within BOL or the DMV itself. BOL states:
Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.
It also has a note:
An initial query of sys.dm_exec_procedure_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.
However when I run a simple group by on database_id and object_id having count(*) > 1 I am receiving rows back which goes directly against BOL. To make sure that it wasn’t just the inital query I ran the same GROUP BY statement several times, each time receiving the same results.
In an effort to find out what could have caused this I have posted a question on the popular community site SQL Server Central. If you have found a similar issue or know what the cause/solution might be then I would be interested in hearing from you.
I have now raised this as a bug on the Microsoft connect site: follow the progress here.
Hey Rich, great catch!
It’s attention to detail like this which distinguishes you from the dusty jobsworths
Cheers
ChrisM
I posted this on the SSC site as well: From my own observations, this appears because there are multiple plans in the cache for the procedure. If you query for the entries that have duplicate object_id’s, you will see that each entry has different plan_handles.
After reading Robert’s post I rechecked the code and found that the plan handle was not being taken from the procedure_stats DMV but a temporary table I had used to earlier. Having changed the code the plan handles are showing different values but the sql handle stays the same.
So whilst my initial diagnoses of the issue may have been a slightly incorrect the DMV does not behave the way BOL states it does which may have an adverse affect peoples scripts. Definitely something to be wary of. Hopefully Microsoft will make an amendment in the next release of BOL to something along the lines of:
“The view contains one row per cached plan for each stored procedure in the current cache, and the lifetime of the row is as long as the stored procedure remains cached.”
This post has also been posted on SSC for a wider audience to appreciate.
The following has been added by Microsoft:
The DMV returns one row per stored procedure compiled plan. It is possible to that the same SP has multiple plans in cache (see blogs.msdn.com\sqlprogrammability, under procedure cache section for more information). You can also check the plan attributes DMV to determine why you may have more than 1 plan per proc. Thanks for your feedback.
I have received confirmation from Microsoft that they will be changing their documentation due to the issue that I raised:
“We will make the documentation changes to reflect the DMV behavior. There is no product issue as such. This feedback item has been moved under documentation also.”