Post reply

TempDB avg_write_stalls

  • October 5, 2012 at 11:10 am

    #282631

    I have tempDB on a RAID 10 which has an avg_write_stall_ms number of 217.7

    This is io_stall_write_ms / num_of_writes.

    I know the answer is it depends, but is that something that is bad? Does that number 217 Jump Out on you ?

    BTW there are close of 90,000 writes to TemDB / Day on an average.

    For comparison sake, the avg_write_stall_ms for User DBs is less than a 1 sec for most of them (of course they reside on a different disk).

    Any input will be appreciated.

    Thanks

  • October 5, 2012 at 1:18 pm

    #1546298

    What are your wait stats? Are users complaining?

    Really, those are the big "it depends" questions.

    Are you trying to tune something, or just curious?

  • October 5, 2012 at 1:52 pm

    #1546303

    I inherited some DBs and was asked to find Queries That are Slow and Fine Tune them.

    I do run the CPU and IO Intensive Query off of DMVs.

    But it is a 22 GB DB not a large one.

    And most of those queries were using TemDB a lot.

    Checked to find out avg_stalls and saw this descrepancy between User DBs and TempDB.

    So was wondering if that is a problem.

  • October 5, 2012 at 4:33 pm

    #1546346

    Yeah, 217 is basically awful. Typically you want to see ~(20-)30ms, although the "base" time will vary depending on your specific disk configuration, of course.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 5, 2012 at 4:55 pm

    #1546350

    See, what I am trying to get is, the DBs that I have been asked to fine tune are going to TempDB quite a bit.

    And the TempDB is located on an awful drive. So how can I fine tune queries of lot of which are hitting TempDB ? I mean is this a valid argument to present.

    What I am going to suggest is may be we need to put the TempDB on a good drive and then see what kind of performance the queries are giving us and then start the fine tuning process.

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

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