Showing posts from May 13, 2012

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:

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 dis…