2

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.

Joel Mansford
  • 985
  • 1
  • 5
  • 13
  • So are these production servers already in production or will you be putting them into production in the near future? – SQL3D Mar 04 '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 04 '11 at 16:23

2 Answers2

0

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • 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 04 '11 at 16:24
  • Using SQLIO. Big enough file to be 4-8 times ram. – TomTom Mar 04 '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 04 '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 04 '11 at 18:33
0

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.

SQL3D
  • 670
  • 1
  • 6
  • 11