Stack Exchange Network

Stack Exchange network consists of 174 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.

1
vote
1answer
45 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
142 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
25 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); ...
5
votes
1answer
315 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
111 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
votes
1answer
278 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
55 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
56 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
256 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 ...
0
votes
0answers
98 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
81 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
68 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
79 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
152 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
95 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
546 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
49 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
119 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
95 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
82 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
100 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
63 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
518 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
857 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
617 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
47 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
374 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
950 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
230 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
153 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
48 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
409 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 ...
11
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
75 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 ...
3
votes
0answers
361 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 ...
-1
votes
1answer
299 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 ...
3
votes
3answers
448 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 ...
3
votes
0answers
260 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
93 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
0answers
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
334 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
71 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 ...
1
vote
1answer
505 views

Identifying Execution Plans with Probe Residual

I am trying to find out Execution Plans that has Probe Residual. Need to know the following Which physical and logical operator has this Probe Residual What is the Cost% of that operator in the ...
6
votes
2answers
425 views

Can a plan “age” reach zero?

I am reading SQL Server Execution plans by Grant Fritchey, He mentions: SQL Server does not keep execution plans in memory forever. They are slowly aged out of the system using an "age" formula ...
6
votes
1answer
2k views

SQL SERVER 2016 high CPU

I have a brand new server with SQL Server 2016. It has 24 CPU, about 80go of RAM. The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all. If I look at the 'execute ...
2
votes
1answer
350 views

Run sp_BlitzCache an agent job to assist performance tuning efforts / baselining query performance?

Just getting into using various Brent Ozar tools like sp_BlitzCache and sp_BlitzIndex to identify and correct SQL Server performance issues. Been experimenting with running sp_AskBrent (now BlitzFirst....
1
vote
0answers
154 views

Inserting rows into a temporary variable causes plan cache pollution in SQL Server

In my database I use stored procedures to insert multiple rows from a temporary table variable. Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each ...
2
votes
1answer
794 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 ...