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 ” dreplayctrl41″ -i “Path to first trace file” -d “<Folder Path on Dreplay 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 “dreplayctrl41” -d “<Folder Path on Dreplay Controller>\IrfFolder” -s -o  “sql2016targetDB” -w “dreplaychild41,dreplaychild42,dreplaychild43,dreplaychild44”
    5. Stop trace capture on target SQL instance
      • <Dea_InstallPath>\Scripts\StopCaptureTrace.sql in SSMS
      • Edit  @Tracefile to match trace file path on target SQL instance
      • Run script against target SQL instance
  3. 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