Database Engine Tuning Advisor

Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning. Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when tuning databases.

Limitations and Restrictions

The Database Engine Tuning Advisor has the following limitations and restrictions.

1.       It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

2.       It cannot analyze a database that is set to single-user mode.

3.       If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first.

4.       For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.

When you specify a constraint to limit tuning, Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.

Database Engine Tuning Advisor might not make recommendations under the following circumstances:

·         The table being tuned contains less than 10 data pages.

·         The recommended indexes would not offer enough improvement in query performance over the current physical database design.

·         The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. The user must be a member of the db_owner database role.

Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. If changes are made to the msdb database you may risk losing tuning session data. To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Performance Considerations

Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. To avoid slowing down your production server, follow one of these strategies:

·         Tune your databases when your server is free. Database Engine Tuning Advisor can affect maintenance task performance.

·         Use the test server/production server feature.

·         Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Physical Design Structures to Use in Database

Tuning option	Causes Database Engine Tuning Advisor Recommendations to …
Indexes and indexed views	Add clustered indexes, nonclustered indexes, and indexed views.
Indexed views	Add only indexed views.
Include filtered indexes	Consider adding filtered indexes. This option is available when you select the Indexes and indexed views option, the Indexes option, or the Nonclustered indexes option.
Indexes	Add only indexes.
Nonclustered indexes	Add only nonclustered indexes.
Evaluate utilization of existing PDS only	Evaluate the existing physical design structures only, but not to consider adding any new structures.

Partitioning Strategy to Employ

Tuning Option	Causes Database Engine Tuning Advisor to …
No partitioning	Not use partitioning in its recommendation.
Full partitioning	Use partitioning in its recommendation.
Aligned partitioning	Only recommend partitions that are aligned with existing partitions of underlying tables or views. (Selecting this option enhances manageability.)

Physical Design Structures to Keep in the Database

Tuning Option	Causes Database Engine Tuning Advisor to …
Do not keep existing PDS	Drop any existing physical design structure if appropriate. Select the Evaluate utilization of existing PDS option with this option to set Database Engine Tuning Advisor to the drop-only mode, which only evaluates current physical design structures to determine whether they can be dropped.
Keep indexes only	Drop all structures except indexes if appropriate.
Keep all existing PDS	Keep all existing structures.
Keep clustered indexes only	Drop all structures except clustered indexes if appropriate.
Keep aligned partitioning	Drop all structures except aligned partitions if appropriate.

Advanced Tuning Options

You can specify the maximum disk space that the configurations recommended by Database Engine Tuning Advisor can consume. If you specify a limit that exceeds your actual disk space, then Database Engine Tuning Advisor may recommend a configuration that cannot be implemented until you add disk space to the server. If this occurs, a warning appears in the tuning log.

The maximum disk space limit is specified in megabytes. When multiple databases are tuned, all of the recommendations for them together (collectively) cannot exceed the maximum space limit that you specify.

If unspecified, Database Engine Tuning Advisor assumes the smaller of the following:

·         Three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database, or

·         The free space on the all attached disk drives plus the raw data size.

The default space limit does not include nonclustered indexes and indexed views.

Max. columns per index

Specify the maximum number of columns to include in any index. The default is 1023.

All recommendations are offline

Generate the best recommendations possible, but do not recommend that any physical design structures be created online.

Generate online recommendations where possible

When creating Transact-SQL statements to implement the recommendations, choose methods that can be implemented with the server online, even if a faster offline method is available.

Generate only online recommendations

Only make recommendations that allow the server to stay online.