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%.
