Database engine tuning advisor


Database Engine Tuning Advisor enables you to tune databases, manage tuning sessions, and view tuning recommendations. Users with advanced knowledge of physical design structures can use this tool to perform exploratory database tuning analysis. Database tuning novices can also use the tool to find the best configuration of physical design structures for the workloads they tune.

Launch first.

  1. On the Windows Start menu, point to All Programs, point to Microsoft SQL Server, point to Performance Tools, and then click Database Engine Tuning Advisor.

  2. In the Connect to Server dialog box, verify the default settings, and then click Connect.

clip_image001

Tune a workload Transact-SQL script file
  1. Copy a sample SELECT statement or statements.Save the file as MyScript.sql in a directory where you can easily find it.

  2. Start Database Engine Tuning Advisor.

  3. In the right pane of the Database Engine Tuning Advisor GUI, type MySession in Session name.

  4. 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.

  5. Select AdventureWorks2008R2 in the Database for workload analysis list, select AdventureWorks2008R2 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.

  6. 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.

  7. 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.

  8. 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.

 

[Taken from MSDN documentation and edited]

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s