TempDB BottleNeck ?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    Ok, here is the problem.

    Our Prod system is having Performance problems.

    what I know:

    All drives are RAID 5 (ON an EMC SAN).

    We use TempDB a LOT (I know, I know...we shouldnt)

    TempDB is Placed on the same logic drive as the Data

    So....I recommend that we switch to RAID 1+0 (we are not a broke company)

    I recommend moving TempDB to it's own logical drive RAID 0, 1 or 1+0 (Not 5)

    the hardware guys say "This is not the problem"

    so.....I need Statistics to PROVE that my hunch is correct.

    how do I gather stats to PROVE that moving TempDB and switching to RAID 1+0 will improve perf by more than just a few % points...?

    we are not seeing massive Disk Queueing at this point. So what other stats do I grab to prove my point ?

    thanks

    Greg J

    Gregory A Jackson MBA, CSM

  • PW-201837

    SSC-Insane

    Points: 20805

    How have you determined that the location of tempdb is the issue ?

    Maybe it's how you're using it versus where it's physically located ? Do you have long-running SQL that builds ##emp tables ? If so, what form are they in ?

    Are they:

    SELECT * INTO #TempTable

    FROM SomeOtherTable

    Or are they:

    CREATE TABLE #TempTable ( {columnlist} )

    INSERT INTO #TempTable

    SELECT {columnlist} FROM SomeOtherTable

    Also, if you're building large #Temp tables and joining them to other data, are you indexing the #Temp tables ?

  • GregoryAJackson

    SSCrazy

    Points: 2794

    well...I dont "Know" that the location of TempDB is the problem.

    I "Suspect" that it is.

    I want to be able to definitively prove if my hunch is right or wrong.

    The fact that TempDB is located where the data resides is a common issue that can cause contention.

    But...I dont see disk Queueing. So, If I dont see disk queueing, does that mean I'm barking up the wrong tree ?

    yeah we create temp tables specifically and then Insert Into Them (the preferred way).

    Yes the Temp Tables have indexes when it makes sense.

    When we can, we use Table Varialbes instead. But we cant do t his in all cases....

    GAJ

    Gregory A Jackson MBA, CSM

  • MohammedU

    SSCoach

    Points: 19644

    What is your build number?

    Take a look Concurrency enhancements for the tempdb database article applies to sp3...

    http://support.microsoft.com/kb/328551

     

    MohammedU
    Microsoft SQL Server MVP

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    Tricky things SAN's and tempdb !! OK, you'll never get decent disk queue stats froma SAN try using disk i/o completion time, this will show if the SAN is an issue ( which in a number of scenarios I've dealt with has been the case )  I could write a small book on how a SAN can screw your database performance and how the vendors and your SAN guys will "prove" otherwise. fn_filestats is your friend for collecting information about tempdb - use that in combination to see which database on your server has the highest trans/sec, this is an indication of tempdb probs if tempdb has greater than 50% of all trans.

    if your read/write ratio is greater than, say, 85/15  (  >15 writes )  raid 5 is bad news - raid 5 is ok for reads but absolute pants for writes, including on a SAN. Finally get a physical map of the drives and partiitioning for the SAN, if you don't have dedicated spindles then find out what you share with etc. etc. It's a long hard slog  and I talk from experience, which is why I got SNIA certified. ( http://www.snia.org )

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • GregoryAJackson

    SSCrazy

    Points: 2794

    this is great info. Thanks for the reply.

    GAJ

    Gregory A Jackson MBA, CSM

  • Wesley Brown

    SSChampion

    Points: 12045

    Sharing spindles with other apps is a huge killer on san's and is much more common on the ones that do virtualization you tend to get hot spots and you are always doing random I/O.

    performance increase between raid 5 and raid 10 is simple math 4 iops for a write in raid 5 vs. 2 iops in raid 10.

    Check out SQL SErver 2000 Performance Tuning Techincal Referince from ms press around page 40 it gets into some decent detail on disk and I/O with diffrent raid levels.

    Also, If you move tempdb add a data file for every proccessing core you have if you are killing tempdb now it will help some with the creation and deletion of objects in temp and widen the I/O path alittle bit.

    Wes

    http://www.sqlserverio.com
    http://www.cactuss.org
    http://www.salssa.org

  • Vladan

    SSC-Insane

    Points: 22069

    I don't know enough about hardware issues, especially not about SAN, but I know that we had precisely the same problem. Started with Raid 5 and tempdb on the same drive as data. Performance : terrible. Changing from RAID 5 to 10 brought a drastical improvement; separating TEMPDB on another drive brought less, but still considerable improvement.

    I think both your suggestions are very probably to the point and although a lot depends on what types of processes run on your server, how many concurrent users, read/write ratio etc., such change should always help. BTW, our LOG file also resides on a different physical drive than the data file.

  • GregoryAJackson

    SSCrazy

    Points: 2794

    Thanks for the reply.

    Our system is very write intensive.

    Even our Reporting sprocs do tons of writes to tempTables before writing out final results.

    we are working feverishly on removing all temp table usage, all User Defined Functions, etc.

    we are making progress with improving performance on the T-SQL\app side but I still firmly believe that the smoking gun is the RAID 5 and File Placement issue.

    I also found that SQL server is configured to use Named Pipes instead of TCP\IP (go figure).

    Now I just have to battle the politics to get all the hardware\networking issue fixed. I'm only allowed to dabble on the app layer.

    cheers,

    GAJ

    Gregory A Jackson MBA, CSM

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    been there, book, T shirt, baseball cap, etc. etc. kick raid 5 into touch

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 10 posts - 1 through 10 (of 10 total)

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