SQL Server Performance

Squeezing more out of SQL 2k5

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by 5p1d3r, May 22, 2007.

  1. 5p1d3r New Member

    I've got 2 x DL580G2's with Quad Xeon MP 2GHz, 8GB RAM, 4x72GB Ultra320 disks RAID1+0.
    Each server is running Win2k3 Ent with Sql 2k5 Ent.

    Both have close to 1M+ Lock Requests/sec with ~0 deadlocks and ~0 lockwaits.
    ... no lock bottleneck

    1000+ Write/sec, Disk Queue Length = ~10
    ... negligible disk bottleneck

    CPU = ~20%
    ... no CPU bottleneck

    With teamed network cards on GB switches the network is 1% utilized.
    ... no network bottleneck

    I've tuned everything I can think of but I can't seem to get any more out of these boxes.

    Am I missing something?

    Next step: install RAID controller and external drive enclosure to split filegroups over 3 channels (Data, Index, Log). Thoughts?
    Any suggestions on a ****-kicking dual channel RAID controller?
  2. satya Moderator

    For a second your login resembles the recent Spammer here [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], but its not once we look at your post.<br /><br />Anyway what is the issue you are having, any slow response of queries or any issues with the results?<br /><br />I presume you have upto date service packs applied on this server.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
  3. 5p1d3r New Member

    All SP's applied.

    The servers are being used to process streaming feeds from AMEX, NASDAQ, NYSE, etc.
    Currently I am about 1 week behind and need to catch up.
    If I can get a 50% improvement then I should catch up within a week and keep pace.

    There are 3 x DL380 G3 (Dual Xeon HT 3GHz 2GB) doing the processing and they are no where near straining.

    All of the performance monitors are telling me that there are no bottlenecks but I can't seem to get the data processed fast enough...


  4. satya Moderator

  5. joechang New Member

    how can you claim no disk bottleneck with a queue of 10 on 4 physical disks
    especially considering the writes/sec

    without proper analysis of the full set of relavent counters,

    i am guessing your app does lots of low row count inserts
    hence the high writes/sec, mostly going to logs, with bursts to the data

    is the cpu 20% even across all processors?
    or is one processor near 100%, if so, you do have a processor bottleneck

    if this app does do mostly 1-10 row inserts
    then the log writes/sec should be 3000-5000/sec with proper configuration
  6. 5p1d3r New Member

    oops... Avg. Disk Queue Length = 0.1

    PerfMon has been used to monitor all of the usual suspects.
    ie:http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

    Page Faults/sec = ~3

    4 CPUs are 30% (1,3,5,7) and the others are 0-20%.

    Logging has been set to 'Simple' in an effort to boost performance.

    App does 1000's of single row SELECTs and INSERT/UPDATES evenly across 20 tables through stored procedures.
    EG:

    ALTER PROCEDURE [dbo].[Update_Bar_AMEX_1] (
    @SymbolId int,
    @DateTime datetime,
    @Last money,
    @LastSize int,
    @Bid money,
    @BidSize int,
    @Ask money,
    @AskSize int
    ) AS

    DECLARE @BarId int
    DECLARE @Open money
    DECLARE @High money
    DECLARE @Low money
    DECLARE @Close money
    DECLARE @Volume money
    DECLARE @CurrentBid money
    DECLARE @CurrentAsk money

    SELECT @BarId = BarId, @Open = [Open], @High = [High], @Low = [Low], @Close = [Close], @Volume = Volume, @CurrentBid = Bid, @CurrentAsk = Ask
    FROM Bar_AMEX_1 WHERE SymbolId = @SymbolId AND DateTime = @DateTime

    if @BarId is null
    begin
    INSERT INTO Bar_AMEX_1 (SymbolId, DateTime, [Open], High, Low, [Close], Volume, Bid, Ask)
    VALUES (@SymbolId, @DateTime, @Last, @Last, @Last,@Last, @LastSize, @Bid, @Ask)
    SELECT @BarId = SCOPE_IDENTITY()
    end
    else
    begin
    if @Last > @High SET @High = @Last
    if @Last < @Low SET @Low = @Last
    SET @Close = @Last
    SET @Volume = @Volume + @LastSize
    if @Bid > @CurrentBid SET @CurrentBid = @Bid
    if @Ask < @CurrentAsk SET @CurrentAsk = @Ask

    UPDATE Bar_AMEX_1 SET High = @High, Low = @Low, [Close] = @Close, Volume = @Volume, Bid = @CurrentBid, Ask = @CurrentAsk
    WHERE BarId = @BarId
    end
  7. joechang New Member

    then you need the precise value for the log disk write latency
    try the following every 10min:

    SELECT DbId, FileId, NumberWrites, BytesWritten, IoStallWriteMS
    FROM ::fn_virtualfilestats(-1, 2)

    assuming FileId = 2 is the log (default unless you did created multiple log files)
    you can susbstitute the specific dbid in the first value (replacing -1)
  8. 5p1d3r New Member

    UPDATE:
    I have purchased an Adaptec 2200S dual channel U320 controller.

    Using the controller I will split the drive cage into two and create two partitions on each RAID1. It will look something like this:

    Drive A (2x72GB U320 15k RAID1):
    C: Windows
    D: Data
    Drive B (2x72GB U320 15k RAID1):
    E: Index
    F: Logs

    Once I have this in place I will measure the performance and follow the suggestions provided. In theory this should SIGNIFICANTLY increase disk performance (4 times?).

    (I did not realise that the DL580g2 drive cage is U320 but the on-board controller is only Ultra3 [single channel], so logically the first step was to upgrade the controller and split the cage.)
  9. satya Moderator

    What difference you observed when changing the Database model to SIMPLE and FULL?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. satya Moderator

    What difference you observed when changing the Database model to SIMPLE and FULL?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page