A common misconception among SQL Server DBAs is that they need to rebuild their indexes often—even as often as every night. The theory is that, to be effective, indexes can’t have any fragmentation. Another common misconception about indexes is that if you don’t rebuild them frequently, they’ll become corrupted, and then you’re in a HEAP of trouble. (Apologies for the pun.)
For several reasons, I don’t want to get into great detail about what fragmentation is or how it happens. First, I just don’t have enough space to do the topic justice, and second, a lot of information about this subject is readily available. Instead, I want to focus on a quick solution by highlighting a stored procedure—rebuild_indexes_by_db —that tackles only the indexes that actually need help.
Who’s Corrupting You?
Corruption in databases, and especially in indexes, was fairly common back before SQL Server 7.0, often necessitating index rebuilds. Changes in the internal structures of indexes and how the system handles them within the storage engine have essentially eliminated index corruption. Today, most index corruption results from faulty hardware or drivers—not from the database engine. Regularly running the DBCC CHECKDB database-integrity task is the best way to catch index corruption before it goes too far. It’s true that rebuilding a non-clustered index can fix the corruption, but it’s important to address the source. Simply rebuilding each night is probably masking the true problem.
Fragmentation Fallacies
Keep in mind that the primary theme of these articles is performance. Can fragmentation affect the performance of your queries? Under certain circumstances, of course it can! Although the white paper “Microsoft SQL Server 2000 Index Defragmentation Best Practices” was written for SQL Server 2000, the same principles apply to SQL Server 2005. If you’re performing typical online transaction processing (OLTP) operations—Inserts, Updates, Deletes, Selects—on small numbers of rows, fragmentation doesn’t harm you nearly as much as you might think.
Essentially, issuing an Index Seek on a single row takes the same amount of time regardless of where the row physically resides in the database file on disk. Therefore, you’ll see little or no difference in normal OLTP operations if the rows are next to each other on disk or scattered in various physical locations. However, if you’re performing lots of full or partial index scans, the amount of fragmentation can play a much larger role in how efficiently the storage engine can read that data from disk.
Re-Indexing Redux
Sure, you can attempt to re-index everything each night. But re-indexing can also have a dramatic impact on performance. In fact, re-indexing can be one of the most resource-intensive and potentially intrusive operations that you can perform. These operations can monopolize your processors, max out your disk I/O subsystem, and block users from accessing tables. They can increase the time and effort necessary to back up your logs, and they can really put a damper on log-shipped systems—even when the re-indexing occurs during off hours.
So, let’s be smart about this. Let’s tackle only the indexes that need attention. Rebuilding a nonfragmented index only wastes resources and potentially hampers other users or activities. A quick Internet search will reveal many examples of how to check for fragmentation levels, then determine which indexes to address and how. The stored procedure that Listing 1, shows—rebuild_indexes_by_db—goes beyond most of those examples to account for other concerns such as rebuilding indexes online, rebuilding by partition, utilizing a MAXDOP setting, excluding specific tables, and more. SQL Server 2005 has made it very complicated to perform online index rebuilds for all but the simplest of tables, but this stored procedure makes it a little easier.
The primary goal of this stored procedure is to loop through all of a given database’s indexes and decide which indexes to either rebuild or reorganize, given the thresholds you specify. By default, it ignores any indexes with less than 15 percent fragmentation, reorganizes indexes that have 15 to 30 percent fragmentation, and rebuilds indexes that are more than 30 percent fragmented. Although I’ve accounted for most common problems and options in Listing 1’s parameters, you might have to customize the stored procedure to suit the needs of more advanced implementations.
Forward to the Forum
I’ll talk more about the rebuild_indexes_by_db stored procedure in SQL Server Magazine’s Performance Tuning and Optimization forum. In the meantime, happy re-indexing!