Of all the things you can do to Microsoft SQL Server, indexing arguably has the biggest impact on performance –...
both positive and negative.
Each index you add has the potential to speed up operations that read data from the database, but you will also slow down operations that write data to the database. The trick is to come up with a set of indexes that provides maximum readability with minimal write impact.
So how do you arrive at the right indexes?
The easiest way is to use SQL Server Profiler (running on a machine other than the server you’re profiling) to capture a day’s worth of real-life production traffic. Actually, if you can capture a truly representative sample of data in less time -- say, an hour -- then do so, as churning through all that data will take some time in the next step. You can use SQL Server Profiler’s built-in tuning template so that you’re capturing the right information and logging it to a file on the computer where SQL Server Profiler is running.
Next, fire up the Database Engine Tuning Advisor (DETA), feed it the SQL Server Profiler data and let it go to town. Remember, if you’ve captured a lot of traffic -- such as a whole days’ worth -- it may take longer for the advisor to process.
The Database Engine Tuning Advisor is going to need some time to think as it examines all the real queries that have been run against the database through SQL Server’s query optimizer and asks the optimizer what indexes it would use to execute that query. It looks at the resulting execution plan to find any areas that are slow and then proposes more or fewer indexes to see what impact they will have.
Each suggestion receives a score and the DETA moves on to the next query. If the next query will benefit from one of the same suggestions, that suggestion gets an increased score. After analyzing everything, the suggestions with the best scores win and the advisor shows them to you.
Note: This is a sort of human-level explanation of what goes on in the Database Engine Tuning Advisor’s mind – the reality is much more technically complicated, but the results are what count!
The cool part is that the Database Engine Tuning Advisor implements the suggested changes for you. In turn, you can accept or reject each suggestion and the advisor will automatically execute the ones you decide to keep. It’s a fast way to quickly create positive change for your system based on real-world usage patterns and statistics.
Of course, ongoing index maintenance is just as important as having the right indexes in the first place, so here are a few things to keep in mind:
- Reorganize your SQL Server indexes fairly often to reduce in-index page fragmentation caused by page splits.
- Rebuild indexes somewhat less often, as doing so is more time-consuming and impactful on the application. I like to reorganize typical database indexes once a week and rebuild once a month during a maintenance window.
- Ensure that your database has up-to-date index statistics, either by allowing SQL Server to automatically update statistics (a database configuration setting), or by updating stats manually as part of a nightly maintenance job.
Miss a tip? Check out the archive page for Don's series, SQL Server for the Reluctant DBA.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.