Questions tagged [execution-plan]

The strategy selected by the query optimizer to process a query.

Filter by
Sorted by
Tagged with
86 votes
4 answers
48k views

Why is my query suddenly slower than it was yesterday?

[Salutations] (check one) [ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer, I have a (check all that apply) [ ] query [ ] stored procedure [ ] database thing maybe that ...
Erik Darling's user avatar
  • 40.5k
70 votes
4 answers
58k views

Index Seek vs Index Scan

Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan. What is the difference between and index seek and an index ...
Greg's user avatar
  • 3,242
47 votes
3 answers
12k views

Excessive sort memory grant

Why is this simple query granted so much memory? -- Demo table CREATE TABLE dbo.Test ( TID integer IDENTITY NOT NULL, FilterMe integer NOT NULL, SortMe integer NOT NULL, Unused ...
Paul White's user avatar
  • 83.7k
43 votes
3 answers
19k views

Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN

So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
RThomas's user avatar
  • 3,396
42 votes
3 answers
82k views

Execution Plan Basics -- Hash Match Confusion

I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join: select Posts.Title, Users.DisplayName From Posts JOIN Users on ...
Kyle Brandt's user avatar
  • 2,345
40 votes
2 answers
16k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[...
Martin Smith's user avatar
  • 84.4k
38 votes
2 answers
12k views

How (and why) does TOP impact an execution plan?

For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
David's user avatar
  • 483
36 votes
3 answers
8k views

Why does SQL Server use a better execution plan when I inline the variable?

I have a SQL query that I am trying to optimize: DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962' SELECT Id, MIN(SomeTimestamp), MAX(SomeInt) FROM dbo.MyTable WHERE Id = @...
Rainbolt's user avatar
  • 800
35 votes
2 answers
5k views

If a CTE is defined in a query and is never used, does it make a sound?

Do unused CTEs in queries affect performance and / or alter the generated query plan?
J.D.'s user avatar
  • 37.1k
33 votes
2 answers
12k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
Tom Hunter's user avatar
  • 2,159
33 votes
1 answer
41k views

Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

I am running a query that is giving the warning about a memory Excessive Grant. There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
Marcello Miorelli's user avatar
32 votes
1 answer
15k views

SET STATISTICS IO- worktable/workfile

I am executiong query, that produces plan: Statistics IO: Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-...
Jānis's user avatar
  • 1,248
31 votes
3 answers
53k views

Eliminate Key Lookup (Clustered) operator that slows down performance

How can I eliminate a Key Lookup (Clustered) operator in my execution plan? Table tblQuotes already has a clustered index (on QuoteID) and 27 nonclustered indexes, so I am trying not to create any ...
Serdia's user avatar
  • 687
28 votes
2 answers
16k views

"Recheck Cond:" line in query plans with a bitmap index scan

This is a spin-off from comments to the previous question: Postgres 9.4.4 query takes forever Using PostgreSQL 9.4, there always seems to be a Recheck Cond: line after bitmap index scans in query ...
Erwin Brandstetter's user avatar
28 votes
1 answer
846 views

SQL Server 2014: any explanation for inconsistent self join cardinality estimate?

Consider the following query plan in SQL Server 2014: In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 ...
Geoff Patterson's user avatar
26 votes
1 answer
27k views

Index not used with = ANY() but used with IN

Table t has two indexes: create table t (a int, b int); create type int_pair as (a int, b int); create index t_row_idx on t (((a,b)::int_pair)); create index t_a_b_idx on t (a,b); insert into t (a,b) ...
Clodoaldo's user avatar
  • 1,135
26 votes
1 answer
21k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
RaoulRubin's user avatar
25 votes
3 answers
4k views

Index on Persisted Computed column needs key lookup to get columns in the computed expression

I have a persisted computed column on a table which is simply made up concatenated columns, e.g. CREATE TABLE dbo.T ( ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY, A ...
GarethD's user avatar
  • 683
25 votes
1 answer
29k views

Operator used tempdb to spill data during execution with spill level 2

I am struggling to minimise the cost of sort operation on a query plan with the warning Operator usedtempdbto spill data during execution with spill level 2 I have found several posts related to ...
Marcello Miorelli's user avatar
24 votes
1 answer
2k views

Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

I have this table: CREATE TABLE [dbo].[Accounts] ( [AccountId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID(), -- WHATEVER other columns ); GO CREATE UNIQUE CLUSTERED INDEX [AccountsIndex] ...
sharptooth's user avatar
  • 1,301
24 votes
1 answer
4k views

Does SQL Server cache the result of a multi-statement table-valued function?

A multi-statement table-valued function returns its result in a table variable. Are these results ever reused, or is the function always fully evaluated every time it is called?
Paul White's user avatar
  • 83.7k
23 votes
2 answers
2k views

Queries without Good Enough Plan Found

I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are: What are all ...
LCJ's user avatar
  • 899
22 votes
1 answer
5k views

When does SQL Server warn about an Excessive Memory Grant?

What are the conditions that produce an "Excessive Grant" execution plan warning? The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 5128 KB, ...
Paul White's user avatar
  • 83.7k
21 votes
1 answer
28k views

Why is "select *" faster than "select top 500 *" in SQL Server? [duplicate]

I have a view, complicated_view -- there are a few joins and where clauses. Now, select * from complicated_view (9000 records) is faster, much faster, than select top 500 * from complicated_view ...
user45867's user avatar
  • 1,683
21 votes
1 answer
7k views

Should I be alarmed by this NO JOIN PREDICATE warning?

I'm troubleshooting the bits and pieces of a poorly-performing stored procedure. This section of the procedure is throwing a NO JOIN PREDICATE warning select method = case ...
swasheck's user avatar
  • 10.7k
21 votes
1 answer
6k views

Hash keys probe and residual

Say, we have a query like this: select a.*,b.* from a join b on a.col1=b.col1 and len(a.col1)=10 Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the ...
TheGameiswar's user avatar
  • 2,989
21 votes
1 answer
8k views

Why does SQL Server "Compute Scalar" when I SELECT a persisted computed column?

The three SELECT statements in this code USE [tempdb]; GO SET NOCOUNT ON; CREATE TABLE dbo.persist_test ( id INT NOT NULL , id5 AS (id * 5) , id5p AS (id ...
Nick Chammas's user avatar
  • 14.7k
20 votes
2 answers
11k views

Can you explain this execution plan?

I was researching something else when I came across this thing. I was generating test tables with some data in it and running different queries to find out how different ways to write queries affects ...
Andrew Savinykh's user avatar
20 votes
3 answers
18k views

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
FilamentUnities's user avatar
20 votes
2 answers
5k views

How to measure or find cost of creating a query plan?

I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem ...
Jeroen's user avatar
  • 879
20 votes
4 answers
1k views

Execution plan vs STATISTICS IO order

SQL Server graphical execution plans read right to left and top to bottom. Is there a meaningful order to the output generated by SET STATISTICS IO ON? The following query: SET STATISTICS IO ON; ...
user avatar
20 votes
1 answer
15k views

Warning in query plan "Cardinality Estimate"

create table T(ID int identity primary key) insert into T default values insert into T default values go select cast(ID as varchar(10)) as ID from T where ID = 1 The query above has a warning in ...
Mikael Eriksson's user avatar
20 votes
2 answers
3k views

T-SQL query using completely different plan depending on number of rows I'm updating

I have a SQL UPDATE statement with a "TOP (X)" clause, and the row I'm updating values in has about 4 billion rows. When I use "TOP (10)", I get one execution plan that executes almost instantly, but ...
SqlRyan's user avatar
  • 1,228
19 votes
1 answer
13k views

Difference between Seek Predicate and Predicate

I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise. I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek ...
Greg's user avatar
  • 3,242
19 votes
1 answer
1k views

Forced plans on readable secondaries

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: Plan Guides Query ...
Paul White's user avatar
  • 83.7k
18 votes
2 answers
3k views

CROSS APPLY produces outer join

In answer to SQL counting distinct over partition Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER (): SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT ...
Paul White's user avatar
  • 83.7k
18 votes
1 answer
1k views

Single-row INSERT...SELECT much slower than separate SELECT

Given the following heap table with 400 rows numbered from 1 to 400: DROP TABLE IF EXISTS dbo.N; GO SELECT SV.number INTO dbo.N FROM master.dbo.spt_values AS SV WHERE SV.[type] = N'P' ...
Paul White's user avatar
  • 83.7k
17 votes
1 answer
9k views

SQL Server 2016 Bad Query Plan locks up DB once a week

Once a week, for the past 5 weeks, around the same time of day (early morning, may be based on user activity when people are starting to use it), SQL Server 2016 (AWS RDS, mirrored) starts timing out ...
Professional Sounding Name's user avatar
17 votes
2 answers
5k views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
Hannah Vernon's user avatar
  • 70k
17 votes
1 answer
12k views

MERGE deadlocking prevention

In one of our databases we have a table that is intensively concurrently accessed by multiple threads. Threads do update or insert rows via MERGE. There are also threads that delete rows on occasion, ...
i-one's user avatar
  • 2,354
16 votes
2 answers
7k 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: command....
Magier's user avatar
  • 4,777
16 votes
2 answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
Danielle Paquette-Harvey's user avatar
16 votes
1 answer
2k views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
Akash's user avatar
  • 1,032
16 votes
2 answers
16k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
Neo's user avatar
  • 435
15 votes
1 answer
5k views

Execution plan with multiple missing indexes

If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the ...
Giorgi's user avatar
  • 768
15 votes
5 answers
12k views

View locks acquired during query execution (SQL Server)

Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?
Faisal Mansoor's user avatar
15 votes
3 answers
4k views

Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme?

The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very ...
usr's user avatar
  • 7,330
15 votes
1 answer
495 views

Which costing factors go into the optimizer choosing different types of spools?

Spoolum In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries. Read only queries, particularly on the inner ...
Erik Darling's user avatar
  • 40.5k
15 votes
3 answers
9k views

Why is selecting all resulting columns of this query faster than selecting the one column I care about?

I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo. This is the query: select top 1000 c.ID from ATable a join ...
L. Miller's user avatar
  • 253
15 votes
1 answer
5k views

Interpreting SQL Server's Showplan XML

I just rolled out a feature on my site http://sqlfiddle.com that allows users to view the raw execution plans for their queries. In the case of PostgreSQL, MySQL, and (to some extent) Oracle, looking ...
Jake Feasel's user avatar

1
2 3 4 5
24