Questions tagged [plan-cache]
The plan-cache tag has no summary.
126 questions
0 votes
0 answers
54 views
SQL Server 2022 CU11: Specific CPU Cores Constantly Spiking After Plan Cache Fills
We have observed an unusual CPU behavior on our SQL Server 2022 (Core Edition) starting from a specific date. The issue is that a few specific CPU cores — particularly cores 1, 7, 14, and 21 — ...
1 vote
0 answers
34 views
Rolling blocking on Compiling a SP [duplicate]
We are experiencing rolling blocking when a specific stored procedure runs. This SP runs multiple times a minute, and does heavily use temp tables. It doesn't block all the time, but during peak ...
- 255
15 votes
1 answer
770 views
What could cause a SQL Server non-cached temporary table to NOT trigger a recompile when a large amount of rows have changed?
I have observed (and reproduced) the following scenario with SQL Server 2022. The pattern in use code is executed via sp_executesql (no stored procedure is involved) The first query selects data into ...
- 958
0 votes
1 answer
244 views
Why doesn't DISCARD PLANS/ALL work for queries inside PLPGSQL functions? [duplicate]
I'm experiencing unexpected behavior with query plan caching in PostgreSQL when using different function types. I have a dashboard query that exhibits significantly different performance ...
- 203
0 votes
2 answers
140 views
why sql server is not reusing plan in my adhoc query?
Seems my understanding of query plan is not correct. If I run this query first time and check the query plan cache. I see one plan cached. declare @codes varchar(max)='BHVD7,BHVDE,BHVDF' SELECT ...
- 63
-1 votes
1 answer
70 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
268 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
103 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 ...
- 1,322
0 votes
2 answers
190 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 ...
- 1,322
2 votes
1 answer
679 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 ...
- 1,322
0 votes
1 answer
121 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,256
1 vote
1 answer
124 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
51 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 ...
0 votes
0 answers
840 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
82 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