SQL Server – Why Database Engine Tuning Advisor Creates Statistics with Auto Create/Update

A couple of things to check into. These two you could capture with a server-side trace or extended events (though I haven't used the latter for this specific task):

• explicit calls to DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE
• certain explicit calls to RECONFIGURE (or RECONFIGURE WITH OVERRIDE)

If you see these calls happening often, you'll want to figure out where they're coming from and why, because they absolutely flush the procedure cache.

Also check the error log for events containing the text "cachestore flush":

EXEC xp_readerrorlog 0, 1, N'cachestore flush';

If you see these events make sure the database in question does not have the option AUTO_CLOSE on. You may want to make sure this isn't the case anyway, and it is often turned on with Web Edition since this edition is typically used to hold a lot of infrequently-used databases at a web host.

The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.

• Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the end of the month, you may be missing the most important part of your workload.
• DTA only considers improving the performance of read queries, and puts no thought whatsoever into what impact new indexes will have on the write portion of your workload. Indexes aren't free.
• DTA has a tendency to recommend multiple redundant indexes, that only differ by one additional key column or an include column. In a lot of cases, one index might be able to satisfy both queries that generated the recommendation for the two individual indexes.

So, the information can be useful, but I would never let any client blindly implement all of the indexes. You need to take a lot more data points into consideration.

For example, how have you come to the conclusion that adding indexes will improve any performance issues you are having? Have you performed any baselines and identified any bottlenecks?