3
votes
1answer
60 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
8
votes
1answer
213 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 ...
3
votes
2answers
105 views

Multicolumn statistics on large table yielding poor estimations

I have a rather large table (~200 million rows), and although the statistics are up to date using WITH FULLSCAN, is it possible that my histogram (limited to 200 steps) is simply too broad for the ...
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
1answer
61 views

Non-Clustered Index key columns to cover a variable where clause

To whoever reads this, sorry for the long question and thanks so much for your time. I'm needing some help on understanding what columns should be key/non key in the following situation, and what ...
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 ...
4
votes
1answer
140 views

Why clustered index scan?

I just started to learn to optimize my queries and to analyze their query plans. I thought this query would generate nonclustered index seek + key lookup. SELECT ct.* FROM Person.ContactType AS ct ...
2
votes
1answer
74 views

Understanding SQL Server plan cache

I've been reading about the SQL Server plan cache and optimizing for adhoc workloads, etc. I'm fairly new to the topic and have a few general questions to help paint a better picture: Suppose I have ...
1
vote
0answers
61 views

What to look for in query execution plans? [closed]

Assuming the query execution is slow, I have collected the xml plan. What should I look for in the actual execution plan. What are the top 10 things I need to watch out for? I know this is a broad ...
8
votes
1answer
172 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 ...
0
votes
1answer
83 views

SQL Server Index Scan Actual Executions

I have a fairly complicated query and running it on two databases with same schemas, but with different data produces different result. On database D1 the elapsed time is around 1 second, and the ...
3
votes
3answers
704 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 ...
3
votes
1answer
170 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
14
votes
4answers
557 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
votes
2answers
535 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
2answers
233 views

Updating a table efficiently using JOIN

I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two ...
6
votes
0answers
134 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 ...
6
votes
1answer
268 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.
7
votes
4answers
2k 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 ...
6
votes
3answers
341 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 ...
3
votes
1answer
219 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 ...
2
votes
4answers
454 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)? ...
8
votes
1answer
160 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
1answer
847 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 ...
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 ...
16
votes
2answers
1k 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 ...
2
votes
1answer
283 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 ...
8
votes
3answers
502 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 ...
1
vote
1answer
137 views

Binding errors with dependent stored procedures

We are getting strange errors on one of our stored procedures. This stored procedure calls other stored procedures & we are getting errors relating to columns that cannot be found in the sub ...
2
votes
1answer
218 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 ...
12
votes
2answers
2k 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 ...
2
votes
1answer
240 views

Does a change in a query executed with sp_executesql cause recompilations?

I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure. ...
3
votes
1answer
105 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 ...
15
votes
2answers
889 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 ...
5
votes
1answer
584 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 ...
13
votes
1answer
1k 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 ...
2
votes
1answer
617 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 ...
13
votes
3answers
2k 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 ...
9
votes
1answer
1k 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 ...
2
votes
1answer
244 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 ...
1
vote
2answers
142 views

Execution Plans Query

I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard ...
3
votes
1answer
768 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 ...
5
votes
1answer
1k 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. ...
9
votes
1answer
3k 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 ...
1
vote
3answers
1k 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 ...
7
votes
2answers
413 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 ...
1
vote
1answer
1k views

What I can do to speed up Key Lookup in my Query

I am using SQL2005 and against them I calling query simila to this declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null); insert ...
5
votes
1answer
397 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 ...
14
votes
3answers
11k 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 ...