• If your clustering key contains uniqueidentifier and you fill it's value with newid() it will heavily fragment that CL index and make all operations several times slower (especially inserts) than they should be.

    You can use NEWSEQUENTIALID() function instead of NEWID() to get less fragmentation.

    int identity would be even better.

    Other indexes (NC) can also slow down changes. You can deal with them:

    a) consolidate them (drop indexes which columns you have previously merged into other indexes, especially ones with the same leading key column)

    b) decrease fill factor to e.g. 90% on indexes that survived consolidation phase. Measure fragmentation before and after and how quickly they get fragmented.

    Does the table has many NC indexes? Does it have triggers or is merge replicated ?

    You could optimize files also (create several data files per that table, set decent FILEGROWTH in MB not percentage, turn on instant-file initialization, optimize transaction log initial size and growth in MB not percent).

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths