SQL Server Performance Forum – Threads Archive

Database Tuning Advisor

I am not able to get my DB Trace analyzed through the Tuning Advisor. Here is what I am doing … I create a new trace. Capture activities for 30 minutes using the default template. Save it as a Trace File. I go to Database Tuning Advisor, select the saved trace file and select the DB to be tuned. Start the Analysis. I always get the Warning "90% of the consumed workload has syntax error. Check tuning log for more information." OR "All the events in the workload were ignored due to syntax errors.The most common reason for this error is that the database to connect has not been set correctly." In the end it does not have any recommendations….I am not sure what I am doing wrong. Any pointeres are greatly appreciated. Thanks.!

If you trace have store procedures calling other procedures, DTA does not work.
If you trace have queries with temp tables, DTA does not work. Also, did you check the correct database?. To get sure, pick one query from profiler, copy and paste into Managment Studio and test it. If work well, then call DTA from Managment Studio and tune the query.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Someone asked a question one day about slow performance on a database in SQL Server 2005 and I advised them to use the Database Tuning Advisor but then came another person and said the following:
"I’ve seen it suggest three indexes and seven statistics for a single query. In testing, one of those indexes reduced the queries’s execution time greatly, the others had little to no effect."
So what I want to know is, is it good to run the DTA or not? I have been using it for quite some time now with no hassles using a table i.s.o. a file to save my traces to. I have not had any of the problems that I read about in this forum. I have actually seen a great improvement in the performance of my database, so can someone please tell me whether this is good?. I am not a DBA (trying to be but still much to learn) but actually a developer.

I support the idea to use DTA.
Is a great tool to find indexes, and probe it in tester server.
You only have to use carefully, i.e., if the index go to a heavy insert table, you will gain performance when reading but loose when inserting.

]]>