January 7, 2014 at 4:14 am
Hi Folks,
While trying to optimize a purge process for a monster table of 200 GB, I was simply running a simple query and find myself very much confused to what I am noticing. I need some help to understand this behavior.
I am running the following query:
select top 10000 accountJournalId
from GAII_Build..AccountJournal AJ(nolock)
where journalDate>'2013-12-01' and journalDate<'2014-01-01'
There is a non-clustered index on this table on journalDate and no other columns in the include list. While running the query, I found that the execution plan shows index seek on the above mentioned column. I am fine with that but what bothers me is from where the engine is reading the column 'accountjournalId' as it is not a part of this index which is being used and what I was hoping to see is this column being read from clustered index as 'Key Lookup' or from somewhere else.
Please let me know where I am going wrong in interpreting this. Let me know if some more data is required.
Thanks
Chandan Jha
January 7, 2014 at 4:34 am
A non clustered index consists of the index key column(s) plus the clustered index column(s). That's why your query was covered by the non clustered index.
John
January 7, 2014 at 4:39 am
John Mitchell-245523 (1/7/2014)
A non clustered index consists of the index key column(s) plus the clustered index column(s). That's why your query was covered by the non clustered index.John
Thanks. But the index which is being used is having only 1 column and if the other column is being retrieved internally from clustered index, shouldn't the execution plan show some sort of key lookup.
Also if I add one more column in the select list, the execution plan changes to clustered index scan. So if the non-clustered index is having clustered index columns as well, why is the clustered index scan now and then why at all we need covering indexes if a non clustered index with just 1 column contains clustered index columns too?
Sorry for some silly questions, just trying to bridge practical and theoretical knowledge.
Regards
chandan
January 7, 2014 at 4:46 am
Thanks. But the index which is being used is having only 1 column and if the other column is being retrieved internally from clustered index, shouldn't the execution plan show some sort of key lookup.
No, because the column you require is already in the index. A lookup is only needed if the column you need has to be retrieved from a different indexc.
Also if I add one more column in the select list, the execution plan changes to clustered index scan. So if the non-clustered index is having clustered index columns as well, why is the clustered index scan now and then why at all we need covering indexes if a non clustered index with just 1 column contains clustered index columns too?
Sorry, I should have made that a bit clearer. The clustered index key columns are included in the non clustered index. If you introduce a column to your query that isn't in the non clustered or clustered index key, the query is no longer covered by the non clustered index and so the query optimizer has to find another way of getting the data - in this case a clustered index scan.
John
January 7, 2014 at 4:46 am
chandan_jha18 (1/7/2014)
Thanks. But the index which is being used is having only 1 column and if the other column is being retrieved internally from clustered index, shouldn't the execution plan show some sort of key lookup.
It's not doing a key lookup because it's not fetching columns from the clustered index.
As John said, the clustered index key is present in all nonclustered indexes. It's there as a column, just not one visible to you.
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
January 7, 2014 at 5:33 am
Thanks John and Gail. Now I understood it more clearly after relating to the practical lab results and theory. The clustered index column is present in all non clustered indexes. In this case, I am retrieving the column which is clustered index column so the non-clustered index is sufficient to provide with that. But when I need more number of columns either the clustered index scan will be performed(depending upon the optimser decision based on cost) or it will be retrieved from a covering index(if present) or a lookup will be done. Correct?
Thanks
Chandan
January 7, 2014 at 5:36 am
Just an additional question on top of it. Lets say that in this case I wanted one more column which is not a part of this index. So in that case will there be an index seek on this index plus the look-up from clustered index or simply a clustered index scan. I think both of them are possible and it depends upon which one costs less when the optimizer decides the plan.
Thanks
Chandan
January 7, 2014 at 5:41 am
Chandan
Yes, for simple queries such as the one you posted, that's a good summary. Of course, there are reasons why the query optimizer may not pick the best plan - parameter sniffing, lack of index maintenance, out-of-date statistics, parallelism not configured correctly, and so on.
John
January 7, 2014 at 5:57 am
chandan_jha18 (1/7/2014)
Thanks John and Gail. Now I understood it more clearly after relating to the practical lab results and theory. The clustered index column is present in all non clustered indexes. In this case, I am retrieving the column which is clustered index column so the non-clustered index is sufficient to provide with that. But when I need more number of columns either the clustered index scan will be performed(depending upon the optimser decision based on cost) or it will be retrieved from a covering index(if present) or a lookup will be done. Correct?Thanks
Chandan
Not quite. If more columns are needed that are not a part of the nonclustered index, that won't affect whether or not that index is scanned or not. If columns that are not included as part of the index are needed, it will have to go where those columns are available. This usually means either the clustered index or the heap table through the lookup operation.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 7, 2014 at 6:03 am
Grant Fritchey (1/7/2014)
chandan_jha18 (1/7/2014)
Thanks John and Gail. Now I understood it more clearly after relating to the practical lab results and theory. The clustered index column is present in all non clustered indexes. In this case, I am retrieving the column which is clustered index column so the non-clustered index is sufficient to provide with that. But when I need more number of columns either the clustered index scan will be performed(depending upon the optimser decision based on cost) or it will be retrieved from a covering index(if present) or a lookup will be done. Correct?Thanks
Chandan
Not quite. If more columns are needed that are not a part of the nonclustered index, that won't affect whether or not that index is scanned or not. If columns that are not included as part of the index are needed, it will have to go where those columns are available. This usually means either the clustered index or the heap table through the lookup operation.
Grant, So do you mean that if the columns which are not part of the NC index, will be fetched through lookup and not the simple and expensive clustered index scan?
Thanks
Chandan
January 7, 2014 at 6:59 am
chandan_jha18 (1/7/2014)
Grant Fritchey (1/7/2014)
chandan_jha18 (1/7/2014)
Thanks John and Gail. Now I understood it more clearly after relating to the practical lab results and theory. The clustered index column is present in all non clustered indexes. In this case, I am retrieving the column which is clustered index column so the non-clustered index is sufficient to provide with that. But when I need more number of columns either the clustered index scan will be performed(depending upon the optimser decision based on cost) or it will be retrieved from a covering index(if present) or a lookup will be done. Correct?Thanks
Chandan
Not quite. If more columns are needed that are not a part of the nonclustered index, that won't affect whether or not that index is scanned or not. If columns that are not included as part of the index are needed, it will have to go where those columns are available. This usually means either the clustered index or the heap table through the lookup operation.
Grant, So do you mean that if the columns which are not part of the NC index, will be fetched through lookup and not the simple and expensive clustered index scan?
Thanks
Chandan
A lookup operation can be a scan or a seek. And a lookup is generally considered to be expensive, seek or scan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply