June 30, 2015 at 8:18 am
HI Gurus,
I ran a simple query against AdventureWorks database:
select * from Sales.SalesOrderDetail
where SalesOrderDetailID = 6
When I looked execution plan it gave me missing non clustered index suggestion on 'SalesOrderDetailID' even though it has PK!!!!
So I created non-clustered index on the column and ran again. Now I get on execution plan with 'key lookup', 'nested loop' operator, which I think are bad.
anyway, my question is how to make 'best' execution plan of this query?
thanks
June 30, 2015 at 8:42 am
Tac11 (6/30/2015)
HI Gurus,I ran a simple query against AdventureWorks database:
select * from Sales.SalesOrderDetail
where SalesOrderDetailID = 6
When I looked execution plan it gave me missing non clustered index suggestion on 'SalesOrderDetailID' even though it has PK!!!!
So I created non-clustered index on the column and ran again. Now I get on execution plan with 'key lookup', 'nested loop' operator, which I think are bad.
anyway, my question is how to make 'best' execution plan of this query?
thanks
Well... You just discovered one of the evils of taking the optimizes word on suggested indexes. It's usually wrong...
Here is the index that will get you the results you were hoping for.
CREATE NONCLUSTERED INDEX ix_SalesOrderDetail_SalesOrderDetailID ON Sales.SalesOrderDetail (
SalesOrderDetailID)
INCLUDE (
SalesOrderID,
CarrierTrackingNumber,
OrderQty,
ProductID,
SpecialOfferID,
UnitPrice,
UnitPriceDiscount,
LineTotal,
rowguid,
ModifiedDate)
Note that the optimizer didn't suggest any included columns... The lack of included columns kept the index from being a "covering index" which is what caused the key lookup (and yes, you are correct... They are bad).
Also, just because a table has a primary key (which does in fact result in an index), doesn't mean that PK's index is appropriate for a given query.
HTH,
Jason
June 30, 2015 at 9:03 am
Great!!! Hat's off to you.
June 30, 2015 at 9:20 am
Tac11 (6/30/2015)
Great!!! Hat's off to you.
Glad to help. 🙂
June 30, 2015 at 9:36 am
Tac11 (6/30/2015)
HI Gurus,
select * from Sales.SalesOrderDetail where SalesOrderDetailID = 6When I looked execution plan it gave me missing non clustered index suggestion on 'SalesOrderDetailID' even though it has PK!!!!
The first column of the PK is not SalesOrderDetailID. It is SalesOrderID.
So it is like having the phonebook and looking for firstname = john, but it is sorted on surname first, then firstname.
How would you get all the firstname johns?
Scan phonebook from page 1 to 100000.
So, if you are looking for firstname = john, the engine will give you the suggestion to add an index on firstname / SalesOrderDetailID , which you may not have.
If the lookup cost is disproportionate, due to say an existing index bringing back too many rows resulting in a nested loop lookup on too many rows, then it may be better to cover the index.
If you are only doing a lookup for on average 1 row, and doing one lookup for a query, then it may not be worth creating a covering index.
June 30, 2015 at 11:05 am
Tac11 (6/30/2015)
Now I get on execution plan with 'key lookup', 'nested loop' operator, which I think are bad.
No they're not. There are no bad operators, if there were they wouldn't be in the product. What there are are inappropriate operators for the number of rows the query affects. Since the query in question is going to return a single row, a key lookup is fine.
The index Jason suggests essentially duplicates the table, it has all columns in either key or include. Since for the query in question, the key lookup is fine, there's little sense in duplicating one of the larger tables in the database, just so that a query can execute in maybe 3 less reads. It would be a waste of space and time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2015 at 11:07 am
Jason A. Long (6/30/2015)
(and yes, you are correct... They are bad).
They are not. They are inefficient on large row counts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2015 at 11:19 am
GilaMonster (6/30/2015)
Tac11 (6/30/2015)
Now I get on execution plan with 'key lookup', 'nested loop' operator, which I think are bad.No they're not. There are no bad operators, if there were they wouldn't be in the product. What there are are inappropriate operators for the number of rows the query affects. Since the query in question is going to return a single row, a key lookup is fine.
The index Jason suggests essentially duplicates the table, it has all columns in either key or include. Since for the query in question, the key lookup is fine, there's little sense in duplicating one of the larger tables in the database, just so that a query can execute in maybe 3 less reads. It would be a waste of space and time.
GilaMonster (6/30/2015)
Jason A. Long (6/30/2015)
(and yes, you are correct... They are bad).They are not. They are inefficient on large row counts.
Both excellent points Gail. Thank you for the correction. 🙂
June 30, 2015 at 11:27 am
GilaMonster (6/30/2015)
The index Jason suggests essentially duplicates the table, it has all columns in either key or include. Since for the query in question, the key lookup is fine, there's little sense in duplicating one of the larger tables in the database, just so that a query can execute in maybe 3 less reads. It would be a waste of space and time.
+1000 to this. It seems like a lot of folks don't understand that a non-clustered index is a duplication of data that not only affects queries and space on disk but also affects backups, time to restore, space on tape, and time for nightly index and stats maintenance not to mention the ridiculous affect that the quantity of indexes has had on all inserts and many updates. We're still cleaning up ridiculously wide indexes both at the B-Tree and the Leaf Level (Includes) left over by the previous regime that were created to remove row lookups for single row queries. It's just stupid how many there are in our system. Many of the indexes were created through the use of DTA and a whole lot of them provided little or no improvement compared to the improvements that are being made to the code itself. Of course, there are many near-duplicate indexes that we're also continuing to consolidate.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2015 at 11:32 am
Jeff Moden (6/30/2015)
It seems like a lot of folks don't understand that a non-clustered index is a duplication of data that not only affects queries and space on disk but also affects backups, time to restore, space on tape, and time for nightly index and stats maintenance
And the buffer pool. The pages of the index and the table are different pages, so worse case you can have double the table's size of memory used up by one table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2015 at 12:41 pm
Also, as much as I'm guilty of saying this, it's not about improving the plan. It's about improving the performance of the query. The plan just exposes how the optimizer is resolving your query with the objects in your database. As Gail said, nothing is bad, it just is.
"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
June 30, 2015 at 1:18 pm
GilaMonster (6/30/2015)
Jeff Moden (6/30/2015)
It seems like a lot of folks don't understand that a non-clustered index is a duplication of data that not only affects queries and space on disk but also affects backups, time to restore, space on tape, and time for nightly index and stats maintenanceAnd the buffer pool. The pages of the index and the table are different pages, so worse case you can have double the table's size of memory used up by one table.
Heh... totally forgot about that. That's one of the worst effects especially on smaller systems with less RAM. Thanks, Gail.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2015 at 1:28 pm
@MadAdmin, when I query:
select object_name(id) as tablename, name, indid from sysindexes
where object_name(id) = 'SalesOrderDetail'
It give me:
SalesOrderDetail---------ix_SalesOrderIdDetailId ------1 (clustered index)
I didn't understand why execution plan suggesting me to create non-clustered index on this while the table already has a clustered index!!!
June 30, 2015 at 1:47 pm
Tac11 (6/30/2015)
@MadAdmin, when I query:select object_name(id) as tablename, name, indid from sysindexes
where object_name(id) = 'SalesOrderDetail'
It give me:
SalesOrderDetail---------ix_SalesOrderIdDetailId ------1 (clustered index)
I didn't understand why execution plan suggesting me to create non-clustered index on this while the table already has a clustered index!!!
First off, you can't just look at the name of the index to know what's actually in the index definition. You need to look at sys.index_columns to see what columns are part of the key (key_ordinal will tell you the order) and what columns are included (is_included_column = 1).
Not sure which version on AdventureWorks you're using but I just check the two version I have on my machine (2008 & 2012). Both have 3 indexes on that table...
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID(CLUSTERED)
AK_SalesOrderDetail_rowguid(NONCLUSTERED)
IX_SalesOrderDetail_ProductID(NONCLUSTERED)
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID has SalesOrderID & SalesOrderDetailID as the key columns (in that order). Which means that the index is sorted by SalesOrderID first then by SalesOrderDetailID.
HTH,
Jason
June 30, 2015 at 2:28 pm
I definitely misspoke when I said key lookups are bad as a blanket statement. So, please accept my apology for that.
That said, they are one of the operators that I look out for when I'm trying to optimize a query and they do prompt me to look at existing indexes that I could possibly add a an included column to, in order to make the index cover the query... (or if it's option, pull the offending column(s) from the select list)
Then again, if I'm writing a query that only returns a small number of rows, executes in a fraction of a second and won't be executed a gazillion times a day, I'm probably not going to delicate a huge portion of my day trying to make it faster and I certainly wouldn't create a new index for it.
The answer I supplied was simply an attempt to explain why the op was seeing what they were seeing and to show a different index that would give them what they were originally expecting to see.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply