Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
"Random distance"? –  Mark Storey-Smith Oct 27 '11 at 12:11
    
Yeah, meaning that the distance, in feet or network hops, cannot currently be determined nor should it make a difference for the testing procedure. (It will make a difference for the test results, but that won't be my problem.) –  Andrew J. Brehm Oct 27 '11 at 12:46
add comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.