Questions tagged [plan-cache]
The plan-cache tag has no usage guidance.
118
questions
0
votes
0
answers
22
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 ...
- 63
1
vote
1
answer
107
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 ...
- 131
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 ...
- 63
0
votes
0
answers
25
views
Cache plan removal
I'm having an application which generates SQL queries from EF library. When looking into the query plan cache on the test server I see that plans are getting frequently removed from there. Plan ...
- 1
0
votes
0
answers
307
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 ...
- 101
0
votes
1
answer
61
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 ...
- 441
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 ...
- 3,360
2
votes
2
answers
84
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 ...
- 1,898
-1
votes
2
answers
439
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?
- 3,360
6
votes
1
answer
703
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 ...
- 28.9k
-2
votes
2
answers
133
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 ...
- 3,360
0
votes
2
answers
182
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....
0
votes
1
answer
165
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 ...
0
votes
2
answers
71
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 ...
- 785
2
votes
3
answers
499
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 ...
- 125
0
votes
2
answers
109
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?
- 1,077
0
votes
2
answers
158
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 ...
- 1,077
0
votes
1
answer
110
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 ...
- 1
2
votes
2
answers
926
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 ...
- 73
0
votes
1
answer
55
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?
- 1,077
1
vote
1
answer
68
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
- 1,077
0
votes
1
answer
270
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 ...
- 156
1
vote
1
answer
79
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: ...
- 1,243
2
votes
1
answer
95
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 ...
- 31
1
vote
1
answer
412
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 ?
- 119
2
votes
1
answer
239
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, ...
- 1,898
4
votes
2
answers
353
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
...
- 143
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 ...
- 1,243
0
votes
1
answer
383
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 ...
- 34k
2
votes
1
answer
63
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?
- 1,243
2
votes
1
answer
80
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 ...
- 1,295
1
vote
0
answers
248
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 ...
- 307
1
vote
1
answer
153
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 ...
- 485
-1
votes
1
answer
185
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 ...
- 1,295
0
votes
0
answers
127
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 ...
- 15.8k
1
vote
1
answer
367
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 ...
- 13
0
votes
1
answer
780
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-...
- 127
1
vote
1
answer
95
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 ...
- 15.8k
2
votes
2
answers
830
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 ...
- 945
8
votes
3
answers
6k
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?
- 34k
4
votes
1
answer
793
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 ...
- 167
2
votes
1
answer
1k
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 ...
- 6,178
3
votes
1
answer
141
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 ...
- 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 ...
- 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 ...
- 333
1
vote
1
answer
746
views
How does SQL Server determine how much memory to allocate to Data cache
SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets ...
- 1,898
7
votes
3
answers
8k
views
Field = Parameter OR Parameter IS NULL Pattern
I am aware of the parameter sniffing issues associated with stored procedures written with a predicate like the following:
CREATE PROCEDURE [dbo].[Get] @Parameter INT = NULL AS BEGIN;
SELECT [...
- 154
2
votes
0
answers
244
views
Plan Cache: Why are multiple plans cached for this stored procedure in SQL Server [duplicate]
I am trying to gain a better understanding as to how SQL Server caches query plans.
For the following table(Populated with the values 1-500,000):
CREATE TABLE [dbo].[Test] ([ID] INT PRIMARY KEY);
...
- 154
9
votes
1
answer
944
views
Measuring plan eviction
We have a SQL Server 2016 SP1 with max memory set to 24GB.
This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored ...
- 1,994
1
vote
1
answer
959
views
Slow Query with RESOURCE_SEMAPHORE wait info [closed]
Hi I see lot of RESOURCE_SEMAPHORE as wait_info when i run sp_whoisactive in my sql database server.I see few CXCONSUMER as well.I learned that CXCONSUMER is because of parallel query execution.How to ...
- 1,295