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

learn more… | top users | synonyms

9
votes
1answer
518 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 ...
9
votes
1answer
607 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 ...
3
votes
1answer
184 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 ...
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 ...
4
votes
3answers
926 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 ...
2
votes
1answer
217 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 ...
0
votes
1answer
338 views

Query performance and join hints, plan cost, and duration

Having some trouble identifying why a query's duration would decrease when using OPTION (HASH JOIN) or OPTION (MERGE JOIN), although plan cost increases. Background I have a reporting database using ...
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. ...
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 = ...
2
votes
3answers
2k 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
180 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 ...
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
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 ...
5
votes
1answer
582 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 ...
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], ...
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
615 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 ...
4
votes
2answers
875 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
326 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 ...
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 ...
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 ...
3
votes
1answer
119 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
766 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. ...
2
votes
2answers
643 views

In DB2, how can I disable NLJOIN?

I want to indicate to the optimizer, via SET or optimization profiles, that NLJOIN shouldn't be used at all when optimizing a query. I've been looking for this for a while without any success. I ...
1
vote
1answer
909 views

SQL Server 2008 R2 sys.dm_exec_sql_text question

If i run the following query on our production server: SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time / 100000 AS MaxElapsedTime, ...
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 ...
5
votes
1answer
476 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 ...
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 ...
2
votes
2answers
781 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 ...
7
votes
2answers
411 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 ...
2
votes
1answer
265 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
2answers
457 views

Clustered monotonically increased index insert performance

I have a table with field Id (bigint, IDENTITY) as primary key and clustered index on it. I inserted 400 rows and saw execution plan. I got: the relative query cost for this insertion 36% and for this ...
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 ...
3
votes
1answer
699 views

View SQL 2008 sp_cursorexecute Underlying Query and Execution Plan

I'm performance tuning a Dynamics AX application and see in a SQL trace a long-running, high-I/O query of the form exec sp_cursorexecute 1073742882 ... When I try and run that query in a new SQL ...
5
votes
1answer
396 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
358 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 ...
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 ...
3
votes
3answers
759 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 ...
4
votes
4answers
1k 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?
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 ...