SQL Server 2005 & 2000 - Index optimization best practices
In SQL Server 2000 for index defragmentation DBCC DBREINDEX and DBCC INDEXDEFRAG statements are used, as you are aware defragmentation on table does not yield performance gains in every case. Every scenario is different. Similarly, determining when you should run the defragmentation statements requires analysis. Run DBCC SHOWCONTIG to determine index fragmentation and the decision of whether to use DBCC DBREINDEX or DBCC INDEXDEFRAG is based on your availability needs and the hardware environment. Few times Updating statistics is useful but it will be a side effect of DBCC DBREINDEX, which is not the case with DBCC INDEXDEFRAG. You can increase the effectiveness of DBCC INDEXDEFRAG by running UPDATE STATISTICS. Refer to this KBA to troubleshoot any slow performance on the application and defragment best practices white paper.
In SQL 2005 DBCC DBREINDEX is still available but DBCC INDEXDEFRAG feature will be removed in a future version of SQL Server, so better to avoid using this statement on your optimization tasks and replacement for this is ALTER INDEX, refer to SQL BOL for more information. So with the changes in SQL 2005 and usage of TEMPDB the index defragmentation strategy must be planned. Using Database maintenance plan for optimization tasks is a common plan for every DBAs, but there are TSQL scripts that can help you to choose a set of optimization tasks rather than performing all of them and wasting the system resources.
Online Indexing feature is one of the best option available in SQL Server 2005 to help manage the performance and concurrency requirements while creating or rebuilding an index. With the new Online Indexing feature (ONLINE=ON) you can continue to make queries and operations on the underlying table durig the index rebuild, while the Offline indexing (ONLINE=OFF) blocks the table. Be aware this is consume most of systems resource and depending upon the database design it might take even hours to complete, that will add unnecessary overhead for your schedule.
Make use of SORT_IN_TEMPDB option in SQL 2005 that uses temporary storage for sorting and other intermediate tasks while creating or rebuilding an index. The storage refers to be in TEMPDB and few times it can be used from the user database, or it can be used from the TEMPDB database. Using this feature determines where the intermediate sort results, generated during index creation, are stored. You can take help of sys.indexes catalog view and sys.stats catalog views to examine the curren value and settings on those indexes. When ON, the sort results are stored in TEMPDB and when OFF, the sort results are stored in the filegroup or partition scheme in which the resulting index is stored. Not all index options values are stored in metadata. Those values that are stored in metadata can be viewed in the appropriate catalog views. Refer to this TEMPDB whitepaper on the enhancement and usage of this feature in SQL Server 2005.
Here is the list that makes few best practices to follow for TEMPDB:
- Place the TEMPDB is on a disk subsystem that provides sufficient I/O throughput, and that the TEMPDB is big enough to accommodate the temporary space that is required for the index create or rebuild operation. It is a best practice to move the TEMPDB to a storage area with sufficient space and performance after you install SQL Server. Also, note that the TEMPDB database is a common resource for the entire instance of SQL Server. You should consider the activities in all the user databases that might be using TEMPDB while you plan for the TEMPDB.
- For less time to create index or rebuild, use the offline option (ONLINE=OFF). This is recommended during less traffic or no user activity on the database as it will restrict the access to database.
- To achieve less effect on user access for tables, use the online option (ONLINE=ON). However, an online operation takes more time and uses more TEMPDB space as compared to an offline operation.
- To use the least amount of space in TEMPDB while you rebuild a clustered index, use the offline (ONLINE=OFF) option. This will have knock on affect on concurrency because access to the table is prevented for the duration of the index rebuild.
- To use the least amount of space in TEMPDB while you rebuild a non-clustered index, use the online (ONLINE=ON) option, but this will take longer time to complete with best concurrency.