Printed 2017/07/28 12:37AM

The Shadow Knows What Procedures Lurk in the SQL Plan Cache

By Robert Davis, 2008/03/28

How do you know what procedures are cached in SQL Server? Simple, just ask, and SQL Server will tell you.You can query the SQL Server dynamic management views to get a list of procedures in cache. In this example, I query sys.dm_exec_cached_plans and sys.dm_exec_sql_text:

Select object_name(st.objectid, st.dbid) As ProcedureName
From sys.dm_exec_cached_plans cp
Cross Apply sys.dm_exec_sql_text(cp.plan_handle) st
Where cp.objtype = 'Proc'
And st.dbid = db_id();


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.