Running Index Tuning Wizard (ITW) or Database Engine Tuning Advisor (DTA) on production machines...

  • Article

Index Tuning Wizard (ITW in SQL Server 2000) or Database Engine Tuning Advisor (DTA in SQL Server 2005) allow you to analyze a workload and make recommendations for the database based on the workload. The recommendations are schema changes, partitioning or indexed view creation for example. During the recommendation process, ITW or DTA can create hypothetical indexes or statistics it will use to analyze the workload. Creation of these indexes or statistics on any table increments the schema version information that is maintained by the database engine. As a result of this, any subsequent queries that reference tables that were touched by ITW or DTA will result in recompilation. This recompilation will be at the statement level in SQL Server 2005 or at the stored procedure level in SQL Server 2000. This is something that you should watch out for when trying to use ITW or DTA on a production machine directly. It is always better to run the tool on a copy of the production database on a different machine in isolation so you can review the changes without affecting other workload and also apply the changes easily without interruption.

 

--

Umachandar