Procedure Stats
-
Hi all,
I'm hoping that somebody may be able to explain an oddity I have spotted.
According to BOL sys.dm_exec_procedure_stats will only return one row per procedure:
However while writing a script to identify code that needed to be tuned, I came across some spurious results. At first I thought I must have an incorrect join but then ran the following code:
SELECT PS.database_id, object_id, COUNT(*) QtyFROM sys.dm_exec_procedure_stats PS
WHERE database_id <> 32767
GROUP BY PS.database_id, object_id
HAVING COUNT(*) > 1
ORDER BY 3 DESC
Which returned 10 rows.
The statistics are completely different as are the cache times but yet have the same memory address, create date, plan_handle and query_handle.
Has anyone else come across this at all? I have checked connect but could not find anything on there. If other people also have the same issue I'll add it as a bug.
The environment I first spotted it on is 2K8 EE SP1 with TDE enabed and have also noticed it on 2K8 DE SP1 with TDE.
Hope this helps,
Rich[p]
[/p] -
Multiple statements within a procedure? Query stats works that way, I'll admit I've never used procedure_stats.
Check one of the procedures that has multiple rows, see how many queries it has in it, see if that corresponds to the count you're seeing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Wow, a reply from Gail I'm honoured :pinch:
I've had a look at the counts on our main prod server and there are 28 (all returning 2 rows) out of several hundred SP's so I don't think it's that, but just to be sure could you clarify what you consider to be a statement? Do you mean only code that would affect or create statistics? i.e. I can ignore statements that populate variables for example?
I checked one of the sp's with only 1 row and that had several select statements populating variables in which to insert into more than 5 tables.
Some of the SP's appear to do different things based upon variables passed in so I guess it's feasible that it created different statistics for each, but then why show the same estimated plan?
Another thing I had noticed was when a developer ran a particular sp, I then tried to check the stats using this dmv and it did not appear, the same SP now appears although it has now been scheduled as a job and has run several times. My understanding is that if the plan is in the cache then the stats will be available for it. This appears not always to be the case.
Thanks for your reply, I really appreciate it.
You've got to love the SQL community!
Hope this helps,
Rich[p]
[/p] -
rdouglas66 (5/13/2010)
Wow, a reply from Gail I'm honouredWhy?
I've had a look at the counts on our main prod server and there are 28 (all returning 2 rows) out of several hundred SP's so I don't think it's that, but just to be sure could you clarify what you consider to be a statement? Do you mean only code that would affect or create statistics? i.e. I can ignore statements that populate variables for example?
A query, something that affects tables and can hence have page reads. Variable manipulation shouldn't.
Some of the SP's appear to do different things based upon variables passed in so I guess it's feasible that it created different statistics for each, but then why show the same estimated plan?
There's only 1 plan in cache (and procs that do different things depending on params can be a bad idea) I have a blog post on it.
Another thing I had noticed was when a developer ran a particular sp, I then tried to check the stats using this dmv and it did not appear, the same SP now appears although it has now been scheduled as a job and has run several times. My understanding is that if the plan is in the cache then the stats will be available for it.
Should be, unless something threw the plan out very quickly (stats update, schema change, recompile, etc)
As I mentioned, I use sys.dm_exec_query_stats, I've never actually used procedure stats, so I'm not familiar with what it shows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I've run another test with some rather bizarre results. For this test I used 2 EE instances (residing on different physical machines)
The following code was run both instances:
use msdbgo
SELECT name
FROM sys.procedures
WHERE object_id in (
SELECT object_id
FROM sys.dm_exec_procedure_stats PS
WHERE database_id = DB_ID('msdb')
GROUP BY PS.database_id, object_id
HAVING COUNT(*) > 1)
Server 1 - Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Sep 11 2009 14:30:58 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
No rows returned
Server 2 - Microsoft SQL Server 2008 (SP1) - 10.0.2734.0 (X64) Sep 11 2009 14:30:58 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
sp_verify_subsystems
sp_sqlagent_notify
sp_verify_job_identifiers
sp_post_msx_operation
sp_verify_job
sp_update_job
This on its own does not prove anything unless the duplicate rows from server two reside in the cache on server 1:
SELECT sp.nameFROM sys.dm_exec_procedure_statsps
INNER JOIN sys.procedures sp on sp.object_id = ps.object_id
WHERE sp.name IN
('sp_verify_subsystems',
'sp_sqlagent_notify',
'sp_verify_job_identifiers',
'sp_post_msx_operation',
'sp_verify_job',
'sp_update_job')
Returned:
sp_post_msx_operation
sp_update_job
sp_verify_job_identifiers
So I now have two servers running the same edition with the same level of patching returning different results for the same stored procedures shipped by Microsoft with their product.
Hope this helps,
Rich[p]
[/p] -
Connect time I think. There's either a bug in the docs or in the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/13/2010)
Connect time I think. There's either a bug in the docs or in the code.Thanks Gail,
If anyone is interested I have raised it on connect here: Bug ID 558937.
Hope this helps,
Rich[p]
[/p] -
I'll see if I can repo it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
That I could understand, different SET options so different plans and one entry per plan. However ...
rdouglas66 (5/13/2010)
The statistics are completely different as are the cache times but yet have the same memory address, create date, plan_handle and query_handle.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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."
Hope this helps,
Rich[p]
[/p] -
Microsoft have replied to the Connect Issue and have confirmed that they will be changing the documentation about this DMV.
Hope this helps,
Rich[p]
[/p] -
rdouglas66 (5/17/2010)
Microsoft have replied to the Connect Issue and have confirmed that they will be changing the documentation about this DMV.Thanks for the feedback.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply