Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [plan-cache]

125 questions
19
votes
2 answers

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.dm_os_memory_objects, looking at the plan in question, says that…
GordonLiddy
  • 445
  • 5
  • 10
18
votes
2 answers

Missing execution plans for stored procedures

What are the reasons for a plan to be missing from cache for stored procedures? WITH RECOMPILE Dynamic SQL Encrypted code Significant data changes Update statistics What else? I've worked on 2 servers (SQL Server 2008 R2 and SQL Server 2012)…
Tara Kizer
  • 5,986
  • 1
  • 17
  • 28
16
votes
2 answers

What is the sense and benefit of using SqlCommand.Prepare()?

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of…
Magier
  • 4,827
  • 8
  • 48
  • 91
15
votes
1 answer

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 a temporary table A DDL statement then creates a…
Kendra Little
  • 938
  • 6
  • 13
14
votes
2 answers

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 SPIDs as per following examples: 09/04/2015…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
11
votes
1 answer

Would it be better for query plans to be split by statement for re-usability?

From my limited knowledge of how query plans are compiled, stored and retrieved by queries I understand that a multi statement query or stored procedure will generate it's query plan which will be stored in the query plan cache to be used by the…
James Anderson
  • 5,794
  • 2
  • 27
  • 43
10
votes
1 answer

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 varying, role character varying) RETURNS json LANGUAGE…
Eric Ly
  • 1,222
  • 12
  • 20
9
votes
1 answer

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 in the plan cache but the size of the plan cache…
Frederik Vanderhaegen
  • 2,122
  • 1
  • 17
  • 36
9
votes
1 answer

Will Changing the Max Memory Setting Invalidate Query Plans?

I recently lowered the max memory from the default (unlimited) down to 20 GB. Does this erase the oldest queries in the plan cache?
Shellz
  • 452
  • 3
  • 11
8
votes
1 answer

Why does the plan cache contain estimated and not actual execution plans?

I can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.
manish
  • 203
  • 1
  • 3
8
votes
3 answers

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.
  • 40,776
  • 12
  • 62
  • 141
8
votes
2 answers

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 any suggestion? Many Thanks.
Commander
  • 333
  • 1
  • 5
  • 16
8
votes
2 answers

Is execution plan cached “better” for stored procedures than for a non-dynamic query?

Reading different explanations about execution plan caching by Microsoft SQL Server, I'm confused about the benefits of using stored procedures instead of non-dynamic queries. By a non-dynamic query I mean a fully parametrized query string which…
Arseni Mourzenko
  • 693
  • 10
  • 16
7
votes
1 answer

Query plan cache bloated by ad-hoc queries, even with "Optimize for Ad-hoc Workloads"

I've been noticing what I thought to be unusual issues with our query plan cache, where the plans in the cache were never more than a day old. Through running the following query (courtesy of Kimberly Tripp), it showed that the majority of plans…
Mark Sinkinson
  • 10,657
  • 4
  • 47
  • 54
7
votes
2 answers

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 sees the same query. When certain attributes of…
variable
  • 3,590
  • 4
  • 37
  • 100
1
2 3
…
8 9