The tag has no usage guidance.

learn more… | top users | synonyms

1
vote
1answer
61 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 ...
3
votes
0answers
110 views
+150

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 ...
1
vote
1answer
33 views

how to clear only the query plans from the current session?

I am working on tuning a query on a very busy server, where I don't want to clear the cache, using DBCC FREEPROCCACHE; I know it will cause at least a CPU spike, and in this case I don't want it. ...
0
votes
2answers
45 views

What are the SET options that affect Plan Reuse and how to get their values in T-SQL?

I'm troubleshooting the case when a stored procedure runs great from the management studio at any time, but the same stored procedure can run terribly badly in one of the webservers, even for the same ...
10
votes
2answers
206 views

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 this? Sample: ...
4
votes
1answer
175 views

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 ...
5
votes
2answers
100 views

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 ...
2
votes
1answer
44 views

Questions about DBCC FREEPROCCACHE

I used spAskBrent for troubleshooting a SQL Server 2005 performance issue. I found the problem was someone had ran DBCC FREEPROCCACHE. It caused low performance (very low PLE) on my database and ...
1
vote
0answers
33 views

Reasons why plans of sys.dm_exec_cached_plans do not match actual plans?

I re-assess index / query optimization and actually focus on key/bookmark lookpus that could be eliminated. Using a script of Jonathan Kehayas I go through the list of query plans that used those ...
1
vote
0answers
90 views

how to minimize the effect of updating statistics on the plan cache?

The issue I am trying to address: After updating statistics there are slow downs in running many of our processes here. then following the scary dba on this link below: ...
5
votes
1answer
800 views

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 ...
1
vote
0answers
35 views

AlwaysOn AG Live Migration

A couple of thoughts on AlwaysOn AG migrations: Is there a reason why Microsoft didn't implement similar technology to Hyper-V Live Migration to AlwaysOn AGs so that the buffer pool and cache are ...
1
vote
1answer
48 views

Plans created several times under Force Parameterization

People use forced parameterization to save plan compilation cost. However, I found things different here. The query I will run is as following, and under AdventureWorks2012 database: DECLARE @Var ...
9
votes
1answer
281 views

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 ...
3
votes
1answer
548 views

Monitoring the Postgres query parser

As I understand it, when you send a query to Postgres like: SELECT id FROM table1 WHERE name = $1; Postgres will create a query plan. The plan will be cached for the same query in the same ...
1
vote
0answers
131 views

Different Execution Plans Picked for Similar Queries

Thank you in advance for reading it. This question is kind of long and need a little patience to read. I will provide as much details as I can to void interference factors to solve this problem. ...
1
vote
0answers
61 views

Flush procedure cache or drop and create?

Okay I had a very strange issue today on SQL Server 2005. We had a stored procedure update that added new parameters to part of it. One database out of ~150 or more seem to not work properly. Using ...
7
votes
1answer
2k views

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 ...
3
votes
2answers
2k views

View scripts ran by a specific user?

I read in a stackoverflow post that you can restore scripts from the past 24 hours by using this query: Use <database> SELECT execquery.last_execution_time AS [Date Time], execsql.text AS ...
2
votes
0answers
201 views

plan_generation_num keeps incrementing, why?

I have a query that is executed using sp_executesql code is as follows DECLARE @rsk NVARCHAR(1) = '1', @ppCityID NVARCHAR(3); SET @ppCityID = CAST(@cityID AS NVARCHAR(3)); exec sp_executesql ...
2
votes
1answer
119 views

Will cleaning data cache help to resolve compiler memory pressure?

I currently experience RESOURCE_SEMAPHORE_QUERY_COMPILE waits causing CPU spike 100% that lasts 10-20 mins and disappears only by completely stopping traffic. I figured it could be a memory pressure ...
2
votes
2answers
420 views

Stored procedure compilation blocking due to many parameters to sp_executesql

I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: ...