October 5, 2012 at 11:10 am
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
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
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
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 5, 2012 at 4:55 pm
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