HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Interpreting an execution plan

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
planinterpretingexecution

Problem

I need some help analysing some simple t-sql execution plans.
I have a table [User] with the following 3 columns:

Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50))

I have Created an Unique NonClustered Index for the Username column

The execution plan for a query filtering by FirstName:

select * from [User] where FirstName ='John'


shows a Clustered Index Scan with a total cost of 0,0033095

The execution plan for another query filtering by Username:

select * from [User] where Username = 'johndoe'


shows a nested loop containing an Index Seek followed by a Clustered Index Seek with a total cost of 0,00657038! this is twice as much as the cost of the previous query and I don't get it!

There is an index associated with the Username column and no index associated with FirstName, I would expect the second query to be faster.

Solution

Total cost is almost always not reliable for things like this

I would use STATISTICS IO and STATISTICS TIME instead

run both queries and look at the difference in reads

SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFF


and you can also look at the time by using this

SET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFF

Code Snippets

SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFF
SET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFF

Context

StackExchange Database Administrators Q#11689, answer score: 7

Revisions (0)

No revisions yet.