Viewing Tuning Recommendations
This task uses the tuning session that you created in Tuning a Workload. After you have tuned the AdventureWorks2008R2 database by using the MyScript.sql Transact-SQL script, Database Engine Tuning Advisor displays its results on the Recommendations tab. The following task introduces the Recommendations tab of the Database Engine Tuning Advisor graphical user interface (GUI) and guides you to explore the information it provides about the tuning session results.
View tuning recommendations
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 the practice Tuning a Workload.
Double-click MySession in the Session Monitor pane. Database Engine Tuning Advisor loads the session information from your previous tuning session and displays the Recommendations tab. Note that Database Engine Tuning Advisor made no Partition Recommendations because you accepted all the tuning option defaults and No partitioning was selected on the Tuning Options tab.
On the Recommendations tab, use the scroll bar at the bottom of the tabbed page to view all of the Index Recommendations columns. Each row represents a database object (indexes or indexed views) that Database Engine Tuning Advisor recommends be dropped or created. Scroll to the right-most column and click a Definition. Database Engine Tuning Advisor displays a SQL Script Preview window where you can view the Transact-SQL script that creates or drops the database object on that row. Click Close to close the preview window.
If you are having difficulty locating a Definition that contains a link, click to clear the Show existing objects check box at the bottom of the tabbed page, which will decrease the number of rows displayed. When you clear this checkbox, Database Engine Tuning Advisor shows you only the objects for which it has generated a recommendation. Select the Show existing objects check box to view all the database objects that currently exist in the AdventureWorks2008R2 database. Use the scroll bar at the right side of the tabbed page to view all of the objects.
Right-click the grid in the Index Recommendations pane. This right-click menu enables you to select and deselect recommendations. It also enables you to change the font for the grid text.
On the Actions menu, click Save Recommendations to save all of the recommendations into one Transact-SQL script. Name the script MySessionRecommendations.sql.
Open the MySessionRecommendations.sql script in the Query Editor of SQL Server Management Studio to view it. You could apply the recommendations to the AdventureWorks2008R2 sample database by executing the script in the Query Editor, but do not do this. Close the script in Query Editor without running it.
As an alternative, you could also apply the recommendations by clicking Apply Recommendations on the Actions menu of Database Engine Tuning Advisor, but do not apply these recommendations now in this practice.
If more than one recommendation exists on the Recommendations tab, clear some of the rows that list database objects in the Index Recommendations grid.
On the Actions menu, click Evaluate Recommendations. Database Engine Tuning Advisor creates a new tuning session where you can evaluate a subset of the original recommendations from MySession.
Type EvaluateMySession for your new Session name, and click the Start Analysis button on the toolbar. You can repeat Steps 2 and 3 for this new tuning session to view its recommendations.
Summary
You have viewed the contents of the Recommendations tab for the MySession tuning session and evaluated a subset of its recommendations in the new EvaluateMySession tuning session.
Evaluating a subset of tuning recommendations may be necessary if you find you must change tuning options after you run a session. For example, if you ask Database Engine Tuning Advisor to consider indexed views when you specify tuning options for a session, but after the recommendation is generated you decide against using indexed views. You can then use the Evaluate Recommendations option on the Actions menu to have Database Engine Tuning Advisor re-evaluate the session without considering indexed views. When you use the Evaluate Recommendations option the previously generated recommendations are hypothetically applied to the current physical design to arrive at the physical design for the second tuning session.
More tuning result information can be viewed in the Reports tab, which is described in the next task of this lesson.