SQL Server Performance Testing with Database Experimentation Assistant

  • Article

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