INDEXING CONSIDERATIONS - A Developer's Guide to Data Modeling for SQL Server - page 239

each other. Reorganization is an online operation by default, regardless of
the edition, and thus you can execute it with greater confidence that per-
formance and usability won't be significantly affected during the process.
When should you rebuild versus reorganize? Microsoft, in SQL Server
Books Online, recommends that if fragmentation of the index is less than
30 percent, then reorganization will likely suffice. If it is greater than 30
percent, then a rebuild will be required to effectively regain the perform-
ance boost of the index. In practice, these numbers hold up for most sys-
tems, although it is usually good to rebuild indexes regularly, regardless of
fragmentation, to help prevent them from becoming fragmented at an in-
convenient time.
Finally, these processes, however they have been set up, should be run
on a frequent enough basis that fragmentation never becomes a major
problem. It is possible for indexes on very large tables to become so frag-
mented that it takes 24 hours or longer to rebuild them. In this case, it may
be necessary to execute maintenance of different indexes on the same
table on different days. However, this situation may indicate the need to
remove or archive data from the table; use your judgment when these sit-
uations arise. For now, from a development standpoint, just be aware that
taking maintenance into account will help you decide how many indexes to
implement and balance the pros of the query performance gain against the
cons of the maintenance requirements.
Summary
In this chapter, we've taken a high-level overview of using indexes in SQL
Server. We've looked at the basic structure of an index and at the various
options available to us as developers to help enhance query performance.
Remember that usage information gathered early in the project can be a
huge help in determining the indexes that should be implemented on your
tables. We've also covered the maintenance aspects of indexes. With this
insight into the cost of using indexes, you can deliver a well-designed data-
base that performs well under the load it has been designed to handle. In
the next, and final, chapter, we discuss some enhanced design techniques
you can use to make your database more flexible and easier to maintain.