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

learn more… | top users | synonyms

4
votes
3answers
2k 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
1answer
232 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 ...
4
votes
2answers
190 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 ...
4
votes
2answers
683 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
4
votes
2answers
474 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
119 views

Understanding below execution plan

I have two tables tb1 and tb2, tb1 doesn't have any indexes on it. I populated tb1 with 1000000 rows and tb2 has 500 rows in it and with a clustered index on ID column. To understand nested loop ...
4
votes
1answer
380 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 ...
4
votes
2answers
219 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
133 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
95 views

Should I use a subquery to help SQL Server find the correct plan

I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
4
votes
1answer
87 views

SQL Server : parsing with XPath Performance

I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I ...
4
votes
2answers
185 views

SQL Server doesn't use expected index

I have the following query (simplified from an Entity Framework query, I don't have direct control of the SQL): SELECT TOP 15 HouseHoldingRuns.Id, ( SELECT TOP 1 ManCoId FROM ( SELECT TOP ...
4
votes
0answers
44 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 ...
4
votes
1answer
1k 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
1answer
425 views

Execution Plans Differ for the Same SQL Statement

As a background to this question the details can be found at this link. In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the ...
3
votes
2answers
93 views

When does SQL Server determine to use a new index?

I have been performing some maintenance on a server that runs some software the company bought, so I can't really change how the queries have been written. I have used some tools to determine some of ...
3
votes
3answers
1k 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 ...
3
votes
2answers
140 views

Changing metadata changes query output issue [duplicate]

Recently stumbled across the following problem: changing metadata changes query output. Here is how to get it. CREATE TABLE [dbo].[prod] ( [ID] [tinyint] NOT NULL, [values] [tinyint] NOT NULL ) GO ...
3
votes
1answer
1k 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
476 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
448 views

sql server partitioned view execution plan questions

I have created a partitioned view for 3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint (respectivly for each table) datepart(year,[Date])=(2010) datepart(year,[Date])=(2011) ...
3
votes
1answer
136 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
38 views

Execution plan for COMPUTE BY

I understand that the COMPUTE BY feature is deprecated. Out of curiosity I would like to know how the query processor executes a COMPUTE BY query. The execution plan does not contain any hint: How ...
3
votes
2answers
335 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 ...
3
votes
1answer
119 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%. ...
3
votes
2answers
2k 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 ...
3
votes
1answer
457 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 ...
3
votes
1answer
112 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
55 views

Why is the secondary selective index not used when the where clause filters on `value()`?

Setup: create table dbo.T ( ID int identity primary key, XMLDoc xml not null ); insert into dbo.T(XMLDoc) select ( select N.Number for xml path(''), type ) from ( ...
3
votes
1answer
137 views

Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] ...
3
votes
1answer
219 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, ...
3
votes
1answer
2k views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
3
votes
1answer
954 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 ...
3
votes
1answer
107 views

Simple SELECT takes forever, but works as a subquery. Why?

I'm running a MySQL 5.1 server and I have a MyISAM table called my_table with nearly 5 million records. Its structure is the following: ...
3
votes
1answer
574 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, ...
3
votes
1answer
1k 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 ...
3
votes
0answers
88 views

Bad Plan after a couple of days

I have this query that runs fine for a couple of days (from the application) and then slows down to where it can't even finish. If I rebuild the plan, it flies. There is a signifigant amount of data ...
2
votes
4answers
2k 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
2answers
79 views

On which server does a query execute?

Given two Microsoft SQL servers: Say ServerA has a view titled "View1": SELECT * FROM ServerB.database.dbo.Table1 WHERE Column1 IN (1,2,3) and a stored procedure "sp_Foo": SELECT * FROM View1 WHERE ...
2
votes
1answer
116 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
2
votes
1answer
4k 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 ...
2
votes
1answer
290 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
1answer
2k 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, ...
2
votes
1answer
294 views

Is there any way to save the execution plan in a file in PostgreSQL?

By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. For example, if the optimizer chooses to do a hash join for a two-table ...
2
votes
2answers
850 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
317 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
73 views

Execution plan has TOP operator for query without TOP or ORDER BY

I have product sales by month temporary table.(subset of much larger historical table) I'm running query to find new product not previously sold in the previous months for a specified 6 month range. ...
2
votes
1answer
106 views

Same query sometimes uses a Table Spool

I'm checking out a query which exhibits strangely inconsistent performance. Using SolarWinds DPA, I can see that the query sometimes uses a Table Spool, sometimes it doesn't. When it actually used a ...
2
votes
1answer
105 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 ...
2
votes
1answer
2k 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 ...