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.

We have several production SQL servers, some physical, some virtual. I would really like some generic scripts that I could run against each maybe using AdventureWorks to stress test them and compare the performance.

Does anyone know of anything which is commonly used? I understand that different types of load (e.g OLTP, DW) require different hardware but I need something to get started in order to give some accurate comparisons between the different environments.

share|improve this question
    
So are these production servers already in production or will you be putting them into production in the near future? –  SQL3D Mar 4 '11 at 15:38
    
They're already in production. I would like to make a case for improved hardware. Simple stats like this 4-cpu VM is 50% slower than the 8-way physical for this type of query (which I will translate to business speak) –  Joel Mansford Mar 4 '11 at 16:23
add comment

2 Answers

Wond work.

run against each maybe using AdventureWorks

Pathetic database. WAY too small for real word testing. Waht you want to test in particular?

There are tests, but they require a lot of hardware.

In general, I do a good SQLIO test, plus you can do some cpu based tests. That is about it.

share|improve this answer
    
I'm not hung up on AdventureWorks, just after something with some credibility / well know. How are you doing a 'good SQLIO test' ? –  Joel Mansford Mar 4 '11 at 16:24
    
Using SQLIO. Big enough file to be 4-8 times ram. –  TomTom Mar 4 '11 at 16:38
    
Since these servers are actually in production, I would recommend running SQLIO only during a maintenance window, as it could impact performance on these servers. –  SQL3D Mar 4 '11 at 17:49
    
Absolutely. But this is the only way to measure how fast you CAN go - no load, and see how far you go. –  TomTom Mar 4 '11 at 18:33
add comment

If you are just looking to get some baseline stats on your servers, I would recommend starting with Perfmon counters. Brent Ozar has an excellent tutorial here: SQL Server Perfmon Counters Tutorial

These stats are great for identifying bottlenecks, trending usage (when you run multiple times), identifying servers you may wish to consolidate, etc.

share|improve this answer
add comment

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.