«

»

Feb 25

Extracting Procedure Name from Cached Plans

The other day, a friend of mine shot me an email asking how he could extract the procedure name from cached plans. He was using the query I referenced from Jonathan Kehayias to get subtree cost, but he really wanted to see which procedures aligned with which cost. I assume this was to look at the ones with the highest cost to optimize them. I told him I didn’t have anything, but I liked the idea so I went to work.

Why reinvent the wheel? I started with a query from Jason Strate:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.usecounts
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
,pa.attribute
,pa.value
,pa.is_cache_key
,qp.dbid
,qp.objectid
,qp.number
,cp.plan_handle
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE attribute = 'dbid'
AND value = DB_ID()
ORDER BY cp.usecounts DESC;

I use this query a lot to find cached plans for a specific database. In fact, that’s pretty much the title of his post… I needed to take this and modify it such that I could get the object name of any procedure. So, step 1 was to make sure that I was only looking at stored procedures. To do that, I had to filter the results on cp.objtype and limit it to ‘Proc’. The next step was to filter pa.attribute to ‘objectid’. This is because the objectid attribute of a ‘Proc’ objtype is the id of the procedure itself. Since this query is already bringing back the id of the database for each object, I could use the OBJECT_NAME() function to retrieve the name of the procedure. Finally, I removed system objects by making sure the objectid was greater than 0.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.usecounts
,cp.size_in_bytes
,cp.cacheobjtype
,cp.objtype
,pa.attribute
,pa.value
,OBJECT_NAME(CAST(pa.value AS INT),qp.dbid) AS proc_name
,pa.is_cache_key
,qp.dbid
,qp.objectid
,qp.number
,cp.plan_handle
,qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE attribute = 'objectid'
AND cp.objtype = 'Proc'
AND pa.value  > 0;

Voila! Now can can get the names of all of your stored procedures that are currently in the plan cache!

2 comments

  1. ChrisW

    You did a great job Jared.

    Thanks

    1. Jared Karney

      No… Thank YOU for giving me a great blog post!

Leave a Reply