I have a query that pulls 900 K rows (no laughing - this is a vendor app! - ok.. u can laugh at the vendor) when I run the query with "set statistics xml on" it says gives the following <MissingIndexGroup Impact="81.0404"> <MissingIndex Database="[Mydatabase]" Schema="[FYIADM]" Table="[FYI_SYSDATA]"> <ColumnGroup Usage="EQUALITY"> <Column Name="[SYS_DKEY]" ColumnId="2" /> <Column Name="[SYS_DELETE_USERID]" ColumnId="19" /> <Column Name="[SYS_LIFE_CYCLE]" ColumnId="22" /> </ColumnGroup> </MissingIndex> </MissingIndexGroup> This index exists on that table and I have updated stats with fullscan: CREATE NONCLUSTERED INDEX [missing_index_78657_78656_FYI_SYSDATA] ON [FYIADM].[FYI_SYSDATA] ( [SYS_DKEY] ASC, [SYS_DELETE_USERID] ASC, [SYS_LIFE_CYCLE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] I am pretty sure that since this query is pulling half the table that the optimizer is not using the index and just doing a table scan. BUT why would it say it is missing when it is there?? I am confused! Mike
The missing indexes feature does not provide accurate information for fine tuning index. Use DTA for fine tuning. see the Limitations for Using the Missing Indexes Feature at: http://msdn.microsoft.com/en-us/library/ms345485(SQL.90).aspx