Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,136
questions
86
votes
4
answers
46k
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 ...
- 37.6k
69
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 ...
- 3,212
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 ...
- 80.3k
43
votes
3
answers
18k
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 ...
- 3,356
42
votes
3
answers
80k
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
...
- 2,335
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)[...
- 81.3k
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 ...
- 483
34
votes
3
answers
7k
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 = @...
- 780
34
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?
- 32.2k
33
votes
2
answers
11k
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)
) ...
- 2,139
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-...
- 1,248
30
votes
1
answer
35k
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 ...
- 15.6k
28
votes
2
answers
15k
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 ...
- 167k
28
votes
1
answer
817
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 ...
- 8,277
27
votes
3
answers
48k
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 ...
- 647
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 ...
- 969
25
votes
3
answers
3k
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 ...
- 683
25
votes
1
answer
26k
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 ...
- 15.6k
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]
...
- 1,291
24
votes
1
answer
3k
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?
- 80.3k
24
votes
1
answer
22k
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)
...
- 1,095
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 ...
- 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, ...
- 80.3k
21
votes
1
answer
27k
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
...
- 1,613
21
votes
1
answer
6k
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 ...
- 10.6k
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 ...
- 2,929
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 ...
- 14.6k
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 ...
- 1,295
20
votes
3
answers
17k
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 ...
- 681
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;
...
- 3,095
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 ...
- 21.9k
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 ...
- 1,228
19
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 ...
- 859
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 ...
- 80.3k
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'
...
- 80.3k
17
votes
1
answer
12k
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 ...
- 3,212
17
votes
1
answer
946
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 ...
- 80.3k
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 ...
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?
- 69.1k
16
votes
2
answers
6k
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....
- 4,717
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 ...
- 1,999
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 ...
- 1,002
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 ...
- 435
16
votes
1
answer
10k
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, ...
- 2,334
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 ...
- 758
15
votes
5
answers
11k
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?
- 253
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 ...
- 7,250
15
votes
1
answer
483
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 ...
- 37.6k
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 ...
- 587
14
votes
3
answers
14k
views
How does one investigate the performance of a BULK INSERT statement?
I am mainly a .NET developer using Entity Framework ORM. However, because I don't want to fail in using the ORM, I am trying to understand what happens within the data layer (database). Basically, ...
- 1,261