2

I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query,

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where 
st.text like '%SearchModel%' 
order by ExecutionCount desc

I see different counts for the result.I am not clearing the plan cache and no other developers. I am working on SQL Server 2016 and i have turned on Optimize for Adhoc option.The queries are generated from application using LINQ-SQL.So i hope the queries generated are adhoc.Please correct me if i am wrong.

Why is that the cached plan counts varies each time.?

Plan cache screenshot

Does anyone has any insight from the below screenshot regarding plan cache usage.It has more memory allocated for adhoch queries as queries in my application comes from LINQ-SQL. I guess prepared queries are compiled queries?

Improve this question
7
  • 2
    dba.stackexchange.com/questions/182227/…
    – S3S
    Jul 20, 2018 at 15:38
  • 2
    Fun fact: the query you're running will end up in the plan cache, and will find itself when you run it. Try adding a recompile hint.
    – Erik Darling
    Jul 20, 2018 at 15:38
  • @scsimon : i haven't done my research.
    – user9516827
    Jul 20, 2018 at 15:49
  • 2
    if you use dm_exec_query_stats instead of dm_exec_cached_plans you can look at creation_time column and you will see different time and identify plans that were created since you ran it last time.
    – SqlWorldWide
    Jul 20, 2018 at 16:14
  • 1
    If your plan cache is getting cleared out: 1. Someone is clearing it manually 2. Your server is under memory pressure 3. Someone is changing settings that cause it to clear out or 4, You have a weird setting that's making things awkward, like having min server memory set equal to max server memory. There's always something that explains it.
    – Erik Darling
    Jul 20, 2018 at 17:06

2 Answers 2

Reset to default
1

It's very unlikely, but you could be experiencing the bug described in the first comment to this user voice item: Procedure Cache empties spontaneously even on an idle machine

This seems to happen when USERSTORE_TOKENPERM hits a certain value.
...
Under this configuration, the plan cache empties spontaneously when the USERSTORE_TOKENPERM size grows to around 5 GB

You can check for that with this query:

select pages_kb 
from sys.dm_os_memory_clerks 
where [type] = 'USERSTORE_TOKENPERM' and [name] = 'TokenAndPermUserStore';

There are several other factors that can cause your plan cache to be emptied. A non-exhaustive list of these factors includes:

  • someone ran the DBCC FREEPROCCACHE command to manually clear it
  • The server is under memory pressure, and old or single-use plans are being evicted from the cache
Improve this answer
0
1

Since you are running SQL Server 2016, you may want to look at using Query Store. This does get enabled at the database level, but it may be helpful for you. Check out this article:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.