replay transactions

Question

Post reply

replay transactions

Brookstone

SSCommitted

Points: 1745
More actions
August 4, 2010 at 9:09 am

#222116

Hello,
We are migrating a server from SQL2000 to SQL2008 (side by side). The new server is SAN attached and has copies of all the databases from the SQL2000 machine. We want to run a test on the SQL2008 machine that approximates the workload on the SQL2000 box. There are 50 databases in use. What is the best way to capture and replay data in this scenario?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 1

SQL Profiler should be a good place to start. Go with multiple threads to match your workload a bit.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

Yeah, I'd go with Profiler as well. The only issue you might run into with that approach is if you have ad hoc SQL that is using deprecated functionality, like ANSI 89 joins or something. Those queries would fail.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brookstone SSCommitted Points: 1745 More actions · Answer 3

Thanks for the replies.

I have used profiler to capture data in the past, but have not used it to replay transactions. Quick question: If I capture transactons over say a 3-hour period, can I configure profiler to replay them in order over a 3-hour period? I would like to replay them at real-time speed.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Yes and no. It kind of depends on how big the machine doing the playback is since it will need to thread the calls and may not be able to precisely duplicate your system if you have say, 2000 clients hitting the machine at the same time or something. But, yes, by and large, you'll get near real time playback, in order. Be sure that you have a good database backup and log backups so that you can recover to a precise point in time to ensure that you're starting the playback in the same state that you started the recording.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 5

I think what you asking for is something that I don't think profiler does what you are looking for...

I think you are looking for this.

That the SQL get replayed with the built in pauses that are in between statements.

So if you have three statements in your trace

Statement Time started Time Completed

select * from sysobjects 13:13:00 13:13:00

select * from mybigtable 13:50:00 13:55:03

select * from mysmalltable 14:45:00 14:45:01

You want the statements to run with pauses between each, not as fast as profiler could run them.

If that is the case, I don't think profiler will do that. It will run one statement after another as fast as it can, it will not pause.

So it is possible you could complete 2 hours of workload in about 20 seconds.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Yeah, true, it doesn't put in pauses. But I don't think that's the worry in this case, unless I'm wrong. Wouldn't be the first time... this hour.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brookstone SSCommitted Points: 1745 More actions · Answer 7

Your assessment is correct. We want to run the work load as it occurred in production with the pauses.

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 8

You might want to look at some third party stuff then. The other option would be to do a trace, edit the SQL and add waits and then replay that single threaded but that might a lot more hassle than it's worth.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

If you want to literally simulate the entire user experience, with waits and everything, I'd suggest taking a look at Quest's Benchmark Factory. We've been able to use it that way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brookstone SSCommitted Points: 1745 More actions · Answer 10

Thanks for all the replies and advice. Much appreciated. I'll take a look at Quest's tool.