Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [plan-cache]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
2 answers
55 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
Mikhail Boyarsky's user avatar
1 vote
1 answer
49 views

Can the plan cache be used to diagnose a lack of RAM?

I have learned a few good ways to diagnose a lack of RAM on a SQL Server and I am wondering if I should add the plan cache to this toolbox. A sign of lacking RAM is that the plan cache doesn't live ...
J. Mini's user avatar
  • 433
1 vote
0 answers
50 views

sp_BlitzQueryStore reports "multiple plans", but I can only find one execution plan. Why?

sp_BlitzQueryStore reports a particular query on several of my rows. In each of these, it reports Unparameterized Query, Parallel, Multiple Plans, Row estimate mismatch However, each of these rows ...
J. Mini's user avatar
  • 433
0 votes
2 answers
146 views

A query I ran yesterday isn't in the plan cache, but older and newer ones are. Why?

Yesterday, I ran a big ad-hoc query that I didn't save. Today, I want to see it again. I thought that I'd hunt through the plan cache. To my surprise, I can find older queries and newer queries, but ...
J. Mini's user avatar
  • 433
2 votes
1 answer
219 views

What are the advantages and disadvantages of inspecting the Plan Cache rather than the Query Store?

The Plan Cache and the Query Store are not the same, even for the same query. When hunting for performance or monitoring information on a specific query or set of related queries, what are the ...
J. Mini's user avatar
  • 433
0 votes
1 answer
80 views

Scalar Functions Causing Plan Cache Bloat

Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below: SELECT dbo.fnSomeFunction('12345678') I have done some testing and found that ...
SE1986's user avatar
  • 1,970
0 votes
0 answers
68 views

Plan Cache Limits and the Local Memory Pressure in Sql Server 2022

I am doing research about Sql Server Plan Cache Limits and the Local Memory Pressure. I have found this article by Erin Stellato but it was written four years ago and I am not sure it is still valid ...
AlexMortola's user avatar
1 vote
1 answer
109 views

Which SQL Server component is in charge with checking cached execution plans for a given query?

I understand that whenever SQL server receives a query it looks in the cache plan for any matching execution plans for that query. However, most of the resources that I encountered don't specify ...
Mehdi Charife's user avatar
3 votes
0 answers
47 views

Does sys.dm_exec_query_stats give information for "Compiled Plan Stub"? [duplicate]

I am investigating on the SQL Server behaviour with the “Optimize for Ad hoc Workloads” option set to “True”; in particular, I am trying to understand which information we have in cache after the ...
AlexMortola's user avatar
0 votes
0 answers
626 views

Force Microsoft SQL to recreate and use Specific execution plan?

I have a stored procedure that was working really well for about 3 weeks, than all of a sudden it basically ground to a complete Stop. This was i believe due to the server creating an additional query ...
Praxiom's user avatar
  • 101
0 votes
1 answer
70 views

SQL All Procs have WITH RECOMPLILE so how to maintain a bloated plan cache

I'm working a star schema data warehouse created in Azure SQL database where the last developer included WITH RECOMPILE on all the Procs. This I believe was because the ETL only executes these Procs ...
Geezer's user avatar
  • 431
7 votes
2 answers
1k views

Which execution plan is stored in the plan cache?

When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost. The chosen plan will be stored in the plan cache for subsequent use when it ...
variable's user avatar
  • 3,636
2 votes
2 answers
102 views

Find Which Queries will be affected by Proposed Change to CTFP

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
SE1986's user avatar
  • 1,970
-1 votes
2 answers
680 views

What causes SQL server to refresh plan cache?

I am trying to understand situations that cause SQL server to refresh the plan cache. For example: SQL server restart DBCC FREEPROCCACHE Is there a list of such things?
variable's user avatar
  • 3,636
6 votes
1 answer
953 views

Why are the majority of cached plans missing from sys.dm_exec_query_stats?

I'm trying to understand some execution plan caching metadata on a SQL Server 2016 SP3 system, and I can't reconcile what I'm seeing with the docs. The docs for sys.dm_exec_cached_plans say it ...
Josh Darnell's user avatar
  • 29.4k
-2 votes
2 answers
159 views

Why does changing parameterization style result in lower plans in the plan cache when using sp_executesql?

