SQL Server Performance

Updates on Clustered Indexes

Discussion in 'SQL Server 2005 General DBA Questions' started by ffebob, Oct 11, 2006.

  1. ffebob New Member

    What are the pros and cons of putting a clustered index on a column that has a lot of updates on it? It seems that there would be a lot of overhead in reorganization of the table to accommodate the new index values, and any non-clustered indexes would have to be updated. Anybody have anything to say?
  2. Roji. P. Thomas New Member

  3. satya Moderator

    The optimizer reliably chooses the most effective index in the majority of cases. The overall index design strategy should be to provide a good selection of indexes to the optimizer, and trust it to make the right decision. This reduces analysis time and gives good performance over a wide variety of situations.
    -Examine the WHERE clause of your SQL queries, because this is the primary focus of the optimizer.
    -Use narrow indexes, the optimizer can rapidly and effectively analyze hundreds, or even thousands, of index and join possibilities. Having a greater number of narrow indexes provides the optimizer with more possibilities to choose from, which usually helps performance.

    Even UPDATE and DELETE operations are often accelerated by clustered indexes, because these operations require much reading. A single clustered index per table is allowed, so use this index wisely. Queries that return numerous rows or queries involving a range of values, are good candidates for acceleration by a clustered index.


    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page