I've been given a task to test where an SQL Server 2012 can achieve better performances - we have two options:

  • Use the SQL Server as an On-Premise machine (Standalone server that we have)
  • Use the SQL Server as a cloud-based server.

What is the best way to check which option gets better performance for our needs? (CPU + I/O performances), is there any recommended benchmark tool I can use? I thought about writing some SQL queries of various types and run them against both options, but I'm not sure which queries I should use.

Are there any recommended type of queries that can give some good glance about the performance?

  • as a cloud-based server, or on a cloud-based server? – Jon Seigel Aug 1 '12 at 18:30
  • on Windows Azure SQL Server VM. – royv Aug 1 '12 at 18:32
  • Unless you were planning to deploy your production server to Azure, why would that be a useful test? Almost everything is guaranteed to be different. – Aaron Bertrand Aug 1 '12 at 18:41

I would say using a local server would be better. At least that way you know the hardware and can simulate it as close to the production server as possible. With a cloud-based server you have no idea what the underlying hardware is, and with some services like Azure you will have to take the 3x write policy into account, different network connectivity, volume bursts for other activity within your "cloud", etc. So your performance picture will be quite different even if the hardware might be similar.

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.