i decided to give the tuning advisor a go, just as I had some spare time. I ran profiler against a database for about a half hour, then used the resulting trace file as the input for the tuining advisor.. A lot of the sprocs called in the trace file cannot be executed again,as they will cause issues with data integrity and if someone tries to execute them , an error is produced detailing primary key violations. the error tuning advisor actually shows is this:
"51% of consumed workload had syntax errors. Check tuning log for more information".
My question is though:
1. For tuning advisor to be effective, does the code/trace its analysing have to be valid, or will the now invalid data in my trace file work?
2. Does the tuining advisor actually run the sql against the database, effectively performing changes to data, or just run an execution plan of the query?
3. is the error i recieve expected or something im doing wrong?
Thanks all for any answers.
What I have observed while running DTA is that all of the recommendation that you receive at the end are actually have been tried by DTA. All of the indexes/stats were actually created and your procedure execution plans were generated without any data modification to see the performance difference. That is how you get the matrics of before and after implementing recommended changes but for sure no data modification takes place whatsoever.
Receiving error message during tuning is expected and it should not be an issue of concern.
No comments:
Post a Comment