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

learn more… | top users | synonyms

26
votes
3answers
10k 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 ...
26
votes
5answers
21k 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 ...
22
votes
3answers
2k 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 ...
19
votes
2answers
4k 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 ...
18
votes
0answers
2k views

Guide to reading, comparing, and interpretting Execution Plans in SQL Server [closed]

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 ...
17
votes
3answers
23k 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 ...
16
votes
4answers
861 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; ...
15
votes
2answers
4k 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 ...
15
votes
1answer
3k 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 ...
15
votes
2answers
2k 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
4k 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 ...
13
votes
1answer
298 views

SQL Server recreating plans each day

We have this problem in our production environment. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) - Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1). SQL Server ...
12
votes
1answer
349 views

How deterministic is execution plan creation in SQL Server?

Given the following constants: The same database with the same structure (tables, indexes, etc) The same data The same SQL Server and hardware configuration The same statistics The same SET options ...
11
votes
2answers
745 views

Why does my WHERE clause benefit from an “included” column?

According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index. I have this definition: CREATE TABLE [dbo].[JobItems] ( ...
10
votes
1answer
787 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 ...
10
votes
3answers
1k 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 ...
10
votes
1answer
2k 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 ...
10
votes
2answers
362 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
10
votes
1answer
6k 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 ...
9
votes
2answers
6k 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 ...
9
votes
1answer
2k 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 ...
8
votes
1answer
902 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 ...
8
votes
1answer
499 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
8
votes
1answer
364 views

Does SQL Sentry Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the ...
8
votes
1answer
353 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?
8
votes
2answers
130 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
8
votes
3answers
90 views

Lots of “FETCH API_CURSOR0000…” on sp_WhoIsActive ( SQL Server 2008 R2 )

I have a strange situation. Using sp_whoisactive I can see this: Ok, with this query, I can see what is triggering ( does this word exists in english? ) it: SELECT c.session_id, c.properties, ...
7
votes
3answers
1k 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 ...
7
votes
2answers
437 views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
7
votes
2answers
676 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 ...
7
votes
1answer
975 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 ...
7
votes
0answers
56 views

Two servers, different execution plans. Several orders of magnitude difference in run time

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my ...
6
votes
2answers
406 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 = ...
6
votes
2answers
640 views

Query Plans not retained insufficient memory errors

We have been experiencing memory issues with SQL Server. We first realised we had a problem when we started getting timeouts and login errors: A connection was successfully established with the ...
6
votes
1answer
381 views

Why does the plan cache contain estimated and not actual execution plans?

I can think of many reasons behind this decision of storing estimated plans in the plan cache and not the actual plan. But I can't find the "correct" answer.
6
votes
2answers
310 views

Unexpected Implicit conversion in a procedure

I have a procedure like this (simplified): CREATE PROCEDURE test @userName VARCHAR(64) SELECT * FROM member M INNER JOIN order O ON M.MemberId=O.MemberId WHERE M.Username = @userName There is a ...
6
votes
1answer
681 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 ...
6
votes
1answer
84 views

Why is DistinctSort followed by TopNSort in my query plan?

I have the following definition: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, [ItemState] INT NOT NULL, [ItemCreationTime] DATETIME ...
6
votes
1answer
1k views

Optimize for ad-hoc workload

I know what this option does and how to enable it. My question is what things will happen if I were to enable this. Without giving too much info, our accounting system is a Microsoft Dynamics ...
6
votes
0answers
173 views

Need help understanding query plan: estimates vs. actuals [closed]

I'd like to understand a section of the query plan that seems to mess up my query. The picture shows that there's a clustered index seek, followed by compute scalar, followed by sort. The part ...
5
votes
1answer
639 views

The use of NOT logic in relation to indexes

According to Microsoft'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 ...
5
votes
4answers
235 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?
5
votes
3answers
3k views

SQL Server 2005 get execution plan of a overnight stored procedure

There is a overnight job runs long time. I have used profiler to get the trace and identified a statement (in a stored procedure) that takes most of execution time. Now I need to see the execution ...
5
votes
1answer
436 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 ...
5
votes
2answers
3k 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 ...
5
votes
1answer
2k 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. ...
5
votes
1answer
2k 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 ...
5
votes
1answer
682 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 ...
5
votes
1answer
123 views

Query Plans Change Performance Worse in SQL Server 2014

We recently upgraded our server from SQL Server 2008R2 to SQL Server 2014. We have a query that ran fine in 2008R2 but now in 2014 runs incredibly much slower and has a bad execution plan. I've done ...
5
votes
1answer
92 views

Warning for missing statistics in execution plan

I have a situation that I cant understand. My SQL SERVER execution plan tells me that I have missing statistics on the table, but the statistics is already created. Please see the screens. But if ...