SQL Index best practices

SQL Server 2005 Index Optimization Best Practices

Published 19 February 08 07:04 AM

Do you have a maintenance window to perform database reindexing?

Have you ever performed full set of re-organize and re-index process on a bigger database?

Do you know there are best practices to deploy in this regard?

On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must. Also following are set of counters you need to keep in mind:

  • Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
  • Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
  • Smaller Index Key gives better performance than Index key which covers large data or many columns
  • Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
  • Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.

Also there are few basic guidelines when you need to create a database from scratch such as,

  • Design a normalized database.
  • Optimize a database design by denormalizing.
  • Optimize data storage.
  • Manage concurrency – by selecting the appropriate transaction isolation level.
  • Select a locking granularity level.
  • Optimize and tune queries for performance.
  • Optimize an indexing strategy.
  • Decide when cursors are appropriate.
  • Identify and resolve performance-limiting problems.
  • Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
  • Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.

Above all a DBA must have strong monitoring and troubleshooting skills, including using monitoring tools such as SYSMON & PROFILER. TO just repeat the content from my own blog about "SQL Server 2005 & 2000 – Index optimization best practices" that:

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.

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.

Further read-on in this regard can be followed on this SQLServerINdexOptimization-BestPractices blog post.