Tagged Questions

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

learn more… | top users | synonyms

10
votes
1answer
4k 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
547 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
2k 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
823 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
515 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
293 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
490 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 ...
2
votes
1answer
2k 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
869 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
484 views

The use of NOT logic in relation to indexes

According to Microsoft'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 ...
5
votes
1answer
385 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 ...
15
votes
3answers
15k 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
868 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
2k 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
0answers
2k views

Guide to reading, comparing, and interpretting Execution Plans in SQL Server [closed]

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 ...