Tuning indexes in SQL Server is the one thing a database administrator (DBA) can completely own in terms of database...
application performance. You can’t necessarily control the queries that an application is firing off to SQL Server, but you can make sure the right indexes are in place to maximize those queries’ performance.
There are, sadly, no hard and fast rules for what indexes an application will need: It’s all based on workload. An application that writes a lot of data but which reads very little can get by with relatively few indexes; applications that read a lot but write little can benefit greatly from more indexes. Most applications fall somewhere in-between, making index tuning more art than science.
More on index tuning
Adjusting the fill factor when tuning your indexes
Tuning and two other ways to improve performance
Your first step should be to use SQL Profiler to capture some representative production query traffic. Profiler comes with a built-in template for query tuning that will grab all the right data. Stuff that data into SQL Server’s Tuning Wizard and wait for the results. There are really three things at play:
First, when reading data, SQL Server benefits from indexes that let it find the desired data and sort it into the desired order quickly. Covering indexes are the best, as they contain all of the data SQL Server needs. For example, if a query needs customer ID numbers and last names, and there’s a compound index containing those two columns, then the index itself can satisfy the query, and there will be no subsequent look-up to the underlying database.
Second, when writing data, indexes hurt performance, as SQL Server has to take the time to update any indexes affected by the change. So index tuning becomes a matter of trading off between read and write performance.
The third issue is a bit more complicated. SQL Server stores indexes on 8KB pages, reading and writing entire pages at once. In theory, a page packed with information gets SQL Server 8KB of data into memory with a single disk read and lets SQL Server update as much as 8KB of data with a single disk write. Fewer disk operations are good because disk operations are one of the slowest things SQL Server has to deal with. The problem comes in the form of page splits. Imagine getting an old paper phone book, with each page crammed full of tiny print. Now imagine trying to insert some data right in the middle of a page. You can’t! SQL Server has to split the page, copying half (or so) of the data off to a new page, freeing up space on the original one for new data. That new page gets written to the disk out-of-sequence with the others, forcing SQL Server to jump from place to place on the disk as it reads those pages in the future. Those jumps consume precious milliseconds, slowing SQL Server down.
Thus, one of the most important and often-overlooked index tuning tasks is managing the index page's fill factor. In other words, when you rebuild or reorganize an index – putting its pages back in order so that SQL Server doesn’t have to jump around – how much free space should you leave on each page? More free space means fewer page splits, which means less frequent index maintenance. But more free space also means that SQL Server gets less data into memory with each disk read, requiring it to read more pages, wasting more time. Fill factor is, like the number of indexes you build, a compromise best made with the help of some Performance Monitor data that shows you how often page splits occur, so that you can tweak the fill factor appropriately.
Index tuning is such a complex art, with so many competing factors, that entire books have been written about it. Consider finding one in your favorite local or online bookstore, and get some advice for fine-tuning the indexes in your care.