While trying to improve performance of some of my SQL server queries, I ran across an excellent article by Craig Freedman that explains how SQL server creates seek predicates when using an index seek to find rows in a table.
The key point I took away from this (and that I didn't know before) is that a multi-column index won't utilize the second column unless the first is searched as an equality. In my current project, we have two dates to represent when a row was added to the table and when it is removed. This is a great system for keeping unlimited change tracking and history data while keeping everything in one table, but it makes searches slow because only the first date is used as an index seek predicate. Most of our queries are something like this:
select FAID from ESP_FAs where Admit <= '3/28/2011 12:00:00' AND Retire > '3/28/2011 12:00:00'
This will get all the rows that exist on the specified date and haven't been deleted. The problem is that based on Craig's article above, only the first date is used in the seek predicate if Admit and Retire are both columns in an index. The retire clause becomes a residual predicate, so having it in the multi-column index isn't really helping us much.
So far the best solution I've found for this is to make sure that the first column included in a multi-column index that is not being compared to with equality is the most limiting. This reduces the number of rows SQL server has to sort through with the residual predicate.
No comments:
Post a Comment