About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Monday, June 7, 2010

Back to the future: SQL Profiler Trace Replay

Are you familiar with Trace Replay feature SQL Profiler? It's not really popular but actually can do a lot for you.
Replay is the ability to save a trace and replay it later. This functionality lets you reproduce activity captured in a trace. When you create or edit a trace, you can save the trace to replay it later.
Replaying trace is useful when
- you need to troubleshoot production issues then you can run recorded trace against fixed application to make sure all bugs were fixed.
- you need to simulate production load on QA/dev environment for stress testing
- test new code that is written on development server by simulating real DML load.
The process of recording and replaying trace is really easy.
- Use preconfigured replay template (TSQL_Replay) because, if you do not capture all required data, SQL Server Profiler will not replay the trace (!)
- Save the trace using any format: table or trace file. If you want to use rollover files with limited size take into consideration the following:
* File name should not contain symbol '_', otherwise Profiler will not load all files when you open the files for replay
* Keep as less files as you can because I haven't found option to tell the profiler " Load all rollover files". It will ask you file after the file if you want to load next file and you will need to press ok.
- Open trace file on target server. If menu Replay items are disabled, this means that file is not completely loaded. Wait with patience.
- When you see that Replay Menu items got enabled, choose Start and here you go.
- Database matching can be done on databaseid but, in most cases, those are different and profiler will match using databasename

Make sure all logins, users , permissions etc. must be the same on source and target servers

Good luck.

Labels: , , , , , , , , , , ,


Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home