Generally sp_execute sql will re-use cached plan, where as EXEC will create new plan for each parameter. In this example, both queries are using sp_executesql - What is the reason that the 2nd query ...
variable's user avatar
  • 3,636
0 votes
2 answers
240 views

Need to maintain execution plans on SQL Server Upgrade 2017 to 2019

We are working on a performance issue and need to maintain the execution plans for troubleshooting. Need to maintain the execution plans and ensure the plan cache is not flushed out on upgrade to 2019....
user3479378's user avatar
0 votes
1 answer
178 views

SSMS - SQL Execution Plans

I am new to SQL execution plans and I'm trying to learn more about them. I have a few questions. How long does a SQL Execution Plan stay in memory? If I disconnect my connection, I assume it won't be ...
Chicken Sandwich No Pickles's user avatar
0 votes
2 answers
74 views

How to see if index is being read "ordered" from DMVs

I have a table that is being used in many reporting queries. I would like to know how many times the table is read in an "ordered" way. In the execution plan for clustered index scan one can ...
xhr489's user avatar
  • 807
2 votes
3 answers
532 views

Critical query plans removed from sys.dm_exec_query_stats while trivial items are kept [duplicate]

Running SQL Server Express 2019. Have a complicated stored procedure containing 11 sql statements (when selected from sys.dm_exec_query_stats by plan_handle). 9 of those statements are simple variable ...
roman m's user avatar
  • 125
0 votes
2 answers
163 views

How to query plan cache for all query's using a table variable?

Is there a way to query the sql plan cache for all query's using a table variable?
user2368632's user avatar
  • 1,097
0 votes
2 answers
262 views

How to query cached plans that contain a specific missing index?

I see a potential missing index for a table but would like to first query all cached plans that have that specific missing index (so I can later test those queries after the new index is built). How ...
user2368632's user avatar
  • 1,097
0 votes
1 answer
115 views

A simple T-SQL query plan reuse question [duplicate]

Even though I have parameterized this INSERT on SQL Server, it fails to reuse the plan if the length of one of the VARCHAR fields changes. Do I have any recourse short of padding the data? The query ...
fthurber's user avatar
2 votes
2 answers
1k views

High CPU and plan cache instability

In the past couple of months my SQL Server instance has been experiencing higher than usual CPU usage than before, at times hitting 100% and affecting an application. There have been no recent changes ...
Garry's user avatar
  • 73
0 votes
1 answer
79 views

Query for returning query plans from sql plan cache that contain 1 or more warnings? [closed]

Is there a query I can run to return query plans from sql plan cache that contain 1 or more warnings?
user2368632's user avatar
  • 1,097
1 vote
1 answer
80 views

How to track down app name for query in query plan cache?

I found a query in the plan cache that needs parameterizing. Not sure which app created this query. Is there a good way in SQL to track down the app that put a given query in cache? sql2014
user2368632's user avatar
  • 1,097
0 votes
1 answer
399 views

Clearing plan cache improves performance - bad plans in cache?

I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1) Using SSMS, I'll run a stored procedure a few times. It consistently takes, say ...
mhenry1384's user avatar
1 vote
1 answer
107 views

Is filling up plan cache causes a decrease in space allocated for data cache?

SQL Server uses allocated server memory for different kind of purposes. Two of them are plan cache and data cache which are used to store execution plans and actual data correspondingly. My question: ...
Rauf Asadov's user avatar
  • 1,273
2 votes
1 answer
100 views

Why is this frequent SQL not in the SQL Server procedure cache? [closed]

I frequently query the SQL Server procedure cache (dm_exec_query_stats, dm_exec_sql_text, dm_exec_query_plan) to get execution stats and execution plans. We have a new application (with a new ...
Mike Tefft's user avatar
1 vote
1 answer
512 views

Does "Table Valued Function" force the plan to get into cache?

sp_executesql and stored procedures force the plan generated from t-sql to get put into the cache, does this behaviour is shared with table value function or not ?
A.HADDAD's user avatar
  • 119
2 votes
1 answer
263 views

sp_Blitzcache doesn't return as many results as sys.dm_exec_query_stats for a given query

I am trying to find plans for a particular set of queries (those that update MyTable) using sp_blitzcache: EXEC sp_Blitzcache @SlowlySearchPlansFor = 'UPDATE [My Table]' this returns 3 results, ...
SE1986's user avatar
  • 1,970
4 votes
2 answers
382 views

Execution Plan Warnings cardinality xml data type

I have the following statements: -- 1st DECLARE @AuditParameters XML = ( SELECT 1 AS AccountID, 2 AS CategoryID, 3 AS CategoryAttributeID, '4' AS SyncBatchGUID ...
Kevy Granero's user avatar
0 votes
0 answers
61 views

Why additional plans are created under simple parameterization?

I have a table which has 2163 rows and clustered index on id column. I run the following queries: select id from ticket_attach where id = 1 select id from ticket_attach where id = 2 select id from ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
673 views

Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?

I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the ...
J.D.'s user avatar
  • 37.9k
2 votes
1 answer
66 views

Can we set the fixed size for both plan cache and data cache?

As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?
Rauf Asadov's user avatar
  • 1,273
2 votes
1 answer
81 views

Does enabling LPIM work even if the memory pressure is coming from SQL Server itself

I have a scenario where I have 128 GB Physical memory for the SQL Server 2016 machine. It is a SQL Server dedicated machine. I have 110 GB allocated to SQL Server itself. I have performance issues ...
user9516827's user avatar
  • 1,315
1 vote
0 answers
256 views

SQL Prepared statement reuse in plan cache

I'm working on minimising our plan cache usage, and I've read a lot around Adhoc queries and how we can parameterize and reuse rather than having many single use plans. I'm not seeing as much ...
crucible's user avatar
  • 307
1 vote
1 answer
194 views

Restore plan cache and buffer pool after database restore? [duplicate]

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use? Similar question: is there a way to pro-actively restore the buffer ...
Conrad S.'s user avatar
  • 525
-1 votes
1 answer
206 views

Query Plan for a linked-server query getting cleared from plan cache after every execution

I have linked server query which runs of a view created in the remote server. Whenever i run the query i can see that a Prepared plan gets created in the plancache. But that gets removed from the plan ...
user9516827's user avatar
  • 1,315
0 votes
0 answers
130 views

many of the same queries on the query plan - whats the pros and cons of Forced Parameterization in this scenario?

while working on finding all the queries in the query plan that are related to a particular index I realised that there were too many duplicates of the same queries on the query plan. Finding what ...
Marcello Miorelli's user avatar
1 vote
1 answer
405 views

Parameter Sniffing - Running query in code vs in management studio

I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a ...
Patrick958's user avatar
0 votes
1 answer
899 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
Hadi's user avatar
  • 127
1 vote
1 answer
97 views

Why is parallelism not good in this query?

I was looking for missing indexes in the cached execution plans then I found a very nice article by Jonathan Kehayias: Finding Key Lookups inside the Plan Cache and from there I borrowed the ...
Marcello Miorelli's user avatar
2 votes
2 answers
1k views

DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones

Kimberly Tripp's blog post here made me think that running DBCC FREESYSTEMCACHE('SQL Plans') clears just single-used ad-hoc and prepared query plans. But that seems incorrect. It clears all. Which is ...
jericzech's user avatar
  • 945
8 votes
3 answers
7k views

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
J.D.'s user avatar
  • 37.9k
4 votes
1 answer
896 views

Why query cache plan kept dynamic queries but not sp?

My company has some legacy system that uses quite a lot of dynamic queries (no parameters). In the newer systems we use Stored Procedures and parameterised SQL. However what we found is that our ...
daxu's user avatar
  • 167
2 votes
1 answer
2k views

How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]

I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
James Jenkins's user avatar
3 votes
1 answer
159 views

Parameterized Plan Flushed

I cannot find documentation anywhere on what would trigger this recompilation reason. We are investigating a sudden query performance drop and the only thing that I can think of is that the plan got ...
Marc S's user avatar
  • 33
0 votes
1 answer
2k views

Determing when a stored procedure was last compiled

Is there a way to find when a procedure last had an execution plan compiled in Sybase ASE 16? I don't see a "modified" datetime column in sysobjects, and I haven't been able to find a Sybase ...
RToyo's user avatar
  • 167
8 votes
2 answers
4k views

How to clear ad hoc queries from plan cache?

As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have ...
Commander's user avatar
  • 333