Does SQL Server's Database Tuning Advisor modify data?

Question

I've been trying to increase performance and diagnose deadlocks on a SQL Server 2005 instance. It had been running SQL Server 2000 until a month ago, when an in situ upgrade was done. After the upgrade, we've experienced a number of issues- performance is down and deadlocks are way up. MS suggested we use 2005's Database Engine Tuning Advisor (DTA/DETA).

I'm generally am skeptical of automated tools like this, but message DTA tossed up on start up is what really spooked me:

"In the Workload section, select a database to which Database Engine Tuning Advisor will connect for analyzing the workload. If your workload include events or Transact-SQL statements that change the database, Database Engine Tuning Advisor will also change the database while analyzing the workload. Finally, select one or more databases or specific tables to tune."

Which implies, at least to me, that it will re-run any and all of the statements when doing the workload analysis. Is that the case? If so, does it rollback statements and transactions as it does so or just chew through everything in a trace file verbatim?

A side question: what difference does the "Database for workload analysis" make? It defaults to master. Does it make more sense to leave it at master or change it to the name of the database I want tuned?

Thanks in advance!
     Aaron

Answer 1

I've used it multiple times on mission-critical database with millions of records, while there were users modifying data in the database and it works perfectly for me. It has never modified or corrupted data. The only thing it's done is slow things down while it applied the index changes.

I know for sure that it does not re-run the statements, because I've got some processes that do modify the data. I intentionally ran those while doing the profiler, and then used the tuning wizard on it, and I would have known if these particular statements were re-executed. The work being done is very obvious and would have resulted in obvious discrepancies.

All of that said, it is a good idea to have a backup of your DB just in case.