Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-11-23 : 18:33:41
|
| 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! |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-24 : 04:28:59
|
| I think Profiler Replay will do. There may be other tools available but SQL 2000 is so long in the tooth good luck with that one!I've never really got profiler Replay to work well - interaction between concurrent tasks has meant that we have got errors etc. during playback. But we have been able to stress our systems with it and get some sort of indication.We never worried about thinking time, just used the Playback as an indication. Playback will be faster than real-life (can't remember if you can influence that, or not - is there an option to play back at original/capture time-interval??), so we only looked at total elapsed time.The other key problem we had was getting a clean starting point.You start your Profiler Capture running at 10:01 ... well, somewhere near that but you don't have an Exact Time AFAICR ...You restore your TEST database to 10:01 ... but its a few milliseconds, maybe more, different to when Profiler started ...Playback may fail because not all rows are identical at the start.If you stop the live database, take a backup, start profiler capture and THEN let people in you will be better off - but that's unrealistic in real world.(I won't be surprised to hear that you cannot create a Profiler Capture on TEST database WHILST there is a Playback going on - but if that is the case not sure how you are going to get a "Sproc Duration Statistics" |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-11-24 : 13:42:25
|
| Thanks for the info Kristen.I'll look into Profiler Replay more.I also saw this tool available, which as trial version, which I'm thinking to give a try: http://www.quest.com/benchmark-factory/Are there any other tool or any other suggestions from people who stress tested MSSQL 2000 before?Thank you! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-24 : 14:36:12
|
| Too long ago for me to remember what we considered at the time, sorry. |
|
|
|
|
|
|
|
|
Stress testing new system