Questions tagged [execution-plan]
The strategy selected by the query optimizer to process a query.
1,135
questions
20
votes
2
answers
3k
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,228
8
votes
1
answer
5k
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
A ...
- 529
1
vote
1
answer
172
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], [CompanyName], [City],...
- 791
21
votes
1
answer
8k
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 ...
- 14.6k
3
votes
2
answers
5k
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 ...
- 225
10
votes
2
answers
11k
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 ...
- 1,298
4
votes
2
answers
2k
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 ...
- 587
20
votes
2
answers
11k
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 ...
- 1,295
15
votes
1
answer
5k
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 ...
- 587
2
votes
1
answer
328
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 ...
- 2,407
2
votes
2
answers
242
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 ...
- 1,639
4
votes
1
answer
164
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 ...
- 1,342
3
votes
1
answer
163
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 ...
- 195
3
votes
1
answer
3k
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 ...
- 593
11
votes
1
answer
9k
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.
...
- 581
2
votes
2
answers
3k
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 ...
- 533
2
votes
1
answer
3k
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,
ISNULL(s....
- 287
3
votes
1
answer
199
views
PostgreSQL + query planner + amount of entities
I'm working on a college assignment about how the query planner uses statistics and makes the most optimal query plan.
I have read the 57.1. Row Estimation Examples article and I know how PostgreSQL ...
- 131
26
votes
1
answer
21k
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 ...
- 969
33
votes
2
answers
11k
views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
- 2,139
5
votes
1
answer
887
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 ...
- 53
2
votes
3
answers
3k
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 OPTION(...
- 383
2
votes
2
answers
948
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 ...
- 183
10
votes
2
answers
2k
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 ...
- 654
2
votes
1
answer
475
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:
...
- 133
4
votes
1
answer
238
views
How can a key lookup with zero executions and zero actual rows take time?
I have a query that is taking about 10 seconds to run and I am trying to optimize it.
All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the ...
- 516
2
votes
1
answer
637
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 ...
- 6,662
5
votes
1
answer
13k
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,393
5
votes
1
answer
2k
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 ...
- 153
13
votes
5
answers
3k
views
Does SentryOne Plan Explorer work?
Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?
It looks like it shows the hot path in color commpared to SSMS's ...
- 255
6
votes
1
answer
510
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
...
- 6,662
3
votes
2
answers
381
views
Comparing two XML query plans
Hopefully a simple one, if I have two (fairly complex, 30-40 node) .sqlplan files (from the same SQL text) what is a good way to compare them side by side? Or just have it tell me what it did ...
- 11.2k
42
votes
3
answers
80k
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
...
- 2,335
5
votes
3
answers
2k
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 ...
- 633
8
votes
4
answers
11k
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?
ahsan