Database Experimentation Assistant – Command Line


#1 and #2 below are manual steps to capture and replay workloads. DEA command line support will be available in future releases. #3 for trace analysis is supported by DEA command line.

  1. Start a new workload capture
    1. Open <Dea_InstallPath>\Scripts\StartCaptureTrace.sql in SSMS
    2. Edit the following variables in the file
      • @durationInMins – time in minutes for workload capture
      • @maxfilesize – Max file size per trace file.Recommended size 200
      • @Tracefile – unique name for your trace file
      • @dbname – By default workload on entire server is captured. Specify database name if workload needs to be captured on a specific database only.
    3. Run it against the SQL instance where workload needs to be captured. Workload is captured for time specified in @durationInMins
  2. Replay workload
    1. Log in to Dreplay Controller machine
    2. Convert workload trace captured in step 1 to an IRF file
      DReplay preprocess -m "dreplaycontroller" -i "Path to first trace file" -d "<Folder path on controller>\IrfFolder"
    3. Start trace capture on target SQL instance using “StartReplayCaptureTrace.sql” .
      1. Open <Dea_InstallPath>\Scripts\StartReplayCaptureTrace.sql in SSMS
      2. Set @durationInMins=0 So that trace capture does not stop automatically after specified time.
      3. Set @maxfilesize – Max file size per trace file. Recommended size 200
      4. @Tracefile – unique name for your trace file
      5. @dbname – By default workload on entire server is captured. Specify database name if workload needs to be captured on a specific database only.
    4. Replay IRF file against target SQL instance
      DReplay replay -m "dreplaycontroller" -d "<Folder Path on Dreplay Controller>\IrfFolder" -o -s "SQL2016Target" -w "dreplaychild1,dreplaychild2,dreplaycild3,dreplaychild4"

      1. To monitor status, open a command prompt and run DReplay status -f 1
      2. To stop the replay, in case you see there is an issue (for example, pass % is lower than expected), open a command prompt and run DReplay cancel
    5. Stop trace capture on target SQL instance
    6. <Dea_InstallPath>\Scripts\StopCaptureTrace.sql in SSMS
    7. Edit @Tracefile to match trace file path on target SQL instance
    8. Run script against target SQL instance
  • Analyze traces (using DEA command)
    1. Deacmd.exe -o analysis -a <tracea filepath> -b <traceb filepath> -r reportname -s <SQL server instance name>
    2. Example: Deacmd.exe -o analysis -a C:\Trace\SQL2008Source\Trace.trc -b C:\ Trace\SQL2014Trace\Trace.trc -r upgrade20082014 -s localhost
    Comments (1)

    1. StartCaptureTrace.sql can be edited to allow parallel traces for other dbs. Edited Lines 26-33.

      –Raise a sql error if any existing traces are still running in the server
      declare @path nvarchar(300)
      set @path =’%DEAExperimentation_%’ + @dbname +’%’
      if exists( select 1 from sys.traces with (nolock) where path like @path and [Status] = 1)
      begin
      RAISERROR(‘50000’,16,-1)
      goto finish
      end

    Skip to main content