Exercise - Review a trace analysis report in DEA

  • 7 minutes

You've completed the first phase of your staged upgrade of CRM databases to SQL Server 2019. Before going live, you need to check that the upgrade hasn't negatively affected database performance.

In this exercise, you'll use the Microsoft Database Experimentation Assistant (DEA) to create a trace analysis report. You'll then review this report to identify any critical issues that must be addressed before the upgraded database is deployed to a production environment.

Generate the analysis report

Start by using DEA to analyze the trace performances against Target 1 and Target 2.

  1. Select Analysis Reports on the left menu. Then in the Server name field, type localhost, and leave Authentication selection as Windows Authentication. Click Connect.
  2. Click + New analysis report. This action opens the Report information screen.
  3. In the Report name field, type AdventureWorksUpgrade, and then in the Storage location for Target 1 traces list, select Local.
  4. In the Trace for Target 1 SQL Server field, select the ..., and browse to C:\replay.
  5. Change the file type from SQL Trace Files (*.trc) to Extended Event Files (*.xel).
  6. Select the last file prefixed ReplayTarget1....
  7. In the Storage location for Target 2 traces list, select Local.
  8. In the Trace for Target 2 SQL Server field, select the ..., and browse to C:\replay.
  9. Change the file type from SQL Trace Files (*.trc) to Extended Event Files (*.xel).
  10. Select the last file prefixed ReplayTarget2....
  11. Click Start.

View the analysis report

  1. When the report is complete, it appears in the list.
  2. Click AdventureWorksUpgrade to view the report.
  3. Note the name of the Target 1 and Target 2 servers listed in the report, including the SQL Server version. View how the slices of the pie charts are categorized.
  4. Select a new value from the Threshold list, and see if this action changes the distribution of the slices.
  5. Click the slices of the pie chart to see the performance metrics for the category.
  6. Select a couple of the queries to compare the statistics between Target 1 and Target 2, then select the Query Plan Information tab to see the plans used.