Justin Manning MSFT PFE

The life of a SQL PFE

SQL Server Performance Testing with Database Experimentation Assistant

Introduction

This quick post is to assist users in performing performance tests on various environments and generate analytics from the results.

The Database Experimentation Assistant makes use of SQL Server Distributed Replay Controller and Clients.

Tool:

Database Experimentation Assistant (DEA)

Virtual Environment:

  • SQL Server 2017 CU6 (DEA has a known issue in replay if the SQL Instance target is below SQL Server 2017 CU 1)
    • Instance Name: SQLEXP
  • Windows Server 2016
  • Distributed Replay Client (up to 8 clients in enterprise edition)
  • Distributed Replay Controller
  • Working Folder for trace files to be collected and replayed:
    • C:\DEA
      • Source
      • Target

Configuration of DEA



Populate the required fields


Click Start to begin collection


Note Completion Status


Replay the workload against your database

Make sure your SQL Server Distributed Replay Controller and Client are in a running state


Confirm the distributed replay Controller is communicating with the client


Click on All Replay to run the collected workload


Create a new Replay


Perform a restore of the database you would like to test the workload against.

This is to ensure the database is in the original state before a workload was performed against it.


Note: Replay is busy running via distributed controller


Notice distributed replay controller status, sending work to clients


Completed Run


Click on Analysis Report to analyse workload


Connect to the instance of SQL where you would like to do analysis


Create a new Analysis Report


Populate required fields. Supply the original workload collected, as well as the target workload, which was generated by the replay


Notice databases being created for the analysis to be performed


Reports Presented once analysis is completed