Tuning a Workload
The Database Engine Tuning Advisor can be used to find the best physical database design for query performance on the databases and tables that you select for tuning.
This task uses the AdventureWorks2012 sample database. To enhance security, the sample databases are not installed by default. To install the sample databases, see Installing SQL Server Samples and Sample Databases.
Tune a workload Transact-SQL script file
Copy a sample SELECT statement or statements from "A. Using SELECT to retrieve rows and columns" in SELECT Examples (Transact-SQL) and paste the statements into the Query Editor of SQL Server Management Studio. Save the file as **MyScript.sql **in a directory where you can easily find it.
Start Database Engine Tuning Advisor. See Launching Database Engine Tuning Advisor.
In the right pane of the Database Engine Tuning Advisor GUI, type MySession in Session name.
Select File for your Workload, and click the Browse for a workload file button to locate the MyScript.sql file that you saved in Step 1.
Select AdventureWorks2012 in the Database for workload analysis list, select AdventureWorks2012 in the Select databases and tables to tune grid, and leave Save tuning log selected. Database for workload analysis specifies the first database to which Database Engine Tuning Advisor connects when tuning a workload. After tuning begins, Database Engine Tuning Advisor connects to the databases specified by the USE DATABASE statements contained in the workload.
Click the Tuning Options tab. You will not set any tuning options for this practice, but take a moment to review the default tuning options. Press F1 to view the Help for this tabbed page. Click Advanced Options to view additional tuning options. Click Help in the Advanced Tuning Options dialog box for information about the tuning options that are displayed there. Click Cancel to close the Advanced Tuning Options dialog box, leaving the default options selected.
Click the Start Analysis button on the toolbar. While Database Engine Tuning Advisor is analyzing the workload, you can monitor the status on the Progress tab. When tuning is complete, the Recommendations tab is displayed.
If you receive an error about the tuning stop date and time, check the Stop at time on the main Tuning Options tab. Make sure the Stop at date and time are greater than the current date and time, and if necessary, change them.
After the analysis completes, save your recommendation as a Transact-SQL script by clicking Save Recommendations on the Actions menu. In the Save As dialog box, navigate to the directory where you want to save the recommendations script, and type the file name MyRecommendations.
Summary
You have completed tuning a simple SELECT statement workload on the AdventureWorks2012 database. The Database Engine Tuning Advisor can also take SQL Server Profiler trace files and tables as tuning workloads. The next task shows you how to view and interpret the tuning recommendations that you received as a result of the practice tuning.