The strategy selected by the query optimiser to process a query.
16
votes
2answers
931 views
Guide to reading, comparing, and interpretting Execution Plans in SQL Server
I'm looking for a good, well-written and explained guide on reading and comparing execution plans in SQL Server. This is for more my own edification as well as for sharing with others.
If you want ...
15
votes
2answers
504 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 ...
14
votes
3answers
7k 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
...
13
votes
2answers
570 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 ...
12
votes
3answers
1k 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 ...
10
votes
2answers
817 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 ...
10
votes
1answer
486 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 ...
9
votes
2answers
899 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 ...
9
votes
1answer
201 views
Does SQL Server 2008 store the creation date of execution plans?
We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
9
votes
1answer
1k 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 ...
8
votes
1answer
316 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 ...
8
votes
1answer
156 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 ...
7
votes
4answers
338 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 ...
7
votes
3answers
281 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
votes
1answer
715 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 ...
6
votes
3answers
173 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 ...
6
votes
2answers
303 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 ...
5
votes
2answers
227 views
Correlated subquery and join: still the same exectution plan?
I have a correlated subquery like this (from BOL):
SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = ...
5
votes
1answer
312 views
The use of NOT logic in relation to indexes
According to MSFT's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:
Neither leading wildcard characters not NOT logic allow the query optimizer to use ...
5
votes
1answer
371 views
Execution plan using B+ tree index, but also sorts
I'm using Oracle sqlplus. And I have the following query:
SELECT fooID from foo MINUS
SELECT fooID from bar;
I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
4
votes
4answers
723 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?
4
votes
3answers
442 views
Large set of execution plans hangs up SSMS
I have a stored procedure that takes about 3-5 seconds that I'm trying to understand so I want an execution plan for it. When I run it in SQL Server Management Studio with Execution Plan enabled, it ...
4
votes
2answers
531 views
How do I get the execution plan for a view?
I have a schema with a number of views. I need to check the execution plans to make sure the appropriate indexes are in place and being used.
How do I do this?
I'd rather not have to copy and paste ...
4
votes
2answers
241 views
Oracle build_plan_xml visualizer
Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML
I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
1answer
296 views
how to improve performance by using or not using table variables
I have a table variable:
DECLARE @to_process TABLE
(
[Id] [bigint] NOT NULL,
[SequenceId] [bigint] NOT NULL,
...
)
INSERT INTO @to_process
( Id
, SequenceId
...
)
SELECT
...
4
votes
1answer
68 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?
4
votes
2answers
176 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
4
votes
1answer
270 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
...
4
votes
1answer
687 views
Why does it take so long to calculate an Execution Plan?
One of our customers has just upgraded to a new server.
For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.
...
4
votes
1answer
111 views
Filtered indices: Why include the filtered-on field?
This just drives me mad.
Consider a simple table with irrelevant columns removed:
create table boxes (
row_id int not null identity(1,1) primary key,
location varchar(15) null,
dismantled bit ...
4
votes
1answer
39 views
Are database query optimizers aware of storage performance differences?
As I understand it, the query optimizer in SQL Server (or any other RDBMS, really) isn't aware of the performance of the storage underneath the database, and will make decisions as if all storage has ...
4
votes
1answer
430 views
SQL Server 2008 query planner failing after index drop & recreate
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
3
votes
2answers
61 views
Performance of different precedence pseudocolumn solutions with LIMIT
I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.
The OP wanted to get the first matching row given ...
3
votes
1answer
492 views
Clustered Index Update on Nonclustered Columns
Using: SQL Server 2008 R2
I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are ...
3
votes
1answer
86 views
How to determine cause of runtime increase given two query plans with SpillToTempDb warning
Two actual query plans were captured for two executions of one query:
Plan "Fast" took ~1s and occurs about 90% of the time.
Plan "Slow" took ~16s.
Since the graphical plans look ...
3
votes
1answer
109 views
How Can the Same Query in Two Nearly Identical Instances Generate Two Different Execution Plans?
Server A and Server B have identical hardware and instance configurations (A is Production, B is QA). B's DBs were restored from A's backups from one week ago. I was provided this query by the ...
3
votes
1answer
100 views
Do operations on indexed views happen in parallel?
When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel.
Is ...
3
votes
1answer
95 views
can sql generate a good plan for this procedure?
I have read that procs that do not always do the same thing per se, will not always have a good plan generated. That is (correct me if I'm wrong), if I have a proc that if the day is even it reads ...
3
votes
1answer
135 views
Execution plan in simple query in PgSQL depends on offset clause, why?
Update on 2013-01-16 11:08: see below
I am working on PostgreSQL 8.4.14 with the following table fed with 53k lines:
CREATE TABLE botany.taxon_data_cache (
taxon_id int NOT NULL,
...
2
votes
3answers
92 views
How can I remove a bad execution plan from SQL Azure?
DBCC FREEPROCCACHE doesn't work in SQL Azure. How else can I force a plan to kick itself out of the cache in a way that won't hurt a production system (i.e. I can't just go alter tables willy nilly)? ...
2
votes
3answers
604 views
How does NHibernate handle execution plan?
I heard from a podcast that there are no ORMs that have a good solution for execution plan reuse. It will lead to increased execution plan cache which affects the performance.
How does NHibernate ...
2
votes
1answer
115 views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
2
votes
2answers
673 views
Why is query using Clustered Index when it shouldn't?
Let us presume I have a table named Category in a SQL Server 2005 database. Category has category_id (bigint, identity) as its primary key and name (nvarchar(50)). There is obviously a clustered ...
2
votes
1answer
232 views
Postgres planer JOIN removal
I have two tables, one contains a lot of rarely update data, and one contains a little of frequently update data. Every record in second table has corresponding record in the first, like following:
...
2
votes
1answer
68 views
Fast paginated result, for various filter clauses
I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I ...
2
votes
3answers
545 views
Revert to old plan in Oracle
A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday. One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes. ...
2
votes
1answer
515 views
Is it correct, order of where clause doesn't matter when it is used with join?
I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.
I tried options like disabling rules and checked actual query ...
2
votes
1answer
144 views
Non-clustered Index Update not displayed in execution plan [duplicate]
Possible Duplicate:
Nonclustered Index Insert
I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
2
votes
1answer
124 views
How are foreign keys resolved when creating tables in batch?
I am creating a database version management tool with the goal of being able to recreate a database from scratch using text/sql files contained in source control. As part of establishing a baseline ...
2
votes
1answer
216 views
Query tuning - SQL Server
One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
