Help with Missing Cached info SQL Server

Question

HI Experts. i was trying to find Stored procedures that ran/executed yesterday or maybe executed within last 24HRS-48HRS, and i used the below query to get info...but the strange thing I found is its always gets me only 1 hr old of data.

For example -if i run at 09:14 am est, i was able to get a history of stored procedures that ran from 8 am est only ...i was not able to get any history of stored procedures execution prior to (older than) 8 am.

• i dont have any Job to clear the cache or i did not restart my production sql server for 6 months
• did not find any in the SQL SERVER logs
• This server(SQL SERVER 2014 sp3) is not having issues ,its not busy server or we don't have disk or memory or cpu issues
• something happening in another server 2016 SP3

Kindly please provide some suggestions -Thanks

SELECT 
    SCHEMA_NAME(sysobject.schema_id) SchemaName,
    OBJECT_NAME(stats.object_id) SPName, 
    cached_time, last_execution_time, 
    execution_count,
    total_elapsed_time/execution_count 
            AS avg_elapsed_time
FROM  
    sys.dm_exec_procedure_stats stats
    INNER JOIN sys.objects sysobject 
        ON sysobject.object_id = stats.object_id 
WHERE 
    sysobject.type = 'P'
ORDER BY
    stats.last_execution_time DESC
	GO
    
 
 

Answer 1

select name, is_auto_close_on

from sys.databases ==OFF for all databases

FYI

Answer 2

To start with, the cache is not intended to be some sort of execution history. It is exactly that, a cache. Using something for another purpose than the intended is precarious.

A better bet in this context would be Query Store, but alas, since you are on SQL 2014, it is not available to you. With Query Store you would have been able to see during which hours it was executed, or more precisely when the statements in the procedure were executed, how many times, how long it took etc. But not by whom it was executed.

There are many possible reasons why things fall out of the cache. There may be memory pressure. There can be schema changes. And schema changes includes index rebuilds. Then again, that invalidates the plan for individual statements, so data on procedure should not be affected. Someone might have run sp_configure to change a configuration settings. That can clear the plan cache, depending on which option you changed.

In any case, I see no cause for alarm. If you want an execution history, there are better tools. Even in SQL 2014.

Answer 3

Hi Erland Thanks for your reply.
a little background about our environment

• We have index rebuild and update stats jobs that run every day for 8 + years and we did not see any issues.
• There is no memory pressure based on dmv and also didn't find any logs.
• we have always on, replication
• no one runs sp_configure as I am only the Admin on these servers and not many users have to write permissions.

any idea how the cache is missing?

Answer 4

Hi @NickK

The sys.dm_exec_procedure_stats returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

As far as I know, The duration that a query plan stays in the plan cache depends upon how often a query is executed. Query plans that are used more often, stay in the query plan cache for longer durations, and vice-versa.

Also, I found below explanation from this thread: Why query cache plan kept dynamic queries but not sp?.

Cached Plans are typically only removed from the plan cache under memory pressure.

SQL Server primarily considers the cost of the plan when deciding which plans to remove. Low-cost plans are removed before high-cost plans. The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.

When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%. For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused. If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost and are not being evicted. For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost. You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.

Cache cost is measured in units known as "ticks", with a maximum of 31. Ticks are incremented according to this:

1. each I/O adds 1 tick, up to a maximum of 19 ticks.
2. each context switch adds one tick, up to a maximum of 8 ticks.
3. one tick per 16 pages of memory, with a maximum of 4 ticks.

Ticks start to be decremented once the plan cache reaches 50% of its capacity. At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.

The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.

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.