October 20, 2009 at 8:48 pm
We would like to gain IO improvements by moving tempdb to its own drive.
That is away from the data and log drives.
What TSQL can I run that will give me a performance measurement?
I would measure the IO pre and post moving the tempdb.
Thank you
October 20, 2009 at 9:28 pm
If you're not moving to faster drives, I'm not sure you get better performance from tempdb, it's more likely that the overall I/O on the server instance is better.
What you can measure is disk Qs, and then reads/writes per second. Note them before and after. If you want, you can use a tool like SQLIO that generates a lot of transactions and measure the throughput and rates you achieve for disk IO.
October 20, 2009 at 10:14 pm
I'm not so sure that's true, Steve. Just like with Log Files on full recovery DB's, a time honored server performance "trick" is to move TempDB to it's own drive to give it it's own R/W heads.
The only way that I'd know to figure out if it actually made an impact on performance would be to run RPC: Completed in Profiler and capture the Duration for some of the heavy hitting procs. Do the same after the move.
I can't say personally that it makes an improvement because I've never done it. Normally I go into a shop and they don't want to mess with such things OR, they already have TempDB setup on it's own spindles. I'm going on what seems to be an overwhelming consensus on this and many other forums.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2009 at 2:45 am
I am thinking of some kind of all-in-one TSQL query that could give some general IO measure.
A simplified version of this?
The idea would be to run some TSQL, get a figure, move the tempdb, restart SQL, run it again get a figure to compare.
October 21, 2009 at 8:36 am
It depends on how heavily you're hitting tempdb, and how much IO you're using. I've seen plenty of servers that weren't using tempdb heavily, or were heavy read-weighted servers. Moving the log to its own spindle didn't help.
The Simple Talk article might work fine. I haven't tried it, but some measure of time and work is what you need. That one looks fine, but depending on how heavily you stress something, you may or may not see the difference. If your sorts are .0001s faster, will that make a difference in your server performance? Hard to say.
Ultimately I think you need some metrics that you can point to, but the appearance of how things run on the server from the clients will matter more.
Be sure that if you test once you've moved tempdb that you warm up the cache. Run a normal load for 10 minutes to be sure that all the data that was in the cache when you first ran the test is there at the end.
Or run before/after tests with a cold cache. That might give you a better idea since you'll be loading things from scratch.
October 21, 2009 at 12:59 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply