Questions tagged [plan-cache]
The plan-cache tag has no usage guidance.
122
questions
-1
votes
1
answer
53
views
What is the purpose of parent_plan_handle in sys.dm_exec_cached_plans?
I am attempting to prove whether the plan cache is caching statements individually within multiple stored procedures with identical statements. I thought I would be able to use parent_plan_handle to ...
0
votes
2
answers
118
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 ...
- 103
1
vote
1
answer
61
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 ...
- 629
0
votes
2
answers
153
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 ...
- 629
2
votes
1
answer
469
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 ...
- 629
0
votes
1
answer
83
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 ...
- 2,033
1
vote
1
answer
110
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
717
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
72
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 ...
- 513
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,455
2
votes
2
answers
116
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 ...
- 2,033
0
votes
2
answers
768
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,455
6
votes
1
answer
1k
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 ...
- 29.5k
-2
votes
2
answers
165
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,455
0
votes
2
answers
262
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
186
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
75
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 ...
- 819
2
votes
3
answers
564
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
174
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,117
0
votes
2
answers
354
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,117
0
votes
1
answer
122
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
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 ...
- 73
0
votes
1
answer
91
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,117
1
vote
1
answer
85
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,117
0
votes
1
answer
481
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
121
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,303
2
votes
1
answer
101
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
537
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
274
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, ...
- 2,033
4
votes
2
answers
392
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
62
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,303
0
votes
1
answer
747
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 ...
- 38.6k
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?
- 1,303
2
votes
1
answer
82
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,335
1
vote
0
answers
259
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
210
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 ...
- 525
-1
votes
1
answer
213
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,335
0
votes
0
answers
133
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 ...
- 16.4k
1
vote
1
answer
447
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
930
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
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 ...
- 16.4k
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 ...
- 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?
- 38.6k
4
votes
1
answer
921
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
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 ...
- 6,297
3
votes
1
answer
174
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 ...
- 45
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
923
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 ...
- 2,033