Interestingly even with both indexes as defined above, when the original query was run again the execution plan was the same вЂ“ a clustered index scan on dbo.SalesOrderDetail and not using either index. These column(s) are my first ones to remove. ItвЂ™s interesting that SQL Server always includes the Primary Keys in either the index definition and/or the INCLUDE clause. Although not a large increase in index size, the Primary Key columns are redundant for the first index causing increased disk space and additional IO. Where dpages is the count of index pages used. WHERE IN ('IDX_SalesOrderDetail_ModifiedDate','IDX_SalesOrderDetail_ModifiedDate2') Running the following query: SELECT id,name,dpages,reserved,used,rowcnt INCLUDE (CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount, SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid)Д¬REATE NONCLUSTERED INDEX IDX_SalesOrderDetail_ModifiedDate2 ON Sales.SalesOrderDetail (ModifiedDate) INCLUDE (SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID, Consider the following 2 indexes: CREATE NONCLUSTERED INDEX IDX_SalesOrderDetail_ModifiedDate ON Sales.SalesOrderDetail (ModifiedDate) IвЂ™ve always been told that the primary key columns do not belong in the INCLUDE block, but letвЂ™s prove it. First consider that it includes the Primary Key columns SalesOrderID and SalesOrderDetailID. While this may be great for this specific query, it just goes against many established guidelines for indexes and query optimization. Maybe, but probably not! The SQL Server suggestion is to essentially create the entire table (all columns) again (in index form) only ordered by ModifiedDate. INCLUDE (SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,LineTotal,rowguid)ДЄha! Just fill in the index name and all is well. * The Query Processor estimates that implementing the following index could improve the query cost by 34.1961%. * Missing Index Details from SQLQuery1.sql - Server.AdventureWorks2012 (ServerUser (54)) If you right click on the header you can select Missing Index Details which gives following in a new query window: /*

The execution plan tab gives the following: SQL Server 2008 now suggests indexes (when it would help the execution plan cost) when specifying an actual query plan while executing a query.Д¬onsider the following query on the AdventureWorks2012 database: SELECT sod.SalesOrderID SQL Server 2005 gave us Dynamic Managed Views (DMVs) that finally allowed queries to look for missing indexes, indexes not used, and rarely used indexes.

Use adventureworks2012 go