dm_exec_query_stats vs. dm_exec_procedure_stats

Question

Post reply

dm_exec_query_stats vs. dm_exec_procedure_stats

Steve Malley

Ten Centuries

Points: 1204
More actions
January 8, 2013 at 10:21 am

#266918

Hi.
I ran several queries this morning to gather a baseline of performance data on a system. dm_exec_query_stats has 840k records. dm_exec_procedure_stats about 500.
Does this mean that all statements in procedure_stats are 'echoed' in query stats?
My intention is to gather stats related to procs and queries [dynamic sql]. Suggestions? I wish to take a baseline to compare to stats obtained later.
Links, thoughts, suggestions appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Steve Malley (1/8/2013)
Does this mean that all statements in procedure_stats are 'echoed' in query stats?

Yes. Procedure stats is a subset, queries that are also procedures

My intention is to gather stats related to procs and queries [dynamic sql]. Suggestions? I wish to take a baseline to compare to stats obtained later.

objtype column in sys.dm_exec_cached_plans

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Malley Ten Centuries Points: 1204 More actions · Answer 2

I am a little puzzled. You remarked that procedure_stats was a subset.

I presumed the reason there were 840,000 query_stats and only 500 proc_stats was because of a one-to-many between proc_stats and query_stats. Am I not looking at this correctly? My understanding [good/bad] is that every cached statement is in query_stats. Every cached proc is in proc_stats. Also, I believe query_stats includes dynamic SQL too.

SteveHood79 SSC Veteran Points: 267 More actions · Answer 3

Query Stats has it all, and if you join to sys.dm_exec_cached_plans you can find the object type and filter it down. I actually recently put a blog post together on capturing this information and have it syndicated to SSC here: http://www.sqlservercentral.com/blogs/simple-sql-server/2013/08/27/query-stats/[/url]

Let me know if this helps. This looks like it's exactly what you're looking for, and is something that has saved me a TON of time since I started using it.

Thanks,

Steve