What is the ideal indexing strategy for SQL Server?

Question

A friend and I are working on a new project that is using SQL Server. In the SQL that I've done past projects, I've always put indexes on any field that is used in a JOIN or a WHERE.

My friend has added them only when there has been a performance need for them. The idea being that there is a cost to maintaining the indexes, and you want to be sure that it is worth paying that cost. It is fair to say that some of the queries are not going to be used often, and also that some tables will be added to more actively than others.

I'm therefore looking for advice on what is the "best practice" for database indexes. What works well for you?

Answer 1

I would try to follow these guidelines:

• always have a good primary/clustering key - typically an INT IDENTITY - avoid GUID or large compound PK/CK. A well and thoughtfully chosen PK/CK will go a long way to help with performance overall. To thoroughly understand why, read all of Kimberly Tripp's blog posts on clustering key choices.

• always index all foreign key columns - separately or together with other columns that make sense; this helps with JOIN performance

• other than that: less is more! Only add indices if you absolutely must - watch your system, profile your data load, see what the performance is, fine-tune, measure again. If an index help - keep it; if an index doesn't get used - toss it

• use the DMV's at hand (the missing index DMV, and the unused indices DMV) to get an idea for what indices might help, and which ones aren't being used at all...

Answer 2

My personal preference goes to the pro-active approach: based on your queries, add indexes where needed. As you say, on fields that are involved in JOINs or WHEREs. Every index speeds up read-queries, but slows down writes (as every write needs to update the index). So for write-intensive table, other solutions (data-warehousing, replication...) might be needed.

The other approach, adding only indexes where performance requires them, is only valid if you do active monitoring, but even then has a few drawbacks:

• You will have to add an index to a table which is suffering performance issues. While the index is being added, your table is locked - and it's a heavily used table!
• Often when testing, test-data is several orders of magnitude smaller than the real data in the application. Bottlenecks risk being overlooked.

Answer 3

You want to put them only on those columns or column groups that have a lot of queries against them. You can get a lot of statistics from SQL Server to see what queries are being run against your tables, and SQL Server will even suggest indexes where you don't have them.

Here's a good link with some useful information and other links to good info. SQL Server Index Checklist and tips