Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [plan-cache]

The tag has no usage guidance.

7
votes
3answers
1k 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?
4
votes
1answer
109 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 ...
2
votes
1answer
59 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 ...
3
votes
1answer
77 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 ...
0
votes
1answer
104 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 ...
2
votes
2answers
270 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 ...
1
vote
1answer
65 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 ...
7
votes
3answers
188 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 [...
2
votes
0answers
27 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); ...
9
votes
1answer
396 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 ...
1
vote
1answer
241 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 ...
3
votes
1answer
348 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 ...
1
vote
1answer
62 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 ...
3
votes
1answer
74 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): ...
2
votes
2answers
512 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 ...
1
vote
0answers
285 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 ...
2
votes
0answers
98 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 ...
2
votes
2answers
76 views

Does Query Plan cache gets cleared by itself?

I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query, SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ...
2
votes
0answers
134 views

Plan Guide for the LINQ-SQL Query

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
2
votes
3answers
215 views

Serial Plan in plan cache

I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow. The query involves a big view and then querying the ...
2
votes
3answers
184 views

Instant plan cache cleanup

I'm experiencing almost instant plan cache clean up. No plan resides in the plan cache for more than about 30 seconds (if it's not use meanwhile). Please see my testing environment (MS SQL 2017 - ...
2
votes
2answers
574 views

sp_Blitz, >1000 plans for CREATE PROCEDURE

I'm using Brent Ozar's sp_Blitz, and one of its results is this: Many Plans for One Query 1146 plans are present for a single query in the plan cache - meaning we probably have ...
0
votes
0answers
52 views

Cannot find query in plan cache and query execution times different for different paramters

I am investigating the reason why a query is performing bad in my SQL server 2016. The query executes in 1 sec when i give the date range filter as 2017-06-21 and 2018-06-20. When i give the date ...
3
votes
1answer
189 views

Can't find compiled parameters values in the plan cache

I have found, through the query store, a query that's performing on average 297582 logical reads. I wanted to see if I was able to tune that query a bit, and after that, try to execute the query ...
3
votes
1answer
101 views

Why get queryplans recompiled every minute

I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated. I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to ...
5
votes
1answer
107 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. ...
2
votes
1answer
137 views

SQL Server 2016 All Queries recompile all the time (plan cache corruption?)

I have a SQL Server 2016 SP1 production server. It's been running without a restart for 330 days. Everything's been running fine. Until last week, one day, all of the sudden, every query on the server ...
0
votes
1answer
109 views

Force to cache SQL query exection plan

I have a problem with long execution of select query first time in the morning. I guess it is because this query is not cached and SQL Server is caching it. Other few possible causes were checked and ...
-1
votes
1answer
78 views

SQL Server is creating incorrect plan

I have 200 GB production database. Normally daily prepared query plan count 8K. Most of these plans are non necessary because of the non parametric queries. Software developers are working on this. ...
9
votes
1answer
557 views

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?
4
votes
1answer
1k views

how to query the plan cache to find how healthy it is?

This morning looking at my monitoring tool I get a warning about High Compiles Query plan compiles should generally be <15% of batches per second. Higher values indicate plan reuse is low, and ...
1
vote
2answers
725 views

SQL Server 2016 SP1 CU3 clears the plan cache

Every few minutes up to more or less one hour, the proc cache is being flushed (but not entirely!). If I run: SELECT count (*) FROM sys.dm_exec_cached_plans ...just after clearing happens, the ...
1
vote
1answer
49 views

What happens when a stored procedure executes one of two different queries based on an input parameter?

I have encountered a stored procedure that performs one of two different queries based on an input parameter. Does this remove the plan from cache and force the query plan to be recompiled if the ...
0
votes
1answer
527 views

Query plan deleted

I have 200 GB database in my production server. System requirements: Amazon EC2 r4.2xlarge (8 vCPU,61 GB Memory) I check, plan count with query: SELECT objtype AS [CacheType] , count_big(*) ...
5
votes
1answer
1k views

SQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queries

Our application uses SQL Server 2014 and we got an issue related to the plan cache. We have a parametrized query and its execution plan depends on parameter values. The server caches an execution ...
1
vote
1answer
317 views

Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%

If 100% Buffer and Procedure cache hit ratio is considered not good in general? I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000) SELECT (a.cntr_value * 1.0 / ...
3
votes
1answer
170 views

Warnings NoJoinPredicate

This morning, our SQL Server 2016 started using high CPU percentage(90%) when normally is about 30%. Reviewing cache plan with sp_blitzcache, I saw a new plan for an old query in the top of CPU time. ...
-1
votes
1answer
54 views

Can the plan-cache and/or statistics be copied from one instance to another

MS SQL Server 2008r2: 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 ...
-2
votes
1answer
489 views

Empty plan cache problem [closed]

I am experiencing a strange situation right now - my plan cache is almost empty. I use the following query to see what's inside: SELECT dec.plan_handle,qs.sql_handle, dec.usecounts, dec.refcounts, ...
0
votes
0answers
21 views

Will hotfixing parameter sniffing with optimize for xy help?

I figured out a parameter sniffing issue and after doing experiments have decided to introduce a code (query) change in app code that uses optimize for unknown hint. Now, I need to make sure the ...
12
votes
2answers
2k views

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 ...
1
vote
2answers
83 views

Redundant plans in cache because of missing type length

Today I realized that our sql server's plan cach is stuffed with hundreads and thousands of nearly identical Compiled Plans. The total number of plans is around 30.000 on a live system consuming ...
2
votes
0answers
430 views

Tracking Down Plan Cache Clearing

I'm dealing with a database whose plan cache seems to be periodically clearing out by itself. As an example, in the span of about 2 minutes, the SQL plan cache went from 12.5k MB to 3.1k MB. At this ...
2
votes
1answer
397 views

SQL Server Plan cache size only 4GB on a server with 500GB RAM

I have a SQL server 2016 SP1 that has been up and running over a year and using the query below, I see that the plan cache size is approx only 4GB where as the physical server has 500GB and there is ...
4
votes
3answers
630 views

How to regenerate execution plans after clearing the plan cache?

After clearing the plan cache on SQL Server, how do I regenerate execution plans for stored procedures without executing the stored procedures themselves? When I updated statistics this morning, one ...
2
votes
0answers
322 views

What could empty our cache (plan and buffer)?

If I execute sp_BlitzFirst in my server, sometimes it tells me 2 things: Plan Cache Erased Recently (indeed my plan cache is around a few hundreds MBs) (sometimes) Page Life to low (indeed my page ...
-2
votes
1answer
109 views

Query the plan cache for scan and seek operations for a particular index

Can you please help me in querying the plan cache in order to see which queries perform a scan and which queries perform a seek operation for a particular (nonclustered) index? I'm on SQL Server 2008 ...
1
vote
1answer
2k views

Why does FREEPROCCACHE let you shrink tempdb?

Somebody did something stupid and tempdb exploded in size. Alerts are firing because the drive is full. I tried shrinking it, and it would not shrink (even though shrink queries said they worked). ...
7
votes
2answers
339 views

Find the value OPTIMIZE FOR UNKNOWN is using

If I have an OPTIMIZE FOR UNKNOWN in my stored procedure, am I able to see what value the database decided was optimal?
0
votes
2answers
104 views

Increase the time to live of a cached plan

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 ...