Questions tagged [plan-cache]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
0answers
41 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 ...
1
vote
1answer
44 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 ...
-1
votes
1answer
45 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 ...
0
votes
0answers
51 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 ...
1
vote
1answer
86 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 ...
0
votes
1answer
82 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-...
1
vote
1answer
73 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 ...
2
votes
2answers
59 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 ...
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
147 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
135 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
87 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
350 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 ...
4
votes
2answers
663 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
108 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
383 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
36 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
463 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
330 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
412 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
67 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
93 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
819 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
426 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
104 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
95 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
161 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
286 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
263 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
592 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
57 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
217 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
103 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
143 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. ...
3
votes
1answer
174 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
190 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
79 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
592 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
2k 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
829 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
51 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
641 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
351 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
175 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
59 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
549 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
90 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 ...