Database Engine Tuning Advisor (DTA) is a graphical SQL Server Management Studio tool that analyzes a supplied workload and recommends changes to indexes, indexed views, partitioning, and other physical structures. Treat its output as guidance, not an implementation plan: it cannot account for queries absent from the workload or for business priorities. section 11.10
Avoid the memorable mistake of implementing every recommendation:
- Check workload coverage. DTA can analyze a SQL trace file or table, the plan cache, or Query Store, but excluded queries receive no consideration. section 11.10
- Check operational context. A missing month-end process or short ETL window can make dropping an index look reasonable while putting a critical deadline at risk. section 11.10
- Check the plan-cache age. A recently restarted Database Engine has an empty plan cache, so analyzing it can produce dangerously incomplete advice. section 11.10
- Review recommendations with DBA or developer knowledge before changing production structures; preserve business-critical indexes and weigh maintenance and workload tradeoffs. section 11.10 section 11.12
The empty-plan-cache disaster
A new DBA analyzed the plan cache only a few hours after the SQL Server Database Engine restarted, not realizing that the restart had cleared it. He implemented the recommendations without review and dropped roughly 90% of the indexes across the instance. The safer response is to validate workload completeness and business-critical processes before accepting any drop recommendation. section 11.10
