Thoughts on index creation for SQL Server for missing indexes

Question

I'm working on performance tuning my SQL Server 2008 database and am using the output from various DMVs to identify missing indexes, indexes that aren't being used, etc.

I am mainly using these 3 scripts (from SQLServerCentral.com) that rely on DMV data that SQL Server provides:

The Ultimate Missing Index Finder

The Ultimate Duplicate Index Finder

The Ultimate Index Usage Reporter

When trying to create new indexes, I know that I need to be smart and try to group many of the suggestions into as few indexes as possible by creating them just right so that they can do a decent job of serving a few different queries vs. creating a separate index for each query when the only difference may be the included columns, etc.

So I wanted to get the community's thoughts and see if people can answer a few questions about reading the DMV results and structuring the new indexes.

Does the order of the included columns in an index matter?

Take the following two index suggestions. What would you do to try to make 1 to fit both?

object_name equality_columns                    inequality_columns              included_columns
    Appointment [FranchiseId], [AppointmentTypeId]  [CustomerId], [ApptDateTime]    NULL
    Appointment [FranchiseId], [AppointmentTypeId]  [ApptDateTime]                  [CustomerId]

If I have lots of index suggestions with the same equality and inequality fields but different included fields, is it better to include more fields or go with less included fields? Again, the goal is to create 1 index vs. 3 (if 3 have different included columns).

Lastly, if there are any good resources out there that can offer up good rules of thumb or general guidelines for things like this I would appreciate it if those links could be provided.

Answer 1

Order matters for indexed columns, but not included columns. This is because only key columns are used for lookups.

You have to "read between the lines" of most of the documentation, but that's what they're implying in this BOL article

This discussion is a bit more explicit in saying order isn't important.

Pretty much the only concern with included columns in indexes is space--the indexes can get quite large if you include lots of columns, or very large columns. BUT this means you definitely don't want to do two separate indexes, each with the same key columns but different included ones. Then you're just compounding your space problem.