Dynamic Management Objects/ Presentations/ SQL

Correlate Session State Information With Cached Query Plans

4 Comments

Cached query plans are used only if the the session settings of the current (calling) session matches the settings that were in place in the original session of the query that created the cached plan to begin with.  So, how do you verify that the session settings match those of the cached plan?  Dynamic Management Objects of course.

SELECT DECP.refcounts, DECP.usecounts, DECP.size_in_bytes, 
  DECP.cacheobjtype, DECP.objtype, DEQP.query_plan,
  DEPA.attribute, DEPA.value
FROM sys.dm_exec_cached_plans DECP
  CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
  CROSS APPLY sys.dm_exec_plan_attributes(DECP.plan_handle) AS DEPA
WHERE DECP.plan_handle = 0x05000C00825DB36840035D2B020000000000000000000000
ORDER BY DEPA.attribute 

sys.dm_exec_cached_plans provides metadata relating to plans stored in cache.  sys.dm_exec_query_plan exposes the xml for the query plan (click on it, dare you, you’ll get the query plan in a new tab in SSMS – cool, huh?)  sys.dm_exec_plan_attributes provides the individual attribute details for the session associated with each cached plan.

Keep in mind that you will need to identify and associate a plan_handle from your real-world situations.  This can be supplied via sys.dm_exec_requests (or you can cheat and type it in as I did here to take the easy way out in providing the example.)

This is complementary content to the presentation from 8/20/2009 for Quest Software’s Pain of the Week series.  I hope you were (will be) able to attend and that I was (will be) stellar.  At this time it remains to be seen.