Database Reference
In-Depth Information
Finally, index rebuild updates statistics, while index reorganize does not. You need to factor in this behavior into the
statistics maintenance strategy in your system if an automatic statistics update is not optimal in the case of large tables.
Designing an Index Maintenance Strategy
Microsoft suggests performing an index rebuild when the external index fragmentation (
avg_fragmentation_
in_percent value
in
sys.dm_dm_index_physical_stats
) exceeds 30 percent, and an index reorganize when
fragmentation is between 5 and 30 percent. While this may work as general advice, it is important to analyze how
badly the system is affected by fragmentation when designing index maintenance strategy.
Index fragmentation hurts most during index scans, when SQL Server needs to read large amounts of data from
the disk. Highly tuned OLTP systems, which primarily use index seeks, are usually affected less by fragmentation. It does
not really matter where data resides on the disk if a query needs to traverse the index tree and read just a handful of data
pages. Moreover, when the data is already cached in the buffer pool, external fragmentation hardly matters at all.
Database file placement is another factor that you need to take into account. One of the reasons why you want to
reduce external fragmentation is sequential I/O performance, which, in the case of magnetic hard drives, is usually
an order of magnitude better than random I/O performance. However, if multiple database files share the same disk
array, it hardly matters. Simultaneous I/O activity generated by multiple databases
randomizes
all I/O activity on the
disk array, making external fragmentation less critical.
Nevertheless, internal fragmentation is still a problem. Indexes use more memory, and queries need to scan
more data pages when data pages have large amounts of unused space. This negatively affects system performance,
whether data pages are cached or not.
Another important factor is system workload. Index maintenance adds the load to SQL Server, and it is better to
perform index maintenance at a time of low activity. Keep in mind that index maintenance overhead is not limited to the
single database, and you need to analyze how it affects other databases residing on the same server and/or disk array.
Both index rebuild and reorganize introduce heavy transaction log activity, and generate a large number of log
records. This affects the size of the transaction log backup, and it can produce an enormous amount of network traffic if
the system uses a transaction log based high-availability technologies, such as AlwaysOn availability groups, database
mirroring, log shipping, and replication. It can also affect the availability of the system if failover to another node occurs.
■
Note
We will discuss high-availability strategies in greater detail in Chapter 31, “designing a High-Availability Strategy.”
It is important to consider index maintenance overhead on busy servers that work around the clock. In some cases,
it is better to reduce the frequency of index maintenance routines, keeping some level of fragmentation in the system.
However, you should always perform index maintenance if such overhead is not an issue. For example, for systems with
low activity outside of business hours, there is no reason not to perform index maintenance at nights or on weekends.
The version and edition of SQL Server in use dictates the ability to perform an index maintenance operation
online. Table
5-1
shows what options are available based on the version and edition of SQL Server
Table 5-1.
Index maintenance options based on SQL Server version and edition
SQL Server version and
edition
Index Rebuild (index
has LOB columns)
Index Rebuild (index does
not have LOB columns)
Partition-Level
Index Rebuild
Index Reorganize
SQL Server 2005-2014
non-Enterprise edition
Online
Offline only
Offline only
N/A
SQL Server 2005-2008R2
Enterprise edition
Online
Offline only
Offline or Online
Offline only
SQL Server 2012
Enterprise Edition
Online
Offline or Online
Offline or Online
Offline only
SQL Server 2014
Enterprise Edition
Online
Offline or Online
Offline or Online
Offline or Online.
