Measuring IO performance before and after moving tempdb

Question

Measuring IO performance before and after moving tempdb

Thermo-879282

SSC Journeyman

Points: 90
More actions
October 20, 2009 at 8:48 pm

#212504

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

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

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

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

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

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)

Thermo-879282 SSC Journeyman Points: 90 More actions · Answer 3

I am thinking of some kind of all-in-one TSQL query that could give some general IO measure.

A simplified version of this?

http://www.simple-talk.com/sql/performance/sql-server-wait-events-taking-the-guesswork-out-of-performance-profiling/

The idea would be to run some TSQL, get a figure, move the tempdb, restart SQL, run it again get a figure to compare.

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 4

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.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

http://sqlserveradvisor.blogspot.com/2009/02/microsoft-included-fnvirtualfilestats.html#

HTH!

MJ