Questions tagged [plan-cache]
The plan-cache tag has no usage guidance.
123
questions
0
votes
2
answers
39
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 ...
- 31.3k
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 ...
- 11k
1
vote
0
answers
48
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 ...
- 139
2
votes
1
answer
193
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 ...
- 139
0
votes
2
answers
145
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 ...
- 40.8k
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 ...
CommunityBot
- 1
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 ...
- 40.8k
3
votes
0
answers
2k
views
how to minimize the effect of updating statistics on the plan cache?
The issue I am trying to address:
After updating statistics there are slow downs in running many of our processes here.
then following the scary dba
on this link below:
http://www.sqlservercentral.com/...
- 117
0
votes
0
answers
63
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
6
votes
1
answer
3k
views
Monitoring the Postgres query parser
As I understand it, when you send a query to Postgres like:
SELECT id FROM table1 WHERE name = $1;
Postgres will create a query plan. The plan will be cached for the same query in the same session. ...
- 176k
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 ...
- 13.2k
0
votes
1
answer
378
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 ...
CommunityBot
- 1
10
votes
1
answer
6k
views
PL/pgSQL issues when function used twice (caching problem?)
I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem.
I have this function:
CREATE FUNCTION sp_connect(mail character varying, passwd character ...
- 176k
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 ...
- 40.8k
3
votes
1
answer
2k
views
Trace flag 2861 and what a 'zero-cost' plan actually means
I'm trying to get my head around trace flag 2861 and what it actually does with trivial queries?
The blurb says:
SQL Server typically won't cache plans for these trivial queries because the cost ...
- 70k
0
votes
1
answer
82
views
Can the plan-cache and/or statistics be copied from one instance to another
I restore production databases on top of a staging instance that I use for testing.
Is it possible to also copy the plan-cache and/or statistics to the staging instance, to better simulate the ...
- 70k
0
votes
0
answers
592
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
2
votes
1
answer
162
views
Will cleaning data cache help to resolve compiler memory pressure?
I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic.
I figured it could be a memory pressure ...
- 2,201
0
votes
1
answer
69
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 ...
- 46.5k
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 ...
- 4,460
2
votes
2
answers
95
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 ...
- 13k
4
votes
1
answer
357
views
Resolving sp_Blitz Finding: Many Plans for One Query
Why Multiple Plans for One Query Are Bad
So I was running sp_Blitz to get a handle on some systems. Got some code to get cleaned up as usual. Some heaps that should have been clustered indexes. ...
- 84.2k
-1
votes
2
answers
646
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?
- 17.1k
6
votes
1
answer
915
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 ...
- 84.2k
3
votes
1
answer
2k
views
Parameterized query creating many plans
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.
Queries are like:
(@P1 varchar(1043),@P2 varchar(6))
UPDATE table
...
- 40.8k
-2
votes
2
answers
157
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,071
0
votes
2
answers
234
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....
- 16.1k
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 ...
- 13k
1
vote
1
answer
1k
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 ...
- 2,420
13
votes
2
answers
12k
views
A possible infinite recompile was detected for SQLHANDLE
I have been finding strange error messages on the sql error log:
Bocss: same deadlock taking place every hour – needs investigating
Also lots of recompiles are listed in the error log for other ...
- 203
0
votes
2
answers
73
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 ...
- 13k
2
votes
3
answers
526
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 ...
- 46.5k
0
votes
2
answers
150
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?
- 13k
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 ...
- 125
0
votes
2
answers
240
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 ...
- 4,056
0
votes
1
answer
114
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 ...
- 29.2k
0
votes
1
answer
75
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?
- 37.5k
1
vote
1
answer
79
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
- 40.8k
0
votes
1
answer
654
views
Increase the time to live of a cached plan [duplicate]
I'm looking at the Plan Cache in SQL Server. I've got a simple stored procedure:
CREATE PROC [dbo].[CountDescriptions](@Description varchar(1))
AS
SELECT ID
FROM MyTable
WHERE ...
CommunityBot
- 1
1
vote
1
answer
105
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: ...
- 46.5k
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 ...
- 29.2k
1
vote
1
answer
501
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 ?
- 16.2k
2
votes
1
answer
260
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, ...
- 43k
19
votes
2
answers
7k
views
Plan cache size and reserved memory
When running a query including the Actual Execution Plan, the root operator (SELECT) tells me that the Cached Plan Size is 32KB.
A query that joins sys.dm_exec_cached_plans and sys....
4
votes
2
answers
379
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
...
- 84.2k
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,263
0
votes
1
answer
613
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 ...
- 37.5k
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?
- 16.1k
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1