• Article

Using Session Monitor to Evaluate Tuning Recommendations

Database Engine Tuning Advisor creates a new tuning session each time that you start to analyze the effects of a workload on your database or databases. You can use the Session Monitor pane of the Database Engine Tuning Advisor graphical user interface (GUI) to view or reload all tuning sessions that have run on a given instance of Microsoft SQL Server. When all the existing tuning sessions are available to review, it is easy to clone sessions based on existing ones, or to evaluate tuning recommendations for existing sessions.

Note

Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. Implement an appropriate backup strategy for the msdb database to avoid the risk of losing tuning session data.

How Sessions Work in Database Engine Tuning Advisor

Before analysis can begin, Database Engine Tuning Advisor prompts you to create a unique name or number to identity the tuning session. In addition to the user-specified session name, Database Engine Tuning Advisor also generates a numeric identifier called the ID, which is displayed in the lower half of the Session Monitor pane. After analyzing a workload, or when analysis is stopped, Database Engine Tuning Advisor saves the tuning session information and associates it with the ID you supplied before tuning began. Information about the following items is saved and associated with the session ID for each tuning session:

General session information (creation time, start time, status, system-generated ID)

  • Workload used

  • Tuning options specified

  • Tuning log (if the workload contains events that cannot be tuned)

  • Tuning recommendations

  • Tuning summary

  • Tuning reports

Review, Clone, and Evaluate Tuning Recommendations

Using the Session Monitor pane, you can view a specific session by double-clicking the session name. You can view all tuning sessions that have run on a given instance of SQL Server simply by opening the Database Engine Tuning Advisor GUI. The capability to recall previous tuning sessions lets you create new sessions that are based on existing ones. You also can compare multiple sessions to determine which tuning options produced the best recommendations for your purposes.