Post reply

Non Clustered Index in DML's

  • May 28, 2009 at 6:35 am

    #200814

    I have a doubt over if Non Clustered Index makes Update/Inserts Slow in performance.

    and if it does or does not ,can anybody tell why?

    Thanks

    Priyank

  • May 28, 2009 at 7:14 am

    #1001380

    Yes, nonclustered indexes slow down data operations because those changes (whether it's insert, update or delete) has to be done to the table and all the NC indexes.

    Test it out, it's not usually a huge thing.

    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

  • May 31, 2009 at 8:01 am

    #1002487

    Insert/Upates can amount to huge page splits if your fill factor for that non clustered index is low. Huge page splits causes excessive disk I/O and contribute to slow performance. You can know about huge splits if you are having fragmented indexes. You can check on sys.dm_db_index_physical_stats DMF avg_fragmentation_in_percent value to check fragmentation of your indexes.

  • June 1, 2009 at 6:55 pm

    #1003111

    Isa related ?. I want to only update a table's columns where the data has changed, but columns_updated is limited to tables with 32 columns or less.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply