Home / server / Questions / 333989
In Process
Matt Penner
Asked: 2011-11-24 00:46:31 +0800 CST

How to test TempDB performance?

  • 772

I'm getting some conflicting advice on how to best configure our SQL storage with our current SAN. I would like to do some of my own performance testing with a few different configurations.

I looked at using SQLIOSim but it doesn't seem to simulate TempDB.

Can anyone recommend a way to test data, log and TempDB performance?

What about using a SQL profiler trace file from our production system? How would I use This to run against my test server?

Thanks, Matt

sql-server tempdb

2 Answers

  1. A universal recommendation - before you even install SQL Server - is to test the IO subsystem with something like SQL IO so that you know its capabilities. http://www.brentozar.com/archive/2008/11/storage-performance-testing-with-sqlio/

    Replaying trace files is a good idea, but can be rather awkward to set up. You might be better getting some scripts together that simulate different kinds of workloads. http://msdn.microsoft.com/en-us/library/ms189604.aspx

    CheckDB and reindexing (particularly online) are quite heavy users of TempDB - depending on the configuration/database size etc. You might want to benchmark typical activities such as these on your new box.

    Finally, your SAN vendor might produce a whitepaper for how to configure specifically for SQL Server. Try to find this, and if it exists - go through it with your SAN admin.

    • 1

  2. The general information that I can provide is that typically tempdb should be running on a RAID10 array as it is a high write database. As for the number of spindles that will totally depend on the server in question and how much IO the tempdb database will be generating.

    • 0