Why Events Are Not Tuned

  • Article

This topic lists the common reasons why events are not tuned and how Database Engine Tuning Advisor chooses the correct database against which to run tuning events.

Common Reasons Why Events Are Not Tuned

Assuming that the database against which the event was originally executed has been identified correctly (refer to the following section), events are not tuned if:

  • They reference tables that have not been selected to tune.
  • They reference very small tables consisting of fewer than 10 pages.

Note

To find the number of pages in a table for Microsoft SQL Server 2000, use the dpages column of the sysindexes system table. To find the number of pages in a table for Microsoft SQL Server 2005, use the used_page_count column of the sys.dm_db_partition_stats catalog view.

  • Database Engine Tuning Advisor does not have sufficient time to tune the query within the time specified by the user.
  • They reference tables that already have close to the maximum number of indexes and the option to keep the existing indexes has been selected. In this case, no additional indexes are possible on those tables.
  • Many data manipulation language (DML) statements (INSERT, UPDATE, and DELETE) are executed against the underlying table for the event. Adding more indexes or indexed views can cause the performance of those underlying tables to degrade.
  • The following tuning options have been specified:

    • The maximum number of columns is limited.

    • The specified disk space for storage is too small, or if there are many existing indexes and indexed views in the database and inadequate disk space has been specified for tuning.

      Note

      You can specify a disk space limit for tuning recommendations by using the Advanced Tuning Options dialog box in the Database Engine Tuning Advisor graphical user interface (GUI), or by specifying a value for the -B argument when using the dta command-line utility.

How Database Engine Tuning Advisor Determines the Correct Database for Tuning Events

Depending on the type of tuning workload file you use and the options you specify when tuning workloads, Database Engine Tuning Advisor chooses the correct database against which to run tuning events, as follows:

For SQL Script (.sql) Tuning Workload Files

  • If you specify a database name with either of the following:
    • -d argument for the dta command-line utility
    • DatabaseToConnect element in the XML input file
      These options set the default database to which Database Engine Tuning Advisor connects first when beginning a tuning session. After that initial database connection, Database Engine Tuning Advisor uses each USE database_name statement in the script to change the database context for subsequent statements.
  • If you do not explicitly specify the initial database connection for a tuning session, Database Engine Tuning Advisor uses the default database that is defined for the login you specify on the server where tuning occurs. After that initial database connection, Database Engine Tuning Advisor changes the database context for subsequent statements the same way as it does when you explicitly specify the initial connection. Database Engine Tuning Advisor uses the USE database_name statements to determine database context.

For Trace (.trc) Tuning Workload Files

When you use trace files, Database Engine Tuning Advisor chooses the database against which to run events for each server process ID (SPID) that is specified in the trace. If no SPID is specified in the trace, Database Engine Tuning Advisor assumes the same SPID for all events that are contained in that trace. Database Engine Tuning Advisor chooses the database for each SPID as follows:

  1. DatabaseName is present in the trace.
    If the DatabaseName field is present in the trace file, Database Engine Tuning Advisor uses the field to determine the database against which to parse the event. All other information is ignored. If the DatabaseName field is not present, Database Engine Tuning Advisor goes to step 2.
  2. DatabaseID is present in the trace, but DatabaseName is not.

If the DatabaseID field is present in the trace file, Database Engine Tuning Advisor uses it to determine the database against which to parse the event. The DatabaseID field is mapped to the database name. However, Database Engine Tuning Advisor does not validate whether DatabaseID fields have changed since the trace was gathered. If you are not sure that the DatabaseID-to- DatabaseName mapping is current, remove the DatabaseID field from the trace workload file. All other information is ignored. If neither DatabaseName nor DatabaseID is present, Database Engine Tuning Advisor goes to step 3.

  1. Neither DatabaseName nor DatabaseID is present in the trace.
    If neither DatabaseName nor DatabaseID is present in the trace, Database Engine Tuning Advisor determines the database against which to run the event in the same way as it does for SQL script tuning workload files. This process is described in the preceding section, For SQL Script (.sql) Tuning Workload Files.

See Also

Concepts

Determining Whether Events Can Be Tuned

Help and Information

Getting SQL Server 2005 Assistance