Database Tuning Advisor in SQL Server 2005

[Happy Harry]

Hi All

This is regarding Database Tuning Advisor (DTA) in SQL Server 2005, Currently I am using DTA directly on the main production machine system. Of late I have noticed there is a recompilation, why this happens? Is it not proper to use Database Tuning Advisor (DTA) in SQL Server 2005 directly on main machine. What are the Implications?

Thanks,
Happy Harry

[lokeshm]

Dear Harry

It is best recommended to run Database Tuning Advisor on a seperate machine. Make a copy of your main/production database to an independent machine and run DTA. It is a fact that DTA may create imaginery index and or statistics which may later result in recompilation.

Regards.

[nickole]

if any one is wondering what is database tuning advisor....

This is an enhanced version of the Index tuning Wizard. In SQL Server 2005 it integrates with the SQL Profiler and reduces the time spent on tuning. It also helps determine whether aligned or non-aligned layout should be used to benefit performance in dealing with partitioning related tables.

It analyzes workload and physical implementation of one or more databases. It uses trace files, trace tables or Transact-SQL scripts as workload input when tuning databases.

It can recommend the optimum physical design structure for the database and statistics that should be collected to backup physical design structures.

Hope this helps some one to understand what it is....

send me PM if you need more explanation or post here..

Thanks for the excellent forum by exforsys