I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following:Date 28-12-2007Time 18:29:31Server SQL2K5Database(s) to tune [DBProd]Workload file C:\Temp\filtered.trcMaximum tuning time 31 MinutesTime taken for tuning 31 MinutesExpected percentage improvement 20.52Maximum space for recommendation (MB) 12874Space used currently (MB) 7534Space used by recommendation (MB) 8116Number of events in workload 545Number of events tuned 80Number of statements tuned 145Percent SELECT statements in the tuned set 77Percent INSERT statements in the tuned set 13Percent UPDATE statements in the tuned set 8Number of indexes recommended to be created 15Number of statistics recommended to be created 50
Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was:Date 31-12-2007Time 10:03:09Server SQL2K5Database(s) to tune [DBProd]Workload file C:\Temp\filtered.trcMaximum tuning time UnlimitedTime taken for tuning 2 Days 13 Hours 44 MinutesExpected percentage improvement 0.00Maximum space for recommendation (MB) 12874Space used currently (MB) 7534Space used by recommendation (MB) 7534Number of events in workload 545Number of events tuned 545Number of statements tuned 1064Percent SELECT statements in the tuned set 71Percent INSERT statements in the tuned set 21Percent DELETE statements in the tuned set 1Percent UPDATE statements in the tuned set 5
This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation. 
It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.I couldn't google an answer for this. Help!!!Thanks in advance.++Vitoco
DTA: Expected improvement 0%
Posted - 2007-12-31 : 11:03:42
