patternsqlMinor
Interpreting an execution plan
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:
shows a Clustered Index Scan with a total cost of 0,0033095
The execution plan for another query filtering by Username:
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.
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
run both queries and look at the difference in reads
and you can also look at the time by using this
I would use
STATISTICS IO and STATISTICS TIME insteadrun 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 OFFand 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 OFFCode Snippets
SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFFSET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFFContext
StackExchange Database Administrators Q#11689, answer score: 7
Revisions (0)
No revisions yet.