June 26, 2012 at 11:34 pm
I have created log files using SQL Profiler tuning template to use as Workload for DTA.
My trace log has many UPDATE/INSERT SP and queries too beside SELECT statements.
Can I run Tunning Advisor on production database, does it make any change to data while running UPDATE/INSERT queries from log?
How DTA works, what it do with Profiler generated log files? Does it again runs queries from log against database?
What is the performance impact running DTA on production DB?
June 26, 2012 at 11:44 pm
No, it won't make data changes.
That said, don't run DTA against the productin database. Very bad idea. REstore a copy to dev/test and tune there, don't trust DTA's output, test every recommendation carefully, implement only the ones that help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2012 at 4:19 am
The DTA relies on statistics from your data and your database as well as the queries that you've captured in your trace events file. It uses those to evaluate the need for indexes, etc.
But, as Gail says, get a copy of your production database. Don't point the DTA at your production server.
Because it works off of statistics, which can be out of date, incorrect or even missing, it's suggestions need to be treated that way, as suggestions. It's not magic. It gets things wrong. In my experience, it gets things wrong more than it gets them right.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply