Database Engine Tuning Advisor (DETA) allows you to optimize your SQL Server database configuration based upon the unique business requirements of your organization. It analyzes your database workload and uses that information to make recommendations about hte physical structure of your database and then allows you to actually implement the recommendations within DETA.
Difficulty: Average
Time Required: One hour
Here's How:
- Open SQL Server Management Studio (SSMS).
- From the Tools menu, select Database Engine Tuning Advisor. DETA will open and present a connection window.
- Provide the connection details for the SQL Server database you wish to tune and click the Connect button. You'll see a tuning setup window.
- Select the File radio button in the Workload section of the window and browse to the file you wish to use for your workload. This file may be either a SQL Trace, XML file or SQL file. It's essential to choose a workload representative of normal activity for your database, as DETA will optimize the database's performance based upon this information.
- Select the checkbox(es) corresponding to the database(s) you wish to tune.
- Click Start Analysis. After DETA completes (which may take several minutes or longer), you'll be presented with a set of recommendations, similar to those shown in the figure.
- Review the recommendations and select those you wish to implement by clicking the checkbox next to each.
- From the Actions menu, select Apply Recommendations.
- Click the OK button to apply the reocmmendations immediately.
- Review the results of your tuning in the status window and click the Close button when you are finished.
Tips:
- Selecting a workload file that is representative of normal database activity is absolutely critical to the success of DETA.
- You may also script the recommendations as a SQL file if you wish to run them at a later time without rerunning DETA.
What You Need
- Workload file
