September 15, 2009 at 5:27 pm
I once read on a DBA blog that the SQL Server Optimizer would only use one index for a search. If an additional index was required for a search or join operation, the Optimizer would not use it. Instead, it would perform a full table or index scan to obtain the final results.
Does anyone know, is this fact or fiction?
LC
September 15, 2009 at 6:22 pm
I believe it can. There is a merge operation that can take results and put them together.
http://www.sql-server-performance.com/tips/composite_indexes_p1.aspx
September 16, 2009 at 6:54 am
There are two ways it can do this, index intersection and index joins. It's not common for the optimizer to make these choices though. If you've got two indexes on a table, each on one column, it's more likely to use one of them to satisfy a given query, so it makes sense to combine the two columns into a compound key in a new index in most circumstances. That said, it's always best to try to keep your indexes as narrow as possible. It's a real balancing act.
"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
September 16, 2009 at 7:15 am
Thanks for your responses. I should have been more specific in my first post.
I've got a situation where I have a concatenated index consisting of 3 columns in a non-clustered index. A programmer has written a query that contains a WHERE clause containing all 3 of the currently indexed columns and a 4th column that is currently the only column in another non-clustered index.
I am wondering if it is necessary to add the 4th column to the existing 3 column index to achieve good seek and/or scan performance or if the Optimizer will use the 3 column index and perform a JOIN on the single column index to produce the rows needed to satisfy the query.
The latter scenario would be acceptable from a performance standpoint. A full table scan would not be.
The focus of all this activity is a VERY large table and response times are an issue.
So, to restate my question:
Do you know if the Optimizer will use the single column index to JOIN on the results obtained from using the 3 column index, or if it will use the 3 column index and then perform a table (or index) scan to satisfy the query?
LC
September 16, 2009 at 7:28 am
There's no way to know for sure which it will do, but I'm fairly certain that the likely path is for it to simply use the compound index and then scan within it. The only way to know for sure is to generate an execution plan and see what's happening.
"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
September 16, 2009 at 8:18 am
Thanks for your responses.
Sincerely,
LC
September 17, 2009 at 9:47 am
1) I believe you can actually force 2 indexes with the INDEX ( index_val [ ,...n ] ) table hint. Then you can see what the query plan is like and if you like it. Be careful doing this as you can make things MUCH worse!!
2) without knowing more details I can still say unequivocally that INCLUDING this 4th column to the 3 column index will make the query MUCH faster because then you would have a covering index.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply