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.