***
In this tutorial you will learn about maintaining Indexes in a SQL Server 2005 database - Dealing with Fragmented indexes, Reorganizing an Index, Rebuilding an Index, Disabling Non-clustered Indexes to Reduce Disk Space During Rebuild Operations, Rebuilding Large Indexes, Setting Index Options, Page Splits and Performance Considerations, Max Degree of Parallelism, Online Index Operations, Partition Index Operations, Statistical Information, Asynchronous Statistics Updates, Disabling Automatic Statistics, Statistics after Upgrading a Database to SQL Server 2005, Bulk copy options and Index operation logging.
The DBA has to ensure optimal performance of the database. One of the key elements in database maintenance is to ensure that minimum disk I/O is performed in queries. Making good indexes is a means to this end. The activities a database administrator performs to maintain his indexes are:
1. Reorganizing and rebuilding indexes. This process involves defragmenting of indexes and optimization of disk space.
2. Using the Fill factor option to fine tune the index data storage and performance.
3. Performing index operations online by using the LINE index option to provide user access to data during index operations.
4. Configuring parallel Index operations by using the max degree of parallelism option.
5. Using the Query optimizer to develop optimal query plans based on statistics.
6. Improving the performance of bulk copy operations on tables with clustered and non clustered indexes.
7. Selecting suitable Recovery model of the database for index operations and listing the types of logging available.
Read
More...
http://www.exforsys.com/content/view/1858/356/