Sample Header Ad - 728x90

Does Query Plan cache gets cleared by itself?

2 votes
2 answers
1241 views
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?
Asked by user9516827 (1345 rep)
Jul 20, 2018, 03:31 PM
Last activity: Jul 23, 2018, 05:10 PM