Sql-server – Index design question

database-designindex-tuningsql serversql-server-2008

This query runs for 60 seconds:

SELECT mychecklis0, ....
FROM dbo.MyChecklistStatus mychecklis0_ 
WHERE mychecklis0_.AppFormSubSectionId=@P0 AND mychecklis0_.ProfileId=@P1

There is an index created on profileid and AppFormSubSectionId

My question:

Does the arrangement of columns in the where statement comparing the column id in the sys.index_columns table matter?

enter image description here

Which is better, creating an index for each col, or 1 index for multiple columns?

Best Answer

It can't use two separate indexes to satisfy that WHERE clause - it will have to pick one or the other. If you want to enable seeks you will need to make a single index with both columns, and order them from most selective to least selective. Think about designing indexes in such a way that SQL Server can eliminate as many rows as possible, as quickly as possible, rather than the traditional way most people think about it (finding as few rows as possible).

You should also consider adding any other columns from the SELECT list to the INCLUDE list of the index (making it "covering"), this way you can avoid key lookups. So, perhaps, drop the two indexes you have and create (guessing at selectivity):

CREATE INDEX IX_BetterIndex 
  ON dbo.MyChecklistStatus -- My? Really? Why?
    (ProfileId, AppFormSubSectionId)
  INCLUDE(mychecklist0, ...);

(Note that you don't have to include the clustered index key column(s), since they exist in all non-clustered index pages anyway.)

This is a general recommendation for something to try. It could very well be that selectivity is high enough that the number of key lookups does not justify using space in the index pages for the covering columns. It could also be that this query isn't run often enough to optimize the indexes on the table to help this query in a vacuum - there may be other queries that are run more frequently that would benefit from a different index. And it may be that your SELECT list has columns in it that aren't really necessary, which can change the formula further. There is no one right answer.

And no, the order of columns in the index has no relevance whatsoever to the ColumnId order in sys.columns.

Related Question