SQL Server Performance

Stress testing when migrating to a new system

Discussion in 'General DBA Questions' started by sql_er, Nov 24, 2010.

  1. sql_er New Member

    Hi,We recently migrated out MSSQL 2000 servers to another system.We have both systems working in parallel for the next few weeks, at which point our original system will be shut down.The original system, system_A, has new data and new requests coming in all the time, as it is live. The new system, system_B, has identical set of data and new data coming in just the same, but no requests are coming into it [e.g. no stored procedure calls].The goal is to make sure that system_B is at least as good as system_A. By good I mean at least the following:a. Stored procedure performance is at least as good [all applications just make stored procedure calls]b. Replication is working finec. Jobs take similar amout of timeSo far replication [transactional] and job time runs seem fine. My main concern right now are the stored procedure runs, which need to run in sub-second time.I am guessing that I need to do some kind of stress testing on system_B.I did some exploration and got some ideas/tools, but not sure which is the best way to go.For example, I read about Profiler Replay option. I then recorded a trace file using Replay Template on system_A and then ran it on system_B and simultaneously running a standard trace on system_B to see what the results would be. Strangly, while the replay ran, nothing got recorded using the Standard Trace. I need to explore this further. Additionally, I read someone's post saying that Replay option is not real world case as there is no 'think time' added on. Additionally, we've been using ACT tool to send db requests similar to the ones we have in production. The same requests were sent to one of the servers (which is currently out of production use) on system_A and system_B. Profiler trace was run on both systems and data collected. While this produced good comparison numbers, I feel that we need more systematic approach.Can someone advise what they have used in such situations for making sure that the new system is as good or better than the old one?Thank you!
  2. satya Moderator

    As you have migrated the system to the new hardware, did you checked the execution plans for the stored procedurs that you are expecting to run in a ziffy!
    Also how about the database maintenance plan execution after the migration>
    How about the UPDATE STATISTICS?
  3. sql_er New Member

    Satya,I did not check the stored procedure execution plans yet. I was hoping to get the global view by stress testing and observing duration. There are too many stored procedures to check one by one.Database maintenance plans are working as good or even better in the new system [i.e. time wise], although it could be due to the fact that the system is not being hit by clients yet and so there is much less locking. The maintenance plans I am referring to are:- Index Defragmentation- Update Statistics- Backup jobsThank you!

Share This Page