• bwilliams-1049831 (3/28/2012)


    Avoid using fields in indexes that are updated frequently.

    Status fields are a prime example. Each time the field is updated the index has to be updated.

    Maybe. Depends on how often that field is filtered on, how important the queries that filter on it are, etc. It's a tradeoff and has to be evaluated case-by-case. If the status is frequently updated but seldom searched on then an index on it would indeed be a bad thing.

    Indexes on frequently changing columns don't automatically cause deadlocks, if you're talking about key lookup deadlocks then the usual way to prevent the deadlocks is to ensure the index is covering (or use one of the row version based isolation levels)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass