There’s a very handy new tool from Microsoft called the Database Experimentation Assistant (DEA). The DEA makes it very easy to conduct A/B testing for SQL Server upgrades, providing quantitative insight into the performance differences across two versions of a database workload on SQL Server.
In days gone by, this was a time-consuming and difficult process that had to be done manually. And, with every roll-out of a major upgrade to either our production applications or the vendor DBMS, the process was required to ensure that no major regressions might surprise us with a significant issue.
Working With the DEA
The new DEA makes that whole process significantly easier. The idea is to evaluate a targeted version of SQL Server for a given workload, say a SQL Server 2016 Enterprise Edition instance, against a version of SQL Server where the application is currently running—from SQL Server 2005 upward.
The DEA encompasses three main components:
- An automated script, which enables the capture and replay of a SQL Server production workload (leveraging the existing Distributed Replay Utility and SQL Server tracing features)
- A statistical analysis model to analyze the traces collected from the A and B systems-under-test (SUT)
- A data visualization report to provide a rich user experience
Following the DEA wizard takes you through three steps: collecting the workload trace, replaying the workload trace, and analyzing the Workload Comparison Report.
Using the DEA reveals information such as breaking changes, compatibility errors, degraded queries, backslidden query plans, and other workload comparisons to provide solid evidence that a successful upgrade is guaranteed. The Workload Comparison Report uncovers information such as the number and percentage of queries that ranked as Degraded, Errors, Improvements, Indeterminate (in which a change in performance couldn’t be determined), and Same. You can drill down on each query to get more information about its specific performance in the test.
Your source versions may be SQL Server 2005 and upward, while the target versions may be SQL Server 2012 and upward. Microsoft mentions that overhead on the source (production) instance may be as high as 1.5% CPU, which is quite an easy amount of overhead for most systems to bear.
I have noticed that the DEA only collects the minimal amount of performance data sufficient to determine whether and how much queries performance has changed between the source and target. Personally, I would recommend that you also run PerfMon or another high-quality SQL Server monitoring tool to collect a full complement of performance metrics for deeper analysis, if needed.
Download and Explore the DEA
When using the DEA, there’s a nice wizard to make the whole process simple and easy. There’s also a very effective command-line interface and easy scripting, if you prefer a more automated approach.
To get a demo of how the DEA works, I recommend watching the video about the product on Channel9.
You can download the DEA from the Microsoft Download Center.
The DEA FAQ, also provides a lot of best practices and useful information, such as how much disk space you might expect to use, ideal trace size, and other tips.