July 29, 2008 at 7:47 am
For simplistic purposes, if I have a table with multiple columns, and have the clustered index on columns A and B, but also have queries hitting columns B, C, and D, how would I know which query would be hit if I added a nonclustered index that included columns B,C, and D?
The reason I ask is that I know that the hitting the clustered index using A and B would give me an Index Seek, just as hitting the nonclustered for B,C, and D. But if for whatever reason the clustered is hit using B, then it becomes an Index Scan, which I know isn't terribly bad, but just thought I would do what was best. So I thought just by having B as my first column in the nonclustered would prompt the optimizer to decide on using that.
I also know I can specify which index to use in the queries, but I don't want to go back and do this for a few hundred procs at this point (I've inherrited this mess).
Any insight?
July 29, 2008 at 9:14 am
It really depends, on the query, on the selectivity of the column that defines the leading edge of the index... Generally, if the criteria that defines the index scan in the execution plan says it's using a column first, then you should be able to substitute that by creating an index on that column, as you said. But if that column is not that selective, the optimizer can still go back to the cluster to scan for the data. Maybe the cluster would work better with the columns in a different order?
Without hard data, this is just an academic discussion.
----------------------------------------------------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
July 29, 2008 at 10:30 am
Well, I've played around some bit and found that the optimizer really prefers to go to the Clustered Index in just about every case unless every column is mentioned in the NonClustered index, even if only one column in my query is found in my Clustered, and several might by in my non-clustered. So unless I specify the index I want with (INDEX( )) I can't trust that it will ever use anything but the Clustered.
Cool, just have to be very specific when I start a new project next time.
July 29, 2008 at 1:41 pm
Not exactly.
SQL will use the index that is most optimal for the particular query. If none of the NC indexes are useful, it will use the cluster. That's probably not what you want though, as it will probably scan the cluster. Clustered index scan = table scan.
The more selective the nonclustered indexes, the more likely SQL is to use them. If a noncluster returns a large percentage of the table and does not contain all the columns necessary, then SQL has to look up those columns in the clustered index. Bookmark lookups (also called key lookups) are very expensive operations. If bookmark lookups are needed for more that about 1% of the table, SQL will probably scan the cluster as the total number of IOs are lower than doing lookups for each row.
You should not forcethe index to use unless you are really sure that you know better than the optimiser. I've seen one case in over five years of an index hint used appropriately (an edge case where SQL was choosing to scan the cluster when the NC seek plus lookup was faster), and many many cases where removing the hint resulted in a large improvement in speed.
If you could post table definition, index definitions and the queries we can discuss this more specifically.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply