empty sys.dm_exec_query_stats

Question

Post reply

empty sys.dm_exec_query_stats

DamianC

SSCrazy Eights

Points: 8015
More actions
October 16, 2019 at 11:57 am

#3689227

Hello

I am having an issue with the query stats dmv
If I run a statement or execute an SP I would expect it to generate a row in sys.dm_exec_query_stats
Sometimes it does but more often than not it doesn't
I'm also finding that when it does, if I query sys.dm_exec_query_stats a couple of minutes later, the row has disappeared
It's on my laptop and there are no jobs running (SQL Agent is currently disabled)
Any ideas what could be causing this?
It's almost like something i clearing it every few seconds

Thanks

- Damian

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrey Right there with Babe Points: 780 More actions · Answer 1

I suspect Express Edition and/or option AutoClose=on of your DB

This reply was modified 4 years, 6 months ago by Andrey. Reason: and -> and/or

DamianC SSCrazy Eights Points: 8015 More actions · Answer 2

Thanks for the response

It's developers edition and AutoClose is set to False

- Damian

Lowell SSC Guru Points: 323504 More actions · Answer 3

have you set your max server memory to a low threshold, so it ends up clearing the procedure cache?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andrey Right there with Babe Points: 780 More actions · Answer 4

Another options is a big query with huge execution plan which pushes other plans away from proc cache.

I saw similar issue with single exec plan 400MB+ in size.

Enable QueryStore on your DB and you will get all your DML queries and plans there.

Btw, i have created 4 dashboards (for SSMS) which visualize QueryStore content and let see what and when was run, how many times and resources consumed.

They are available here and main report is QueryStore_Overview.

Andrey Right there with Babe Points: 780 More actions · Answer 5

btw, you can check procedure cache using the following query:

 select objtype [objtype]
, cacheobjtype
, sum(1) [plans]
, convert(numeric(10,1),sum(size_in_bytes/1024.)/1024.) [TotalSizeMB]
, max(size_in_bytes)/1024/1024 [LargestPlan_MB] 
from sys.dm_exec_cached_plans 
group by objtype, cacheobjtype
order by 4 desc
option (recompile)

DamianC SSCrazy Eights Points: 8015 More actions · Answer 6

Thanks for your suggestions

I tried a few and it didn't work although useful to know

I'll also take a look at your Query Store SSRS reports @Audrey

I eventually found a resolution here:

https://sqltechblog.com/2016/12/08/what-keeps-clearing-my-query-stats/

It seems to be related to lock pages

I've amended this and now start SQL Server as Local System

That seems to have resolved it

Thanks

- Damian