• Article

Considerations for Using Database Engine Tuning Advisor

Before you use Database Engine Tuning Advisor, you should understand its capabilities and performance impact. The following sections describe these considerations, but this is not an exhaustive list of issues. Your specific environment and implementation of SQL Server determine the exact issues you must consider when using Database Engine Tuning Advisor.

Database Engine Tuning Advisor Capabilities

Database Engine Tuning Advisor does not do the following:

  • Recommend indexes on system tables.

  • Add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.

  • Tune single-user databases.

Note

Database Engine Tuning Advisor does recommend unique clustered indexes on views when indexed views are part of its recommendation.

In addition, Database Engine Tuning Advisor has the following limitations:

  • Database Engine Tuning Advisor gathers statistics by sampling the data. Consequently, repeatedly running the tool on the same workload may produce different results.

  • Database Engine Tuning Advisor cannot be used to tune indexes in databases from Microsoft SQL Server 7.0 or earlier.

  • If you specify a maximum disk space for tuning recommendations that exceeds the 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. Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.

  • 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 choose one of the following options:

    • Use a trace file instead of a trace table.

    • Copy the trace table to the remote server.

  • When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.

  • When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), 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 might not make partitioning recommendations under the following circumstances:

  • The xp_msver extended stored procedure is not enabled. This extended stored procedure is used to fetch the number of processors and available memory on the server where the database resides that is being tuned. Note that this extended stored procedure is on by default when you install SQL Server. For more information, see Understanding Surface Area Configuration and xp_msver (Transact-SQL).

Note

When Database Engine Tuning Advisor tunes a test server the xp_msver extended stored procedure must be enabled so Database Engine Tuning Advisor can fetch information from the production server during tuning. For more information, see Considerations for Using Test Servers.

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. For more information, see Reducing the Production Server Tuning Load.

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

Database Engine Tuning Advisor Stores Session Information in the msdb Database

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.