October 18, 2011 at 3:13 am
Hello Expert,
I noticed something odd in the query excution plan, so thought of checking with you guys.
Here is the story
I copied the SalesOrderDetail from the AdventureWorks database to my database called playGround and the created the Indexes. I am using SQL Server 2008 Express Edition.
Please find the below steps.
SELECT * INTO SalesOrderDetail FROM AdventureWorks.Sales.SalesOrderDetail
GO
ALTER TABLE SalesOrderDetail
ADD CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
GO
CREATE INDEX IX_SalesOrderDetail_ProductID on SalesOrderDetail(ProductID)
GO
CREATE INDEX AK_SalesOrderDetail_rowguid on SalesOrderDetail(rowguid)
I ran the below query,
SELECT productId, SUM(orderqty) as qty, SUM(unitprice) as price from SalesOrderDetail
WHERE SalesOrderID=51721 and SalesOrderDetailId = 39791
group by productId
I got the execution plan as I expected
|--Clustered Index Seek(OBJECT:([playGround].[dbo].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), SEEK:([playGround].[dbo]. [SalesOrderDetail].[SalesOrderID]=(51721) AND [playGround].[dbo].[SalesOrderDetail].[SalesOrderDetailID]=(39791)) ORDERED FORWARD)
Then I ran the below query.
SELECT productId, SUM(orderqty) as qty, SUM(unitprice) as price from SalesOrderDetail
WHERE SalesOrderDetailId = 39791
group by productId
This time I got the execution plan like below. This is something odd.
|--Index Scan(OBJECT:([playGround].[dbo].[SalesOrderDetail].[IX_SalesOrderDetail_ProductID]), WHERE:([playGround].[dbo].[SalesOrderDetail].[SalesOrderDetailID]=(39791)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([playGround].[dbo].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]), SEEK:([playGround].[dbo].[SalesOrderDetail].[SalesOrderID]=[playGround].[dbo].[SalesOrderDetail].[SalesOrderID] AND [playGround].[dbo].[SalesOrderDetail].[SalesOrderDetailID]=[playGround].[dbo].[SalesOrderDetail].[SalesOrderDetailID]) LOOKUP ORDERED FORWARD)
If you notice the second query’s execution plan, it does the scan in the non clustered index then uses the clustered index key to pull the records from the table.
Questions :
1.My confusion is that, how SQL Server chosen the non clustered index (IX_SalesOrderDetail_ProductID)
2.And moreover if you see the non clustered index schema,I just used the “ProductId” as the index column. But in my second query I never mantioned the ProductId in the where clause. Considering this, how / why SQL Server chosen this non clustered index to retrive the key. according to me this non clustered index will not have any information about the SalesOrderDetailId column. Correct me if I am wrong.
3.Is there anything I am missting to investigate
Thanks for your help.
October 18, 2011 at 4:15 am
There are no indexes satisfying your second query.
So the SMART query optimizer uses the productid index as long as which seems to be a better possible index key and uses the clustered index for other columns(Look up).
October 18, 2011 at 4:25 am
dfine (10/18/2011)
Questions :1.My confusion is that, how SQL Server chosen the non clustered index (IX_SalesOrderDetail_ProductID)
2.And moreover if you see the non clustered index schema,I just used the “ProductId” as the index column. But in my second query I never mantioned the ProductId in the where clause. Considering this, how / why SQL Server chosen this non clustered index to retrive the key. according to me this non clustered index will not have any information about the SalesOrderDetailId column. Correct me if I am wrong.
3.Is there anything I am missting to investigate
Thanks for your help.
Indexes do not just come from the where clause. The columns returned in your query also contribute to your indexes. This is most likely why the optimizer chose the index scan (leading edge column is same in your query as index) and then the key lookup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 18, 2011 at 5:53 am
Yes, I know that column included in the select query also consider by Query Optimizer.
however, if you see the second query's excution plan it uses the non clustered index to get the key of clustered index and then used the key to pull the records.
My thought:
A. The Query Optimizer could have used only the clustered index to satisfy my query. whilst I am not sure why it picks the non-clustered index. (this is my question)
B. This non-clustered index contains only two columns 1. ProductId 2. Bookmark (Clustered Index key)
since non-clustered doesn't contains any information about "SalesOrderDetailId" column how optimizer find this plan?
Just trying to understand the internal of optimizer
Thanks
October 18, 2011 at 6:48 am
dfine (10/18/2011)
Yes, I know that column included in the select query also consider by Query Optimizer.however, if you see the second query's excution plan it uses the non clustered index to get the key of clustered index and then used the key to pull the records.
My thought:
A. The Query Optimizer could have used only the clustered index to satisfy my query. whilst I am not sure why it picks the non-clustered index. (this is my question)
B. This non-clustered index contains only two columns 1. ProductId 2. Bookmark (Clustered Index key)
since non-clustered doesn't contains any information about "SalesOrderDetailId" column how optimizer find this plan?
Just trying to understand the internal of optimizer
Thanks
There are no indexes satisfying your second query.Your clustered in on salesOrderid and then DetailsID. The order of key has a great impact.Hence its not using the clustered index.
As long as its not able to find the detailid, it uses the lookup using the clustered seek.
October 18, 2011 at 7:37 am
dfine (10/18/2011)
A. The Query Optimizer could have used only the clustered index to satisfy my query. whilst I am not sure why it picks the non-clustered index. (this is my question)
Because the optimiser calculated that the nonclustered index is more efficient, being a much smaller index. Clustered index scan is it's 'last resort', what it will fall back on when there's no other option that's more efficient.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply