In my previous post http://blogs.msdn.com/gauravbi/archive/2005/12/01/BufferPoolDesc.aspx we talked about analyzing the contents of buffer pool.
Let's talk a bit about analyzing the contents of procedure cache-
First we need to figure out what are the plans present in the cache after a scenario has been executed. This can be done by executing the following query -
-- FIND ALL THE PLANS IN -- THE CACHE AND THEIR SIZE-- select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) as sql_text
See http://msdn2.microsoft.com/en-us/library/ms187404.aspx for details on dm_exec_cached_plans and http://msdn2.microsoft.com/en-us/library/ms181929.aspx for details on dm_exec_sql_text.
I added the cross apply to dm_exec_sql_text as this helps me identify the procedure name / ad-hoc query.
Once we have narrowed down the procedure which we want to focus on, using the handle for the procedure / statement, we can execute the following statement to see the plan for the query in XML format.
-- Find Plan for a Query--select query_plan from sys.DM_EXEC_QUERY_PLAN(0x0600050059571120B8414C0B000000000000000000000000)
If you are like me, this plan will not make much sense to you. For looking at the plan in graphical format, see http://blogs.msdn.com/gauravbi/archive/2005/12/01/GraphicalXMLPlan.aspx.
Using the above statements, you shoud be able to find out why a query is executing slow or why is it consuming too much memory.