Differences Between Database Engine Tuning Advisor and Index Tuning Wizard

In addition to handling the new database features of Microsoft SQL Server 2005, Database Engine Tuning Advisor also behaves differently from the Microsoft SQL Server 2000 Index Tuning Wizard. Although both tools provide a graphical user interface (GUI) and a command prompt interface, users who are familiar with Index Tuning Wizard should take the following changes into account.

For a full list of Database Engine Tuning Advisor new features, see Database Engine Tuning Advisor Features.

Permissions Required to Tune Databases

In SQL Server 2000, only members of the sysadmin fixed server role could use the Index Tuning Wizard to tune databases. In SQL Server 2005, using Database Engine Tuning Advisor, members of the sysadmin role can still tune databases, but now users who are members of the db_owner fixed database role also can tune the databases that they own.

Workload Context

The Index Tuning Wizard evaluated each statement in the workload using the database that was selected to tune, without regard to whether the statement was originally executed in the context of that database. Index Tuning Wizard could tune only one database during a tuning session. In SQL Server 2005, Database Engine Tuning Advisor can tune multiple databases during one tuning session. Database Engine Tuning Advisor uses the information from the script to determine the database where the statement is run, and evaluates the statement against that database. The databases that are selected to tune do not affect how the statements are evaluated.

For example:

• The AdventureWorks database has a Person.Contact table with the columns FirstName and LastName.

• The workload TuneQuery.sql contains the following query:

  SELECT FirstName, LastName
  FROM Person.Contact
  WHERE LastName = 'Abercrombie';
  GO
  

• User1 connects to the MyDB database by default.

In SQL Server 2000, User1 issued the following from the command-line or performed similar steps using the Index Tuning Wizard GUI:

Itwiz -D AdventureWorks -I TuneQuery.sql –o rec.sql –U <username> –P <password>

This approach worked, as each statement in TuneQuery.sql was parsed against the AdventureWorks database because it was specified on the command line (-D AventureWorks). TuneQuery.sql was valid in the AdventureWorks database and tuning proceeded without any issues.

In SQL Server 2005, the behavior has changed. The Database Engine Tuning Advisor command line syntax is:

dta -s Session1 –D AdventureWorks –if TuneQuery.sql –of rec.sql –U username –P password

Because User1 connects by default to the MyDB database, the system sets the database context to MyDB. Next, the Transact-SQL statement is parsed against the MyDB database and not against AdventureWorks. The statement is not valid in MyDB and so it is ignored.

Why did this happen? If User1 executes the TuneQuery.sql using sqlcmd or SQL Server Management Studio without specifying a target database, TuneQuery.sql would be executed against MyDB and fail. Database Engine Tuning Advisor mimics this same behavior.

What should be done? Add a USE <database> statement to the script TuneQuery.sql as follows:

USE AdventureWorks;
GO
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName = 'Abercrombie';
GO

Database Engine Tuning Advisor first sees the statement USE AdventureWorks and uses that information to set the current database to be AdventureWorks. Next when it sees the statement SELECT FirstName, LastName FROM Person.Contact WHERE LastName = 'Abercrombie', it parses the statement against AdventureWorks, because the current database context is AdventureWorks. This enables Database Engine Tuning Advisor to successfully tune the database. Note that if you execute the above script using sqlcmd or SQL Server Management Studio, the statement is executed against AdventureWorks, because the first USE <database> statement changes the database context from MyDB to AdventureWorks.

The USE <database> statements can be used to specify the database against which the statement is intended to be executed. In general, this is not necessary if each statement uses fully qualified table names.

Because Database Engine Tuning Advisor tries to find the respective database against which each statement is run (to mimic the execution environment), the following information is crucial to understanding how Database Engine Tuning Advisor deals with different types of input.

SQL File/Inline Workload

As mentioned in the preceding section, Database Engine Tuning Advisor uses USE <database> statements that precede a Transact-SQL query to identify the database against which the query should execute. Database Engine Tuning Advisor looks at the input starting from the first statement in the Transact-SQL script file. It starts with the assumption that the current database is the default database. The presence of USE <database> statements changes the current database context against which statements are parsed.

Trace Files and Trace Tables

Database Engine Tuning Advisor mimics the replay of SQL Server Profiler when it goes through the trace file. It uses the following information from trace files in the order listed:

• If the trace file has events with the DatabaseName column populated, Database Engine Tuning Advisor uses that to find the database against which that event was executed.
• If the trace file has the DatabaseID column populated, Database Engine Tuning Advisor uses that to find the database against which that event was executed. It queries the system catalog to find the database name corresponding to DatabaseID.

• If neither DatabaseName nor DatabaseID are present as columns in the trace, Database Engine Tuning Advisor decides which database to use for each statement as it does with Transact-SQL scripts for each SPID column in the trace file. If the SPID column is not present, then the determination is made in exactly the same way as it is for Transact-SQL script files.

Database Engine Tuning Advisor also uses the login information (as in SQL Server Profiler replay) during the parsing of each statement. Default databases on the server can change depending on the LoginName column values that appear in the trace file.

Tuning Time Limits

In SQL Server 2005, Database Engine Tuning Advisor allows you to specify a tuning time, or to specify an unlimited tuning time. This feature was not available in Index Tuning Wizard. For more information, see Limiting Tuning Duration and Events.

See Also

Other Resources

Database Engine Tuning Advisor Tutorial
Tuning the Physical Database Design

Help and Information

Getting SQL Server 2005 Assistance