Questions tagged [plan-cache]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-3 votes
0 answers
33 views

Page Life Expectancy is dropping frequently

Server Spec: 64 GB Ram (R5.2xLarge) Support Team reported there is memory pressure on the SQL Server. Allocated 52 GB memory as min and max for SQL Instance Observed page life expectancy counter value ...
user avatar
  • 1
2 votes
2 answers
31 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 ...
user avatar
  • 1,450
-1 votes
2 answers
41 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?
user avatar
  • 2,070
5 votes
1 answer
205 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 ...
user avatar
  • 27.1k
-2 votes
2 answers
75 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 ...
user avatar
  • 2,070
0 votes
2 answers
71 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....
user avatar
0 votes
1 answer
107 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 ...
user avatar
0 votes
2 answers
61 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 ...
user avatar
  • 587
2 votes
3 answers
432 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 ...
user avatar
  • 125
0 votes
2 answers
52 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?
user avatar
0 votes
2 answers
54 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 ...
user avatar
0 votes
1 answer
35 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 ...
user avatar
1 vote
2 answers
316 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 ...
user avatar
  • 63
0 votes
1 answer
38 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?
user avatar
1 vote
1 answer
37 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
user avatar
0 votes
0 answers
112 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 ...
user avatar
1 vote
1 answer
38 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: ...
user avatar
  • 1,163
2 votes
1 answer
88 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 ...
user avatar
1 vote
1 answer
129 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 ?
user avatar
  • 119
2 votes
1 answer
190 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, ...
user avatar
  • 1,450
4 votes
2 answers
256 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 ...
user avatar
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 ...
user avatar
  • 1,163
0 votes
1 answer
83 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 ...
user avatar
  • 24.1k
2 votes
1 answer
56 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?
user avatar
  • 1,163
2 votes
1 answer
67 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 ...
user avatar
  • 1,285
1 vote
0 answers
182 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 ...
user avatar
  • 307
1 vote
1 answer
102 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 ...
user avatar
  • 435
-1 votes
1 answer
124 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 ...
user avatar
  • 1,285
0 votes
0 answers
109 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 ...
user avatar
1 vote
1 answer
266 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 ...
user avatar
0 votes
1 answer
484 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-...
user avatar
  • 117
1 vote
1 answer
85 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 ...
user avatar
2 votes
2 answers
363 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 ...
user avatar
  • 935
8 votes
3 answers
5k 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?
user avatar
  • 24.1k
4 votes
1 answer
468 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 ...
user avatar
  • 167
2 votes
1 answer
777 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 ...
user avatar
3 votes
1 answer
112 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 ...
user avatar
  • 33
0 votes
1 answer
1k 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 ...
user avatar
  • 167
8 votes
2 answers
3k 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 ...
user avatar
  • 323
1 vote
1 answer
548 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 ...
user avatar
  • 1,450
7 votes
3 answers
4k 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 [...
user avatar
2 votes
0 answers
242 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); ...
user avatar
9 votes
1 answer
810 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 ...
user avatar
1 vote
1 answer
837 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 ...
user avatar
  • 1,285
3 votes
1 answer
825 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 ...
user avatar
1 vote
1 answer
156 views

Finding Serial plan from Plan cache

My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for ...
user avatar
  • 1,285
4 votes
1 answer
177 views

Group together similar queries in sys.dm_exec_sql_text

I'm analyzing a 2008 Instance that supports a 3rd party app. The app will generate the SQL Code then send it to the Database as an ad-hoc query. I'm using this query (based on a Glenn Berry script): ...
user avatar
  • 1,058
2 votes
2 answers
2k views

Tracking the last execution date of a query

My manager is requesting to know when was the last time a number of reports were last ran to see if they are getting used. He requested info from our vendor and the vendor if coming to me for SA level ...
user avatar
  • 23
2 votes
0 answers
733 views

SQL Buffer Pool and Plan cache

Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours. I have 128 GB Physical Memory. Earlier i ...
user avatar
  • 1,285
2 votes
0 answers
128 views

Plan cache mystery

I work on SQL Server 2016 and i had severe performance issues with few queries. So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a ...
user avatar
  • 1,285