My Experience with SQL Server 2005 Database Engine Tuning Advisor

I won't go into the how-to's for using SQL Server DBTA.  There are many out there of higher quality than I would produce, most notably:
http://msdn.microsoft.com/en-us/library/ms173494(v=sql.105).aspx

There are also several blogs with good info.

I want to relate some specifics of my recent experience.

I executed both the turning advisor and the activity monitor from a separate instance of SQL server.  My rationale for this was to distribute the workload across these two devices, thus hopefully minimizing the impact to the server that was being profiled.

Several observations:

1.       While the activity monitor will store trace data on the local server, the database tuning advisor will run its analysis and consume resources on the server that is being targeted.  This eventually lead to issues as the memory resources for the server in question were consumed.

2.       It is important to keep the workload being analyzed as small as possible.  The analysis runs for a very long time.

3.       It is important to disable the timeout value.  I had several analysis runs that ran for over an hour and a timed out, producing no recommendations.

4.       It appears possible that prior analyses which are left open continue to consume resources.  By resources, I mean resources on the server are being analyzed, not the server that is running the tuning advisor.

All of that said, the SQL server database tuning advisor is a fantastic tool.  It was able to recommend new indexes and statistics that improved our entire batch cycle time by almost 50%.