Database Experimentation Assistant – Command Line


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

  1. Start a new workload capture (using DEA command)
    1. Deacmd.exe -o startcapturetrace -s <SQLServerInstance> -e <encryptconnection> -u <trustservercertificate> -d <database name> -p <trace file path> -f <trace file name> -t <Max duration>
    2. Example: Deacmd.exe -o startcapturetrace -s localhost -e -d adventureworks -p c:\test -f sql2008capture -t 60
  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 <Target1 trace filepath> -b <Target2 trace filepath> -r reportname -s <SQLserverInstance> -e <encryptconnection> -u <trustservercertificate>
    2. Example: Deacmd.exe -o analysis -a C:\Trace\SQL2008Source\Trace.trc -b C:\ Trace\SQL2014Trace\Trace.trc -r upgrade20082014 -s localhost -e
    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