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.
One of the best ways to compare two servers is to repeat tasks that you (hopefully) normally do on both:
- Run DBCC CHECKDB
- Take Full backups
- 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.
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