SQL Server Performance

Set Statistics XML doesnt see index?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by MichaelB, Aug 19, 2008.

  1. MichaelB Member

    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
  2. moh_hassan20 New Member

    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

Share This Page