0
votes
1answer
49 views

Comparing execution plans [closed]

How does the optimizer compare different execution plans of the same query? Let's say we have two complex and structurally different execution plans, how can I know if they are providing the same ...
2
votes
0answers
45 views

Multi-Statement Template Plan Guide

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' ...
2
votes
0answers
48 views

Identical query & instance but other execution plans & performance [duplicate]

I have 2 identical queries . One is executed with parameters, the other is executed with hardcoded values. Somehow sql server is creating 2 different execution plans for the 2 queries. The first one ...
3
votes
2answers
528 views

SQL Query too slow on SUM function

I've a table with around 32 million rows having clustered unique index on CountryID,RetailerID,ProductID,DateID,EventID,TypeID and query is SELECT f.RetailerID , TypeID ...
6
votes
2answers
1k 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 ...
1
vote
1answer
152 views

Relative cost of two similar queries involving XML columns

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column. I checked the execution ...
4
votes
2answers
206 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 ...
5
votes
2answers
269 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 = ...
15
votes
2answers
887 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
vote
1answer
123 views

SQl code performance

What measurement do you know if this sql code is efficient based on execution plans? How do you know if this sql code is efficient based on execution plans? SELECT [CustomerID], ...
4
votes
1answer
121 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 ...
18
votes
2answers
1k 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 ...