(Reviewed by: Ajay Jagannathan, Mark Jones, Mohamed Kabiruddin, Neetu Singh, Sreraman Narasimhan, Venkata Raj Pochiraju)
Database Experimentation Assistant (DEA) is a powerful tool used for comparing performance of existing SQL Server workload against a specific target version. Typically, DEA can be used in upgrade and migration scenarios when you intend to upgrade from older version of SQL Server to the latest version or migrating to Azure SQL. DEA does comparison of workloads at query performance level and thus provides greater confidence when performing upgrade or migration. To install DEA, download the latest version of the tool.
As customers evaluate to move SQL Server workloads to Azure and look for Platform as a Service (PaaS) capability they can choose either Azure SQL Database Managed Instance (MI) that provides instance level features and near 100% compatibility with on premise SQL Server or Azure SQL Database (DB) suited for modern cloud applications. Before embarking on this route, it is crucial to comprehend how existing SQL Server workload would perform on MI or DB and DEA tool can be leveraged to get deeper level of understanding. This article lists the sequence of steps to follow when evaluating MI or DB as a target.
Concepts and terminology used in DEA are explained in detail in the DEA documentation page and is outside the scope of this article. At a high-level following steps are involved when evaluating SQL Server workload using DEA.
1. Capture workload on source SQL Server
2. Replay workload on Target 1 that mimics source SQL Server
3. Replay workload on Target 2 (MI or DB)
4. Analyze Target 1 with Target 2
Source SQL Server would be the current production instance that’s being considered for migrating to MI or DB. To evaluate workload against MI or DB, we need to capture existing workload at source in format instead of traditional SQL Server profiler . Ensure that the user running DEA can connect to source SQL Server and has SYSADMIN privileges.
Figure 1 shows a new capture (Open DEA | Click Capture Traces from the left side menu | Click on + New Capture) and annotations for key options
Figure 1: New Capture
Once capture has started and the duration has elapsed, DEA would show the confirmation for completed capture. Next step is to replay the captured traces on Target 1 and Target 2.
Figure 2: Completed Capture
In this step, the captured traces have to be replayed against Target 1, a SQL Server instance that mimics source SQL Server both in terms of system resources like CPU, Memory, Disk configuration and SQL Server version. The step is relevant is to avoid impacting production SQL Server instance and perform the experimentation against a separate instance.
Figure 3 shows a new Replay (Open DEA | Click Replay Traces from the left side menu | Click on + New Replay) and annotations for key options
Figure 3: Replay against Target 1
Once replay finishes, DEA would show the confirmation for completed replay.
Figure 4: Completed replay against Target 1
Inbuilt replay tool uses ReadTrace.exe and OStress.exe under the hood to pre process and replay respectively. Readtrace and OStress are robust and popular tools included in Replay Markup Language (RML) utilities for SQL Server. OStress can be used in stress or replay mode and DEA uses it in replay mode. Providing detailed overview of ReadTrace and OStress is outside the scope of this article and we would recommend referring the help file included in RML installation.
To better understand what happens behind the scenes with Inbuilt replay tool, there are log files available in “PreProcessOutput” folder that gets created under the folder containing source extended event files (#3 in Figure 3).
As mentioned, replaying extended event traces involves two steps
Step 1: Pre process
ReadTrace.exe is used to pre process extended event files and generate bunch of .rml files. Each RML file corresponds to one SPID in the captured workload. As shown below, ReadTrace.log file contains the command line parameters used by ReadTrace.exe and the verbose log.
Step 2: Replay
OStress.exe is used to replay the *.rml files against target SQL Server and it generates *.out files for each corresponding rml file.
As shown below ostress.log file contains the command line parameters used by OStress.exe and the verbose log. The command line parameter includes the configuration file (-c parameter) used by OStress and the default value is
-cC:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\ReplayConfig.ini
ReplayConfig.ini file contains all the options related to connection, query & replay used by OStress.
Next step is to replay the captured traces on Target 2.
In this step, the captured traces from source must be replayed against Target 2, an Azure SQL Managed Instance (MI) or Azure SQL Database (DB ) that has the number of vCores and amount of memory similar to source SQL Server. For the purpose of this article we choose MI but the steps equally apply to DB as well.
Figure 5 shows the Replay (Open DEA | Click Replay Traces from the left side menu | Click on + New Replay) against MI and annotations for key options
Figure 5: Replay against Target 2
Once replay finishes, DEA would show the confirmation for completed replay.
Figure 6: Completed replay against Target 2
You will find the extended files generated by replay in Azure Storage account container. These files should be downloaded for the final analysis step.
Figure 7: Extended event files generated by replay
Final step is to analyze the extended event files generated by replay on Target 1 and Target 2 so that we can compare how SQL Server workload performed.
To create analysis report DEA needs to connect to a SQL Server instance for storing analysis results and this can be a separate instance, or you can use the Target 1 instance. For the purpose of this article we are using the SQL Server instance in Target 1 as the files generated by replay on Target 1 already exist and we downloaded the files generated by replay on Target 2 from Azure storage.
Figure 8 shows New Analysis Report (Open DEA | Click Analyze Traces from the left side menu | Connect to SQL Server instance | Click on + New Report) and annotations for key options
Figure 8: New Analysis Report
Once the report is generated you can review the report to better understand how SQL Server workload performed against the desired target (Target 2) in comparison to the existing environment (Target 1).
Here are some best practices to follow while performing workload comparison using DEA:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.