How to tune ISV apps – Capture the workload

This is the first article of my blog post series How to tune ISV apps
In today’s blog post i will describe the steps that you have to do in order to capture a typical workload during business hours.

Importance of the baseline.

Why is it important to capture a workload?
Because you need something that you can compare your improvements against. If you have no baseline, you cannot guarantee that the improvements you have performed against your database are real improvements or just drawbacks.

The tools you need to capture the workload are:

  • SQL Server Profiler
  • SQL Server Management Studio

Don’t worry – SQL Server Profiler will not be used to capture the workload on the production server, it is just the tool to create the right “setup”.

Create the Tracefile

Start SQL Server Profiler, whether from the production server or another server does not matter, because we will not bring the production server to it’s knees 😉
Choose “File -> New Trace…”

Connect to your SQL Server instance using the credentials that you have. (You need at least the “ALTER TRACE” permission for you SQL Server Login.)

In the next wizard the only important thing is the template that you choose, this has to be “TSQL_Replay”. With that template you are able to really replay one and the same workload against many different versions of our optimized database. Everytime you do that, you can check wether your changes have been good ones or bad ones.

Just click “Run”. The grid with the incoming requests appears.
Click immediately on the red square button “Stop” in order to stop the trace.

Now you have to export this new “Script Trace Definition” to a T-SQL file. Just save it anywhere, you can later copy’n’paste it to your production server, if you did not already start the SQL Server Profiler on that very server.

Alter the Trace Definition File

Now you have to open the file and make some settings and tweak it a little bit.

  • Set an appropriate file size. Once you run this Trace Definition File in order to create the real trace files on file system, the trace will generate a new file each time it hits the threshold defined at the parameter “@maxfilesize”. The default for this value is 5 what means 5 MB. Personally i set it to 50 MB:
    set @maxfilesize = 50
  • Specify the location where the trace files should be written.
    !Important!: Please make sure that the amount of disk space you need is in place, because the amount of data you collect depends on the workload that you really have got on the production server and also on the duration that you want to capture.
    I captured a workload on a OLTP Helpdesk Trouble Ticket System between 1:30pm and 15:00pm and collected 4 GB of data.
    So please edit the following line, so that it fits your needs.
    You have to change two things in this line:

    • Change the value of the second parameter from “0” to “2” in order to activate the rollover option.
      Otherwise you will find the following error in SQL Server Errorlog:
      Trace ID ‘2’ was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem.

    • At the third parameter specify a proper path and filename.
    exec @rc = sp_trace_create @TraceID output, 2, N'D:\CapturedTraces\Trace', @maxfilesize, NULL, 200

    As mentioned in the notes above the line, the “.trc” extension will be appended to the filename automatically.

  • At the end of the file you will find a line with
    exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - d7cd774c-ec84-488e-afab-2211df8a6ead'

    If the SQL Server Instance you want to trace, hosts more than one database, you have to apply another filter like this, where you have to replace “YourDatabaseNameHere”:

    exec sp_trace_setfilter @TraceID, 35, 0, 6, N'YourDatabaseNameHere'
  • Now back to the very top of the file you have to add TSQL code to backup you database before executing the capturing trace like this:
    BACKUP DATABASE [YourDatabaseNameHere]
    TO DISK = N'E:\MSSQL10_50.MSSQLSERVER\MSSQL\YourDatabaseNameHere_4_Replay.bak'
    	, NOINIT
    	, NAME = N'YourDatabaseNameHere-Full Database Backup'
    	, SKIP
    	, STATS = 1

    Once again, just replace “YourDatabaseNameHere” with your real database name.
    !Important!: Please make sure that you have set the “COPY_ONLY” option in order to leave the backup chain unbroken. If your version of SQL Server supports backup compression, please also add “COMPRESSION”.

    Execute the Trace

    Once you have finished tweaking your “Trace Definition File”, open your SQL Server Management Studio, connect to your SQL Server instance, open a “New Query” and paste the code or just open the Trace Definition File.

    Now: execute the script, pressing “F5” or clicking “! Execute”.

    In the result grid, you will see
    This is the ID of your very trace that you started. Keep this ID in mind or elsewhere for later use.

    If you have a look at the file system, you will see the created file(s) in your specified folder. Once the 50 MB have been reached, SQL Server rolls over to the next file, named YourFileNameHere_1.trc

    Stop the Trace

    After your defined period of time that you wanted to capture, you have to stop the SQL Trace. Here comes the ID of the trace into play as you have to pass it to the stored procedure that you execute as the first parameter.

    exec sp_trace_setstatus 2, 0

    After that, just delete the Trace so that there are no more processes living in SQL Server.

    exec sp_trace_setstatus 2, 2

    Save the data

    Now that you successfully captured the workload, just move the backup and all of your trace files away from your production server to a location that is safe.


    In today’s blog post you have learned how to make use of SQL Server Profiler and SQL Server Management Studio to capture a workload on your production server in order to be able to replay it whenever you want against a specified backup that you made beforehand.

    I hope you enjoyed reading this blog post and will return for the next one when i will cover the topic “Diagnosis and Doing with “sp_BlitzIndex™””


This entry was posted in SQL Server, Uncategorized and tagged , , , , . Bookmark the permalink.

One Response to How to tune ISV apps – Capture the workload

  1. Pingback: Blog Post Series „How to tune ISV apps“ | cms4j

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s