Sql-server – Any general reasonable performance tests for DBs on application server and DBs on separate server

performanceperformance-testingsql serversql-server-2008-r2

My company are planning to move the DBs (SQL Server 2008 R2) from the application server to another server (random distance). All servers are Windows 2003 x86.

Are there any general performance tests in SQL Server I can run to find out if under three different circumstances (no load, normal load, full load) this configuration is performing better or worse than the original configuration where application and DBs were located on the same server?

(I do not have more details yet.)

Best Answer

If you can capture a typical workload with Profiler, you could replay it against a point-in-time restored copy of the database via the RML tools. Summary of the RML tools can be found in Testing Stored Procedure Scalability.

What you decide to measure as part of these tests in dependent on the application. Measuring response time is simple for a web application for example, not so easy with a desktop client.

It would be rare to find that performance degrades when you move the database to a dedicated server. Typically this only occurs when the application is overly "chatty" i.e. executing lots of iterative queries (incorrect use of an ORM a common cause), rather than set operations. In this situation the additional overhead of marshalling data over the wire via TCP or Named Pipes vs Shared Memory Protocol outweighs the performance gained from dedicating a server to SQL.

Related Solutions

Get and Put performance testing on Google BigTables (and other integrated DBs)

It seems to me your problem is that you are trying to test performance metrics that are not well supported in the underlying db. This makes it very difficult to compare performance across systems because the underlying approaches are very different. I don't think it is possible to do apples to apples comparisons just as I don't think you can do an apples to apples comparison of ORDBMS type approaches to RDBMS type approaches. The performance concerns are just too different and if Stonebraker is right that optimizing an ORDBMS for TPC-C tests misses the point, then for systems that are even further apart it is going to be impossible. (I think he is right there, however, only where ORDBMS functionality comes into play.)

I think what you need to be honest is to look at how you would use each system and build a benchmark tool based on the approach you'd take with each. Then you can say at least for that workflow that the benchmark shows something specific. I don't see how you can generalize however. You could further run it in a profiler in order to get additional information about where the time is being spent in the test run on various engines.

However db benchmarking is very difficult to make meaningful in the best of circumstances and when you are comparing dissimilar systems it becomes impossible to generalize.

SQL Server – I/O Requests Taking Longer than 15 Seconds

Note that all servers use the same NICs - Broadcom 5709Cs with up-to-date drivers. The servers themselves are Dell R610's.

Kyle Brandt has an opinion on Broadcom network cards which echoes my own (repeated) experience.

Broadcom, Die Mutha

My problems have always been associated with TCP Offload features and in 99% of cases disabling or switching to a-n-other network card has resolved the symptoms. One client that (as in your case) uses Dell servers, always orders separate Intel NICs and disables the on-board Broadcom cards on build.

As described in this MSDN blog post, I would start with disabling in the OS with:

netsh int ip set chimney DISABLED

IIRC it may be necessary to disable the features at the card driver level in some circumstances, it certainly won't hurt to do so.

Best Answer

Related Solutions

Get and Put performance testing on Google BigTables (and other integrated DBs)

SQL Server – I/O Requests Taking Longer than 15 Seconds

Related Question