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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    SQL 2005

     

    --Script

    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

     

    Investigations

    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

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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