Post reply

Tempdb

  • September 15, 2008 at 9:38 am

    #154728

    Hi,

    How can I determine how heavily used (or not) the tempdb file is? I'd like to find out to see if it's worth the effort of moving it to it's own drive. Since the server is suffering poor performance I am in the process of various activities to improve that and want to see if that's one option that might help.

    Cheers,

    Richard

  • September 15, 2008 at 10:01 am

    #871854

    Performance Monitor...

    Add two indicators,

    SQL Server Databases in general --- transactions per second

    SQL Server Databases, TempDB --- transactions per second

    That would give you an idea of how heavily TempDB is used when compared with your whole environment.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • September 15, 2008 at 10:12 am

    #871879

    You can monitor the transaction/sec via perfmon (SQL Server: Databases (tempDB)), then you can look a the IO throughput with the sys.dm_io_virtual_file_stats DMV. It's cumulative since SQL last started, so take snapshots of the values and look at the changes to the values over time.

    If you're seeing a lot of IO stalls, it would probably be an idea to move TempDB to a faster/separate drive

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 15, 2008 at 10:55 am

    #871919

    Thank you both! 😀

    Richard

  • September 22, 2008 at 7:30 pm

    #875366

    IMHO... it's ALWAYS worth doing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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