Menu
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
965
On the Database Engine Tuning Advisor GUI, you can tune a database by using the plan cache, workload files, or workload tables. You can use the Database Engine Tuning Advisor GUI to easily view the results of your current tuning session and results of previous tuning sessions.
A SQL Server database automatically manages system resources, like disk space and memory, to reduce the need for large-scale manual optimization. SQL Server performance tuning allows administrators to view performance trends and determine if changes are needed.
Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as indexes, indexed views, and partitioning.
SQL performance tuning is similar to database performance tuning, but it's narrower in scope. SQL performance tuning refers to best practices and procedures designed to ensure relational databases are running as efficiently as possible. This primarily involves tuning, managing, and optimizing SQL queries and indexes.
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.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
