Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.