August 23, 2016 at 3:02 am
Good Morning All,
I want to be able to test the typical daily activity against a test server. The problem we have here is the test environment is really only used for functional testing (does this proc compile? Does it return the right data?) -- As we don't have a team of testers, or any testers at all any test performed here are really rather basic.
MY knowledge of DRC is pretty non-existent and so I might have the wrong end of the wrong stick to begin with:
Is it possible with the Distributed Replays to create some trace files and REPLAY them in REAL time against a test database, similar to the following:
1. Run server side trace to a file for 24 hours
2. Run the resulting file against a copy of the database whilst OTHER USERS are also accessing the copy in real time.
3. Are there any problems I am missing?
Cheers All,
Alex
August 23, 2016 at 8:48 am
You've got it pretty much nailed. It will do exactly what you're expecting of it. The only trick is that you need to do a point in time restore of the database in question to the point when the trace is going to start playing so that you're not attempting to read/write stuff that may or may not be there. Other than that, it just works. It's not the easiest thing in the world to set up initially, but once the initial setup is done, it chugs along nicely.
"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
August 23, 2016 at 9:16 am
Ahh I see! Cheers Grant.
So should I:
Create a marked transaction
Start a trace for N hours
Restore last full backup up to the marked transaction
And then run in the trace file?
Can I keep the trace running and run in subsequent workloads without performing a full restore again?
One more question, the work load will be generated from about 10 application servers so do I need to rerun the workload from 10 DRC's or can I get away with just a couple as long as they perform okay (they should as the app servers are massively under utilised as it is <1% CPU and very little memory usage.
Cheers
Alex
August 23, 2016 at 9:26 am
alex.sqldba (8/23/2016)
Restore last full backup up to the marked transaction
Full backups can't be restored to times or marks, they are always restored to the consistent point at the end of the backup period. Only tranaction log backups can be restored to times or marks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 9:31 am
Aye, sorry, I just meant restore up to the marked transaction, so be that a full+diff+logs -- but my typing was being lazy.
August 23, 2016 at 10:14 am
You can get by with one or two DRCs. It depends on how much you're attempting to simulate precise load and how much you just want "a" load.
I would not just keep the trace running. I'd capture a reasonable set of data (4-8 hours during a busy period) and then plan for resets during testing. You'll probably find, unless you go for a timed replay, that your reruns of the capture frequently will be faster than the capture time. Meaning, you capture 4 hours of events, but it only takes 2-3 hours to replay them because of a lack of wait time. It's a dance to get things just right. Usually, I'm just happy to have a load in place so that I've at least got production emulation if not simulation.
"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
August 25, 2016 at 1:36 am
Thanks again, Grant.
I've not got around to testing it yet, but just had a thought - will this still work with prepared statements? The application is prepared statement heavy.
Cheers
Alex
August 25, 2016 at 3:33 am
Yeah, it should. Just use the published trace that's in the documentation.
"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
August 25, 2016 at 5:16 am
Yes. It works fine with ad-hoc, prepared or stored procedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply