January 10, 2013 at 2:36 pm
Our application currently runs on a SQL2005 SP4 SQL Server running on Windows Server 2003. This server has 4 cores and 16gb of memory. We are planning on migrating the database to a new server, Windows Server 2008 R2 running SQL Server 2012, with 8 cores and 32gb of memory. Of course, the new SQL Server is going to be a lot faster than the old one, but the management wants to know exactly how much faster.
What is the quickest and easiest way to get some numbers on how much faster the new server will be? We have built a development version of the new server for testing the upgrade process. One test I was thinking of doing was taking a large query that runs regularly on our current system and get an average run time for it. Then compare that to the average run time on the new server. The problem there is that the test system doesn't have the production work load going on it, so the test system isn't fighting the same load.
Any ideas? Any (free) tools out there for accomplishing this?
January 10, 2013 at 3:04 pm
Actually, your best bet for that is replaying a trace file from the current server on the new server. Details on how to do that are here: http://msdn.microsoft.com/en-us/library/ms189604(v=sql.105).aspx
Collect the new stats, in a trace on the new server, and you'll have exactly how much better, stronger, faster, you have rebuilt it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 10, 2013 at 5:18 pm
I'd agree with Gus. a workload from your current server is a great way to benchmark.
January 11, 2013 at 4:54 am
Since you're moving to 2012, you can use distributed replay to test the new server. That'll work better than using Profiler. And, it's a great new tool in your 2012 toolbox.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 9:56 am
More Cores and RAM will be a great advantage, but I was bitten by this. In my environment the SAN was the bottle neck, and we saw a degradation when upgrading from SQL Server 2005 to 2008 R2 last year.
I addition to replaying a workload, you should run SQLIO on the new server. If you can get some downtime run the same SQLIO against your current production server. This will give you a good comparison between the systems. (And in my case would have saved me the embarrassment of a loss in performance because of a configuration error.)
Crystal DiskMark is another good way to compare IO performance without having to incur the downtime of SQLIO. Brent Ozar has a good blog post on this. http://www.brentozar.com/archive/2012/03/how-fast-your-san-or-how-slow/ He also has a good tutorial on benchmarking your IO using SQLIO. http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/
Good luck with your upgrade, and hopefully you will realize some real gains.
Greg Roberts
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply