Microsoft SQL Server 2008 R2 Unleashed- P127:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1204 CHAPTER 34 Data Structures Indexes and Performance Table details some other differences between the missing indexes feature and Database Engine Tuning Advisor in greater detail. TABLE Differences Between Missing Index Features and Database Engine Tuning Advisor Comparison Point Missing Indexes Feature Database Engine Tuning Advisor Execution method Server side always on Client-side standalone application Scope of analysis Quick ad hoc analysis providing limited information about missing indexes only Thorough workload analysis providing full recommendation report about the best physical database design configuration in the context of a submitted workload Statements analyzed SELECT statements only SELECT UPDATE INSERT and DELETE Available disk storage space Not factored into analysis Factored into analysis recommendations are not provided if they would exceed available storage space Columns ordering Recommended index column order not provided Optimal index column order determined based on query execution cost Index type Nonclustered only Both clustered and nonclustered index recommendations provided Indexed views recommendations Not provided Recommended in supported editions Partitioning recommendations Not provided Recommended in supported editions Impact analysis An approximate impact of adding a missing index is reported via the up_stats dynamic management view DMV Up to 15 different analysis reports generated to provide information about the impact of implementing recommendations Download from Identifying Unused Indexes 1205 Identifying Unused Indexes As mentioned previously in this chapter each index on a table adds additional overhead for data modifications because the indexes also need to be maintained as changes are made to index key columns. In an OLTP environment excessive indexes on your tables can be almost as much of a performance issue as missing indexes. To improve OLTP performance you .
