SQL 2005 only using 5MB/sec throughput on Disc Subsystem RRS feed

  • Question

  • SQL 2005



    Create table ATable


    ID bigint identity(1,1)

    ,SomeColumn tinyint



    Create clustered index Atable_ID_Clust

    On ATable(ID)


    Create nonclustered index ATable_Somecolumn

     On Atable(SomeColumn)


    /*Some more code and F5 and ATable has 480 mill rows*/


    --Problem Statement

    Update Atable

    Set SomeColumn = 1

    Where somecolumn = 0


    Rowcount for Atable with SomeColumn = 0 is 477mill


    Execution time 16 hours elapsed

    I executed Select count(*) from ATable with (NOLOCK) where SomeColumn = 0


    Results = 0



    Ok this was just the start. This now lead to many other investigations. Starting with the basics, locks? none, only user on machine, Server is paging? No perfmon shows 0.6 and 0.9 % of pagefiles used


    SQL tells me that is is waiting for the disc subsystem WaitType = “PageIOLatch_EX” Again Perfmon comes handy. Check disc throughput 4 – 6 MB/sec. This could not be possible. Ran SQLIO and pushed a 1GB file through to the drive with DBs on, Throughput 130MB/sec – 250MB/sec


    If I create an exact duplicate of ATable and do insert into BTable (Select * from ATable) the throughput is 10 MB/sec to 30MB/sec


    CPU’s Idle @ 9% (16 CPU’S), Drives with page files idleing at 1MB/sec if not 0, Memory cap 32 GB, SQL using 27GB


    Only issue I found was memory page faults / Sec that are excessively high am unable to do memory test.


    Any help PLEASE that does not include violence like kicking / shooting / throwing or formatting


    Friday, June 3, 2011 10:37 AM


  • There is a large cost to maintain the non-clustered index when a significant number of values change.  If you are updating more than say 15% of the SomeColumn values, I think will get much better performance if you disable the non-clustered index and rebuild afterwards.

    ALTER INDEX ATable_Somecolumn ON dbo.Atable DISABLE;
    UPDATE Atable
    SET SomeColumn = 0
    WHERE somecolumn = 1;
    ALTER INDEX ATable_Somecolumn ON dbo.Atable REBUILD;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Peja Tao Tuesday, June 7, 2011 5:59 AM
    • Marked as answer by Peja Tao Tuesday, June 14, 2011 8:48 AM
    Friday, June 3, 2011 11:33 AM