August 16, 2016 at 9:11 am
Hi Guys,
I have a quick question about non clustered indexes. If I have an index across 4 columns and I have a query that only filters using one of the four columns in the index, will SQL still use the index or not?
August 16, 2016 at 9:16 am
To be sargable, the query would need to filter on the leading column in the index.
For example, assume we have CLUSTERED INDEX ( StoreID, TransactionID, ProductID ). A query WHERE ProductID = 44563 would not leverage the index for partial scanning. Assuming no other indexes on ProductID, it would be a full table scan.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 16, 2016 at 9:18 am
The same goes for non-clustered indexes; in terms of query predicates, column position in an index matters.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 16, 2016 at 9:22 am
crazy_new (8/16/2016)
Hi Guys,I have a quick question about non clustered indexes. If I have an index across 4 columns and I have a query that only filters using one of the four columns in the index, will SQL still use the index or not?
The index CAN still be used, but not for a seek, if the column is anything but the first in order of index creation. The query plan would need to scan all rows to get to 2nd-4th columns.
I also note that the (nonclustered) index could be skipped altogether and the entire base table scanned if the optimizer estimates more than about 1% or so of the total rows on the table will be hit and you need at least one column from the table that is not part of the index. This is due to the optimizer math being so "bad" for random IO that you get from index-seek-row-lookup plans.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 16, 2016 at 9:49 am
Hi Guys thanks for the replies it was very helpful. Just one more thing, If say I have a clustered index on ProductID, and ProductID is the first column in my nonclustered index, would it make any difference if I just remove it from the nonclustered index?
August 16, 2016 at 10:29 am
crazy_new (8/16/2016)
Hi Guys thanks for the replies it was very helpful. Just one more thing, If say I have a clustered index on ProductID, and ProductID is the first column in my nonclustered index, would it make any difference if I just remove it from the nonclustered index?
Yeah, that'll make the nonclustered index more likely to be used.
The first column is used in the histogram in the statistics. That's one of the single driving factors for how useful an index is to the optimizer (not the only factor, but a huge one). Your nonclustered index having the same leading edge as the clustered index makes it that much less likely to be useful since the clustered index has the same histogram (potentially; identical, likely; very similar) and the clustered index has all the data.
Just be sure that whatever column you pick for the leading edge, the first column, of the index is fairly selective and reasonably well distributed. It doesn't have to be unique or even the most selective column, just selective enough with a good data distribution. This will make for a better histogram with more accurate row counts which means better execution plans which means better performance.
"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
August 17, 2016 at 2:24 am
Thanks for the feedback on this, it is very helpful. I am actually assessing Indexes on a production environment and the one tables that is always involved in deadlocks has a ID as the clustered index and the first column in the non clustered index is the same ID. That's why I wanted to know if the removing it from the non clustered index would make a difference. So what you are saying is that it is always using the clustered index to filter on if the query only filters in this ID?
August 17, 2016 at 3:01 am
crazy_new (8/17/2016)
So what you are saying is that it is always using the clustered index to filter on if the query only filters in this ID?
No. It probably will be using the nonclustered index sometimes, but the nonclustered index is redundant, it's not necessary as any query using it can use the clustered index instead.
I wouldn't suggest just removing the ID column. Rather do a full index analysis exercise on that table and see what indexes are needed for the queries that run against that table. Trying stuff at random doesn't usually produce good results.
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
August 17, 2016 at 3:53 am
Hi Gila thanks for the advise..Yes will would have removed it in a test environment first not on production. Do you know of any sites where I can learn about all these things that SQL does and uses in the background for everything that happens in the front?
August 17, 2016 at 5:38 am
There is a use case for having an ordinary index with the same leading edge column as the clustered index. If your table is very wide (meaning less rows per page), a narrow ordinary index can be significantly more performant. Always test - if your narrow index doesn't cover, you've blown it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 17, 2016 at 6:00 am
ChrisM@Work (8/17/2016)
There is a use case for having an ordinary index with the same leading edge column as the clustered index. If your table is very wide (meaning less rows per page), a narrow ordinary index can be significantly more performant. Always test - if your narrow index doesn't cover, you've blown it.
Actually, it can be more than slightly beneficial. Creating an NCI on the CI key column will be a serious benefit to a range scan. Instead of reading the entire CI, it can do a seek against the NCI and the reads plummet. Like you said though - if it isn't covering, you're going to take a hit no matter which choice the optimizer makes.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply