8

In my company we have multiple virtual machines hosting SQL Server 2008 R2 and some of these machines perform differently than others, some because of very busy Vmware hosts some because of slower connections to NAS.

Is there a way I can run some SQL code in a test SQL database or other known best practices I could use to run some performance tests in each VM and baseline/benchmark performance so that I can understand which machine behaves better than others prior to moving those machines to Prod or UAT environments? Thanks, Davide.

5

You could use one of the free SQL benchmarking tools such as Dell's Benchmark Factory for Databases (BFD) or Hammerora. Benchmark Factory has a free trial and Hammerora is opensource. They both run industry-known TPC benchmarks, and you can do custom workloads with BFD. I have only used BFD and each test 'run' can be saved as a report which I found really useful for comparisons. However Hammerora is also well supported ( eg here and here ).

You could also record one of your own known workloads with a Replay trace via Profiler / server-side trace then restore the backup and replay it in each environment. There's a bit more work to this option but would make for a more familiar test.

I also have a soft-spot for ostress which is part of the RML Utility Suite which had an update in April 2014 and I still use all the time. More of a learning curve with this free Microsoft tool, but it's really powerful. For example you can do things like "run all the .sql scripts in this directory 20 times, simulating 50 concurrent users".

In summary, if you're after a free tool and a quick start look at one of the free tools I mentioned above. If you want to invest a bit more time, have a look at Replay traces, RML and ostress.

HTH

2

SQLIO seems like it is probably as good of a benchmarking tool as you could want for something like this since it sounds like you are looking to specifically check potential issues related to storage.

Brent Ozar has a great blog post to walk through that here - Brent Ozar SQLIO Tutorial

0

Yup! DiskSpd works great. It's actually one of my favorite tools at this point. It's available for free at:

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

And remember to use the -Z1G command to make the write tests better for the real world.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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