0

We have just built a new server running SQL Server 2005 sp3 64 bit. The plan is to migrate our production database over to this new server from the existing server. I would like to get an idea of the performance of the new server compared to the old server (on paper it is more powerful), before we move it.

What methods would be best suited to benchmark the new server against the old, taking into account SQL Server?

damitamit
  • 71
  • 1
  • 6

2 Answers2

1

The best way to assess how a server will respond with your application is to benchmark it with your application. Unfortunately this is not quite as easy as more arbitrary benchmarks, but it does give you much more realistic view of expected performance.

Exactly how you do this depends on your application, but the general case is:

  1. take a copy of your live data
  2. run a set of common queries to test baseline response times
  3. run said queries multiple times concurrently to mimic a few concurrent concurrent users
  4. keep increasing the level of concurrency until responses get to slow or things break (time out errors, out of memory situations, and so on)

This gives you an indication of how your application will scale on the given hardware, you can say things like "on this hardware we can serve X concurrent users with response times below Y seconds". For this to be accurate you need to design the set of test queries such that it represents the usual balance of activity. This is often done by analysing what users usually do (base user logs in, gets list of tasks due, opens first task, fills in form, opens second task, fills in form, logs off) and scripting that process up so you can replay these stories many times for may users (the many users bit is important - if you use the same user and access the same few data rows in each test you will see unrealisticly high performance as everything will be running from cache where in more real circumstances disk access would be needed).

Getting this "perfect" can be bit of an art form - it requires good knowledge of how your users interact with the application. Some of our clients outsource this sort of testing to a specialist third party company when we deliver new versions of our applications, to ensure that our setup keeps our scalability and response time promises, though if you have resources to spare it can be done in-house, and is a good idea to do from time to time anyway as it can highlight performance problems introduced by new features before you put the changes in front of your users.

Search for "non functional testing" techniques, specifically "stress testing", "load testing" and "scalability testing", for more info - there are tools out there that can help automate it all, though the technique/software that is best for your app will depend greatly on what your app actually is.

David Spillett
  • 22,754
  • 45
  • 67
  • Thanks for all the info! The application is MS Dynamics AX. I was wondering if I could record a SQL profiler trace and then somehow run that on the new server (with the same data) and compare specific stats? – damitamit Jun 22 '10 at 11:40
  • That would only produce a meaningful result if the old server was flat-out while the trace was produced (which isn't likely if the trace is produced from normal user activity) or you run the replay on the old hardware too (using the same copy of the database, and run when the live application is not in use) so you can compare like-for-like (old server ran all this sequentially in X minutes, new server did the same in Y minutes). This doesn't properly test interactive response times or concurrency scaling, but might give you a reasonable expectation of likely relative performance. – David Spillett Jun 23 '10 at 18:07
1

You may also want to look into the performance of your Disk Subsystem. You can add RAM easily in future, but changes to improve disk issues will tend to require a destructive fix.

Tools like SQLIO and SQLIOsim are useful for this. SQLIOsim allows you to create a log and data file and test the performance with disk activity which resembles that of a SQL Server.

What OS are you using? If it's 2003 or earlier disk alignment is worth looking into. 2008 takes care of this for you though. Formatting disks used for SQL Data with a cluster size of 64K is also recommended.

The video below from a SQL Bits session is packed full of info regarding disk performance

http://sqlbits.com/Agenda/event5/Designing_I_O_systems_for_SQL_Server/default.aspx

Ally Reilly
  • 366
  • 1
  • 7