SQL Server Performance Testing with Database Experimentation Assistant
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.
- 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:
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
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