4

I need to compare SQL query speed on two servers, with the same databases, one is an Amazon RDS SQL Server instance and the other SQL server is on IBM Softlayer. What do I need to do and how do I go about doing it. I need to generate a report for the comparisons to management. I am a SQL novice. Thank you.

  • Try and use a reply tool with your own systems' load(s). Running a SELECT * FROM tab for a three record table won't tell you much! :-) – Vérace Sep 13 '17 at 5:55
  • Do you already have the database and workload you want to test or are you planning to create one for testing? There are many factors to consider with performance testing that can skew results, especially with different PaaS offerings. – Dan Guzman Sep 13 '17 at 11:30
  • Are you planning to use both databases over the internet? – eckes Sep 13 '17 at 11:42
4

One of the best ways to compare two servers is to repeat tasks that you (hopefully) normally do on both:

  1. Run DBCC CHECKDB
  2. Take Full backups
  3. Run any index or statistics maintenance

And of course, one of the best ways to measure the impact they have on your server is to look at wait stats. If those pass, then move on to comparing queries. You generally don't want to test query performance in a vacuum, because a whole lot of other stuff may not run the way you expect it to

I wrote a whitepaper for Google earlier this year on how to do this. It's free, and I suggest you give it a read. It walks through how to do all the stuff you're interested in. Even though it's done using Google's Cloud, it's applicable to any server comparison.

1

SET STATISTICS TIME ON, (also, always run these two statements before running query in Non-Production environment), DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS to clear cache and buffers)

Include Actual Execution Plan button when running queries

Please utilize Windows Performance Monitor or other DBA tools (Sql sentry, solarwinds, Redgate) to review performance metrics such as CPU%, Memory usage, as needed, etc

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.