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.)
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.
