question

dj1979 avatar image
dj1979 asked

dm_exec_query_stats getting cleared automatically

i have a situation where the data in dm_exec_query_stats getting cleared automatically. I cant find any scenarios why its getting cleared. When my .net application calls the stored procedure, i can see it takes time when there is no data in dm_exec_query_stats, so i am assuming its creating a plan. Then when the plan is there, the stored procedure is fast. Then in some inactivity (maybe 5 seconds or 30 seconds etc), the data in this table gets deleted, and slowness comes back. Any idea? Database setting is not to auto_close, no error logs in sql server, no additional services running, memory of my system is only 40% used, no one has run dbcc commands to clear the cache, stored procedure is not having recompile option. Help please
sql serverdbadmv
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
what version/edition of SQL Server?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
What is your min/max memory configured as? You can get this by running `exec sp_configure 'max server memory' ` `exec sp_configure 'min server memory' ` but you might need to run ` exec sp_configure 'show advanced', 1` ` reconfigure`
0 Likes 0 ·

1 Answer

·
dj1979 avatar image
dj1979 answered
Microsoft SQL Server 2016 (RTM-GDR) (KB3194716) - 13.0.1722.0 (X64) Sep 26 2016 13:17:23 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 6.3 (Build 14393: )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.