The Database Experimentation Assistant (DEA) is a new A/B testing solution for changes in SQL Server environments (e.g. upgrade, new indexes, etc.). It assists in evaluating how the workload on your source server (current environment) will perform against your new environment. It guides you through performing an A/B test through three steps: capture, replay, and analysis. This post walks you through the steps.
First step of SQL Server A/B testing is capturing a trace on your source server (usually production server). Trace files capture the entire query workload on that server as well as their timestamps. Later, this trace will be replayed on your target servers to be used for analysis; the analysis report will give you insights on the difference in performance of the workload between your two target servers.
- Before starting your trace capture, please make sure to backup the databases you are capturing trace from.
- DEA user should be configured to connect to the database using Windows authentication.
- SQL Server service account should have access to the source trace file path.
Step 1: In DEA, go to All Captures by clicking on the camera icon on the left navigation.
Step 2: Provide necessary inputs to begin capture.
- Trace name: the file name for the new trace file you are creating. Avoid using a trace name with the rollover file naming convention; for example, 'CaptureName_NNN'.
- Duration: duration for the capture.
- SQL Server instance name: server instance you would like to capture trace from.
- Database name: name of the database on the SQL Server to capture trace from. If left blank, trace will be captured from all databases on the server.
- Path to store source trace file on SQL Server machine: folder path where you would like to save the trace file.
Please note that DEA user should be configured to connect to the database using Windows authentication, and that the SQL Server service account should have access to the source trace file path.
Once you have provided all the necessary inputs, please double check that you have taken a backup of the target database(s) and check the checkbox, then press Start to start capture.
You will then see the progress of your capture, including start time, duration, and remaining time. You can start a new capture while waiting for this capture to finish. Once your capture is completed, you can use the output trace file to start the second phase: replaying the trace file on your target servers.
Check out this FAQ page for commonly asked questions for Capture.
Second step of SQL Server A/B testing is to replay the trace file captured in the first step to your target servers and collect extensive traces from the replays for analysis. You will replay on two target servers: one mimicking your source server (Target 1), and one mimicking your proposed change (Target 2). Target 1 and Target 2 should be as similar as possible in hardware configurations so that we can accurately analyze the performance impact of your proposed changes in SQL Server.
- To perform replay, your machines must be set up to run Distributed Replay (DReplay) traces on. Read the blog on how to set up Distributed Replay controller and clients.
- Make sure to restore the database(s) on your target servers using the backup from the source server.
- Query caching in SQL may affect evaluation results. Restarting the SQL Server service (MSSQLSERVER) in the services application will improve consistency in evaluation results and is recommended.
Step 1: In DEA, go to All Replays by clicking on the play icon on the left navigation. Similar to the All Captures page, you will see the list of past replays you have performed during the session, if any. To start a new replay, click on “New Replay”.
Step 2: provide all necessary inputs:
- Replay name: the file name for the replay trace.
- Controller machine name: name of the distributed replay controller machine.
- Path to source trace file on controller: file path for source trace file from .
- SQL Server instance name: target SQL Server to replay source trace on.
- Path to store target trace file on SQL Server machine: folder path for the resulting replay trace file.
After providing all necessary inputs, double check that you have restored the backup from the first step, check the checkbox to indicate this, and press Start to start replay. Much like New Capture, you will be able to see the status of your replay. After replaying the source trace on both of your target servers, are ready to generate analysis report.
Check out this FAQ page for commonly asked questions for Replay.
The final step is to generate an analysis report using the replay traces to gain insights on performance implications of the proposed change.
- To generate analysis reports, you need If you are missing one or more of these components, a prerequisites page with links for download will show up when you try to generate a new report (internet connection required).
- To view a report generated in the previous version of the tool, you need to update the schema first.
Step 1: Go to Analysis Reports on the left navigation. Connect to the SQL Server where you will store your report databases. You will see the list of all reports in the server. To create a new report, click on New Report.
Step 2: provide necessary inputs to generate report:
- Report name: name of the analysis report to be created.
- Trace for Target 1 SQL Server: path for the trace file from replaying on Target 1.
- Trace for Target 2 SQL Server: path for the trace file from replaying on Target 2.
After providing these inputs, click Start to generate report. You will see your newly generated report on top of the list, and the icon next to it will turn to a green checkmark when it is completed; now you can view the analysis report to gain insights provided by your A/B test.
Check out this FAQ page for commonly asked questions for Analysis.
On the first page of your report, you will see the version and build information for the target servers on which the experiment was run. . Threshold allows you to adjust the sensitivity or tolerance of your A/B Test analysis. By default, it is set at 5%; any improvement in performance that is >= 5% is categorized as ‘Improved’. The dropdown allows you to evaluate the report with different performance thresholds.
The two pie charts demonstrate the performance implications of the difference between the two target servers for your workload; the left one is based on execution count, the right one is based on distinct queries. There are five possible categories:
- Improved: statistically, the query performed better on Target 2 than on Target 1.
- Degraded: statistically, the query performed worse on Target 2 than on Target 1.
- Same: there is no statistical difference for the query between Target 1 and Target 2.
- Cannot Evaluate: the sample size for the query is too small for statistical analysis; in order for A/B testing analysis DEA requires the same queries to have at least 30 executions on each target.
- Error: the query errored out at least once on one of the targets.
Clicking on a slice lets you drill down into a particular category and get performance metrics, even Cannot Evaluate.
On the drill-down page for a performance change category, you will see a list of queries in that category. On the Error page, you will see three different tabs:
- New Errors: errors which appeared on Target 2 but not on Target 1
- Existing Errors: errors which appeared on both Target 1 and Target 2
- Resolved Errors: errors which appeared on Target 1 but not on Target 2
Clicking on a query takes you to a Comparison Summary page for that query.
On the Comparison Summary page, you can find a summary statistics for that query, such as the number of executions, mean duration, mean CPU, mean reads/writes, and error count.
If the query is an error query, the Error Information tab shows more information about the error. On the Query Plan Information tab, you can find information about the query plans used for the query on Target 1 and Target 2.
On any page of the analysis report, you can click on the Print button on top right to print what is visible on the UI at that moment.
We would love to hear from you! We welcome all feedback/comments/questions. To provide feedback, click on the smiley on the lower left corner of the application. A feedback pane will open on the right. There, you can rate the application (how likely are you to recommend DEA?), leave us comments, and if you wish to be contacted regarding your feedback by the product team, leave your email address.
Check out this post to perform an A/B test for your SQL Server through command-line.
DEA can be used in an iterative manner, and is useful for evaluating and A/B testing on the same software platform as well. For instance, you can evaluate structural changes in your DB environment (e.g. schema changes, index changes) through DEA, even if it doesn’t involve any version change.
If your goal for using DEA was to evaluate a SQL Server upgrade scenario, using DEA along with the Data Migration Assistant (DMA) may provide additional insights and help you in your migration.
Download DEA here.