Index Maintenance
Yesterday I talked about SQL Server Indexes and how important it is to keep them from becoming fragmented in your database. Defragmenting clustered indexes cause all the data to be grouped contiguously on the disk. Defragmenting non-clustered indexes cause those indexes to also be located contiguously on the disk. So, in order to defragment both the data and the indexes, both need to be re-built in some fashion in order to remove disk fragmentation of your tables.
Today I want to extend that topic, talking about options you have for index defragmentation.
In different versions of SQL Server you have different options for Index Defragmentation. In the Enterprice and DataCenter editions of SQL Server 2008 and later, indexes may be rebuilt in the background while the table is still in use. New indexes are created as a background task, and when complete, the old index is replaced with the new one.
In other versions SQL Server 2008 you also have the option of simply reorganizing the leaf nodes of an index. This works nicely with Clustered Indexes, but does not fully rebuild the whole index. Only the nodes where data is physically stored in clustered indexes, or the nodes that point to the data records in non-clustered indexes, are re-arranged in a contiguous form. This option is often adequate when the database experiences high activity.
A third option available in all instances of SQL Server is to rebuild the index completely. In older version a DBCC command may be used to rebuild the index. If a clustered index is rebuilt in older versions, then the non-clustered indexes are always rebuilt as well.
In newer versions other options are available to rebuild the indexes. Rebuilding the clustered index does not always cause
the non-clustered indexes to be rebuilt as well.
SQL Server 2008 has the preferred syntax of ALTER INDEX (see BOL for more information regarding the complete syntax) allowing you to rebuild one or all indexes on a table or view. This is the syntax you would use to perform either a complete index rebuild or partial index reorganize in SQL Server 2008.
So, what option do you choose and when? That is a question I would like to get some reader feedback to help our readers understand what has worked for different situations. It will be useful to know a little background in your responses.
What patterns of index maintenance do you use, never, monthly, weekly, daily, more often?
Perhaps you use more than one pattern. For a pattern you use, what kind of database is it on which you have established a specific pattern? It helps if you specify the size of the database in MB or GB, perhaps the numbers of records in your large tables, the type of database (OLTP, OLAP), and the frequency and volume of data that is modified.
Here is a real world example from my experience. I have a client where I literally have to re-organize certain tables ever hour during business hours in order keep optimum performance, because the entire contents of these tables change two to three times daily. There are less than a million rows in each table. I rebuild the other indexes nightly if they are more than 10% fragmented…otherwise, I simply update the statistics. This database is a hybrid for both OLTP and OLAP. The hourly re-indexing supports the OLAP functions with a few rapid changing dimensions.
One note, this topic is not restricted to only SQL Server. Feel free to share your experience using other data engines as well. If there are index management features beyond those presented here, that would be good for you to share as well.
Send your index experience to btaylor@sswug.org. I’m looking forward to your feedback.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Sharepoint (Part 3 of 3)
In this session, attendees will learn the basics of Microsoft Windows SharePoint Services (WSS) and Microsoft Office SharePoint Server (MOSS). This session will cover some of the basic use-cases for both WSS and MOSS, and will discuss when one should be chosen over the other. This session will also demonstrate a commercial application that is based on the MOSS platform.
Featured White Paper(s)
Enterprise Content Management
Written by AvePoint
The goal of an enterprise content management (ECM) system… (read more)
Featured Script
mfnCalculateHoursMinutes (shift time)
The function returns the hours and minutes between a starting and ending date. It was developed to calculate the hours and mi… (read more)