sys.dm_exec_cached_plans not showing any data after executing stored proc multiple times.

Question

Executed different stored proc multiple times and ran below query to check stats. its not showing any data.

SQL Version : Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)

Stored Proc : exec [dbo].[uspGetManagerEmployees] @BusinessEntityID = 7

DB : [AdventureWorks2012] Query:

SELECT  DB_NAME(st.dbid) DBNamee
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL 
AND cp.objtype = 'proc'
AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
ORDER BY MAX(cp.usecounts) DESC
GO

Answer 1

Hi @SSR

Stored Proc : exec [dbo].[uspGetManagerEmployees] @BusinessEntityID = 7

While in your query: AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'

Is that a typo?

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What server collation do you have? If you have a case-sensitive collation, change proc to Proc, because that what it says in the DMV. It could also be a issue with memory pressure. Given the database name, I assume that this is on your laptop or similar and not a server. How much RAM do you have? I have seen on several occasions where things fall out of the cache quite quickly, because Windows things there is memory pressure, and therefore asks SQL Server to trim.