Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
169
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
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
9
votes
2
answers
1k
views
Index that is not used, yet influences query
I've got a PostgreSQL 9.3 table with some numbers and some additional data:
CREATE TABLE mytable (
myid BIGINT,
somedata BYTEA
)
This table currently has about 10M records and takes 1GB of ...
- 289
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
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
8
votes
1
answer
609
views
Optimizer not choosing index union plan
Why is the following query slow?
select count(*)
from [dbo].[mt_dispatch_link]
, [dbo].[_mt_dispatch] [_mt_dispatch]
where (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id
...
- 2,187
7
votes
3
answers
14k
views
Why does my query run fast in Environment A, but slow in Environment B?
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!
Environments are supposed to be the same, so what should I do and/or ...
- 10.9k
7
votes
1
answer
1k
views
PostgreSQL partial index unused when created on a table with existing data
In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
- 587
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
8
votes
1
answer
5k
views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
A ...
- 529
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
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
7
votes
1
answer
2k
views
Inconsistent statistics on jsonb column with btree index
I noticed that the performance of a query involving a jsonb column varied significantly between VACUUM ANALYZE runs while testing it. I get completely different execution plans seemingly randomly ...
2
votes
3
answers
3k
views
Why would recompile query hint result in different plan for same adhoc statement after freeproccache?
This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.
I've got an adhoc sql statement that gets a different query plan solely because of OPTION(...
- 383
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
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
13
votes
1
answer
3k
views
How does SQL Server's optimizer estimate the number of rows in a joined table?
I am running this query in the AdventureWorks2012 database:
SELECT
s.SalesOrderID,
d.CarrierTrackingNumber,
d.ProductID,
d.OrderQty
FROM Sales.SalesOrderHeader s
JOIN Sales....
- 2,609
8
votes
1
answer
906
views
Inserting with implicit type conversion causes warning for cardinality estimates
I noticed this while doing some performance testing recently. When I insert a value into a column that will require an implicit conversion (e.g. bigint into nvarchar), I get a warning:
Type ...
- 183
6
votes
1
answer
2k
views
Stored procedure with recompile
I have this stored procedure..when i told the developers that it is not recommended to use with Recompile option they replied "This was because this SP can be called with a number of different params ...
- 1,383
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
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
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
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
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
14
votes
2
answers
1k
views
Constant Scan spooling
I have a table with a few dozens of rows. Simplified setup is following
CREATE TABLE #data ([Id] int, [Status] int);
INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);
And I have a ...
- 2,334
11
votes
1
answer
23k
views
Index usage on a temporary table
I have two rather simple queries. The first query
UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;
and it's plan
duration: 0.125 ms plan:
Query Text: UPDATE ...
- 223
10
votes
1
answer
552
views
Why does a plan with FULL optimization show simple parameterization?
I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized.
Then why is this plan showing Full Optimization, and ...
- 37.6k
7
votes
1
answer
6k
views
Query is slow in SQL Server 2014, fast in SQL Server 2012
During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues.
One of the queries that completes within seconds on SQL ...
- 73
5
votes
1
answer
2k
views
Why does this implicit join get planned differently than an explicit join?
In this answer I explain SQL-89s implicit syntax.
But I noticed different query plans while playing around:
EXPLAIN ANALYZE
SELECT *
FROM (values(1)) AS t(x), (values(2)) AS g(y);
...
- 60.4k
4
votes
4
answers
837
views
Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR
I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:
SELECT *
FROM Users u
LEFT JOIN Comments c
ON u.Id = c....
- 1,741
3
votes
1
answer
4k
views
Are implicit joins as efficient as explicit joins in Postgres?
It's often convenient to write:
SELECT *
FROM t1 # ... +many more tables
INNER JOIN t2 ON (t1.id = t2.col)
INNER JOIN t3 ON (t1.id = t3.col)
INNER JOIN t4 ON (t1.id = t4.col)
...
as a cross join ...
- 171
3
votes
1
answer
637
views
How can I find out if a SQL function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
- 60.4k
1
vote
1
answer
992
views
How to use index for simple time range join?
I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp.
This is a follow up of this question:
Why does a CTE ...
- 233
0
votes
3
answers
21k
views
Comparing values between two tables
How to compare values between two different tables?
I have two tables as shown below:
Table 'A'
sql_id OPERATION OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc TABLE ACCESS FULL
...
- 29
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
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
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
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
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
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
14
votes
3
answers
2k
views
Concatenation Physical Operation: Does it guarantee order of execution?
In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like:
select 'A' as c union all select 'B'
Could return two rows in any order (although, in practice ...
- 2,222
12
votes
1
answer
2k
views
Query Store Force Plan feature doesn't work
Query Store Force plan feature doesn't appear to be enforcing the plan.
I'm aware of Query Store - Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query ...
- 231
11
votes
2
answers
2k
views
Slow fulltext search due to wildly inaccurate row estimates
Fulltext queries against this database (storing RT (Request Tracker) tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB.
The ...
- 213
10
votes
2
answers
2k
views
Code creates different plan when ran ad-hoc vs. in a stored procedure
I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.
I have rebuilt all the indexes for the tables used by the ...
- 654
10
votes
1
answer
14k
views
Have Postgresql query planner use nested loop w/ indices over hash join
I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
- 203
10
votes
2
answers
944
views
Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer?
The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen?
Microsoft says:
The Query Optimizer must analyze the ...
- 4,974
10
votes
3
answers
4k
views
Are SQL Server statistics stored in database or buffer pool?
Just wondering are statistics kept in the database but not in the memory? If I backup/restore the database from a prod server to a development server, would it keep the same statistics so that ...
- 331
9
votes
2
answers
6k
views
Are there side effects to Postgres 12's NOT MATERIALIZED directive?
I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
- 193
8
votes
4
answers
11k
views
When is a full table scan better than index scan?
What are some scenarios (in SQL Server) in which a full table scan is better than an index scan?
ahsan
8
votes
1
answer
7k
views
Index seek much slower with OR condition compared with separate SELECTs
Based on these questions and the answers given:
SQL 2008 Server - performance loss possibly connected with a very large table
Large table with historical data allocates too much of SQL Server 2008 ...
- 617