Viewing Tuning Reports
In the previous practice of this lesson, you viewed the Transact-SQL scripts that create or drop database objects in the Database Engine Tuning Advisor recommendations that were generated as a result of the MySession tuning session. The MySession tuning session was created in Tuning a Workload.
Although it is very useful to view the scripts that can be used to implement the tuning results, Database Engine Tuning Advisor also provides many useful reports that you can view. These reports provide information about the existing physical design structures in the database you are tuning, and about the recommended structures. The tuning reports can be viewed by clicking the Reports tab as described in the following practice. This practice uses the MySession and the EvaluateMySession tuning sessions that you created in Tuning a Workload and in Viewing Tuning Recommendations.
View tuning reports
Start Database Engine Tuning Advisor. See Launching Database Engine Tuning Advisor. Make sure that you connect to the same SQL Server instance that you used in previous practices of this lesson.
Double-click MySession in the Session Monitor pane. Database Engine Tuning Advisor loads the session information from this session.
Click the Reports tab.
In the Tuning Summary pane, you can view information about this tuning session. Use the scroll bar to view all of the pane contents. Note the Expected percentage improvement and the Space used by recommendation. It is possible to limit the space used by the recommendation when you set the tuning options. On the Tuning Options tab, select Advanced Options. Check Define max. space for recommendtaions and specify in megabytes the maximum space a recommendation configuration can use. Use the Back button in your help browser to return to this tutorial.
In the Tuning Reports pane, click Statement cost report in the Select report list. If you need more space to view the report, drag the Session Monitor pane border to the left. Each Transact-SQL statement that executes against a table in your database has a performance cost associated with it. This performance cost can be reduced by creating effective indexes on frequently accessed columns in a table. This report shows the estimated percentage improvement between the original cost of executing a statement in the workload and the cost if the tuning recommendation is implemented. Note that the amount of information contained in the report is based on the length and complexity of the workload.
Right-click the Statement cost report pane in the grid area, and click Export to File. Save the report as MyReport. An .xml extension is automatically appended to the file name. You can open MyReport.xml in your favorite XML editor or in SQL Server Management Studio to view the report contents.
Return to the Reports tab of Database Engine Tuning Advisor, and right-click the Statement cost report again. Review the other options that are available. Note that you can change the font for the report you are viewing. Changing the font here also changes it on the other tabbed pages.
Click other reports in the Select report list to familiarize yourself with them.
Summary
You have now explored the Reports tab of the Database Engine Tuning Advisor GUI for the MySession tuning session. You can use these same steps to explore the reports that were generated for the EvaluateMySession tuning session. Double-click EvaluateMySession in the Session Monitor pane to begin.