May 23, 2012 at 2:09 am
Hi,
I have created one index in address table in adventureworks database just for testing.
CREATE NONCLUSTERED INDEX [IX_Address_PostalCode] ON [AdventureWorks].[Person].[Address] ([PostalCode]
Now I ran the below query
select * from person.address WITH (INDEX ([IX_Address_PostalCode])) where PostalCode='97205'
When I see the actual execution plan, I get the below things.
Select(Cost: 0%) - Nested loops(Inner join Cost: 0%)- Index seek(Non Clustered) [Address].[IX_Address_PostalCode] Cost: 1%- Key lookup(Clustered) [Address].[PK_Address_AddressID] Cost 99%.
When I have used the index in the hint of the query why It uses the index [PK_Address_AddressID] ?
What actually means when it shows the cost in percentage after the index?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 23, 2012 at 2:16 am
You probably already know this but, as you have done select * in the query, it needs to return all the other columns along with the postal code. To do this, the query looks through the new index for the postal code then finds the corresponding pointer to the primary key to get the other columns from the address table
If the command was just SELECT postalcode FROM person.address then it will only use the postal code index which would change to a 100% weighting against that index.
So I would read that plan as 1% of execution time took on finding the postal code in the postal code index, and 99% of the execution time looking up the other columns needed to satisfy the query.
May 23, 2012 at 2:39 am
This is what I was looking for. Thanks a lot, Anthony.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 23, 2012 at 2:42 am
In a real world scenario. Just think of it like you're flicking through an index of a book for a postcode, if you want more than just the postcode you need to flick through to the page that you want and that's going to cost you time.
May 23, 2012 at 5:11 am
Also remember, those costs are estimates. They're the only numbers you have, so you must use them, but they're estimates, not actual measures.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply