February 22, 2018 at 3:28 am
I have an issue on one of my SQL servers that the query stats are only being retained for a handful of minutes. Query:
SELECT execquery.last_execution_time AS [Date Time], execsql.[text] AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC
In my experience this tends to retain ~36 hours worth of data and I am seeing this across my servers, however on one server it is only retaining 2 and 30 minutes worth of data. I have confirmed there are no jobs running any DBCC commands to clear the cache, including Windows scheduled tasks, TSQL jobs and SSIS jobs. I've considered whether the server has insufficient memory allocated to this but am unable to find a way to increase the memory allocated to DMVs. Any advice on how to retain more history?
• SQL Server 2012 Enterprise
• 64Gb ram
• 4 Cores
• Windows Server 2012 R2
February 22, 2018 at 3:59 am
According to the documentation:
The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
Start by looking here to find out why stuff isn't staying for long in the plan cache.
John
February 22, 2018 at 4:06 am
sys.dm_exec_query_stats is dependent on the query's plan being in cache. When the query's plan is removed from cache, the query stats for that query are removed.
Hence, what you're seeing is likely to be a symptom of plan cache churn, which may be lack of memory (allocated to the plan cache) or may be a bunch of other things
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2018 at 6:45 pm
Agree it could be a handful of other things. Have you considered possibility that somebody placed a DBCC FREEPROCCACHE on some job/ad-hoc script/or proc? I would give it a try by setting up a trace to look for just that. The command would be quick to execute and one might miss it if it's just being observed casually/visually.
I've had a case where a GRANT statement was at the end of a proc, which didn't make sense. He did complained why the simple SELECT proc won't run w/o sysadmin access. Turns out it was a mistake not removing the GRANT statement at the very end of the proc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply