locked
Missing Index Information is Missing RRS feed

  • Question

  • I have been using the dynamic management objects to evaluate missing indexes on one of our SQL Server 2008 SP1 servers for several months now.  I also have a daily task in place to persist that data for analysis.  Additionally, I evaluate the  MissingIndexes element in XML Showplans, also to determine if there may be missing indexes.  I also persist this data periodically throughout the day.  There have been numerous missing indexes recommended in both the dynamic management objects and the MissingIndexes element in XML Showplans until 7/13/2010.  For some inexplicable reason, neither shows any missing indexes since 7/13.  Additionally, there are 39,512 entries in dm_db_missing_index_group_stats that do not have a corresponding group_handle in dm_db_missing_index_groups.  There are also 600 entries in dm_db_missing_index_groups that do not have a corresponding index_group_handle in dm_db_missing_index_group_stats.  I understand that there is a limitation of 500 missing index groups, which is a strange limitation, but that doesn't explain the sudden lack of data in the MissingIndexes element in XML Showplans.  Any information that anyone may have on this issue would be appreciated.
    This is a cluster and the last failover, which was planned and initiated from Windows Cluster Mgmt., occurred in late May.  That would have been the last service re-start.
    Monday, July 26, 2010 9:24 PM

Answers

  • We failed over the affected cluster last week and missing index capture was restored.
    • Marked as answer by Tom Li - MSFT Friday, August 13, 2010 1:05 AM
    Thursday, August 12, 2010 7:08 PM

All replies

  • See if someone enabled Trace Flag 2330 on the instance at some point since the last failover:

    DBCC TRACESTATUS(-1)

    This flag disables collection of index usage statistics to feed data for sys.db_index_usage_stats DMV. So after you enable this TF and restart the server, you should see empty rows in this DMV.  This data also feeds into the Missing Indexes DMV suggestions held by sys.dm_db_missing_index_group_stats and will also not be available. (http://support.microsoft.com/default.aspx?scid=kb;en-us;2003031)  You don't have to restart SQL to enable the flag, you can do it with DBCC TRACEON(2330, -1) and it has serverwide scope as well.


    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Tuesday, July 27, 2010 4:07 AM
  • I verified that trace flag 2330 is not on.  Thanks.
    Tuesday, July 27, 2010 3:18 PM
  • Hi cehottle,

     

    Based on my research, this issue might be caused by the start switch “-x”.

    If SQL Server is started with switch “-x”, Missing Indexes Feature will be disabled.

    For more information about this switch, you could refer to the "Enabling and Disabling the Missing Indexes Feature" section in this link: http://msdn.microsoft.com/en-us/library/ms345524.aspx

     

    Please follow the steps to check if this instance is started with switch “-x”:

    1.       Download Process Explorer
    we could get it from this link:
    http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

    2.       Run Process Explorer
    if we are using an operating system with User Account Control, please run it with “Run as Administrator”

    3.       Check the “Command Line” column to see if SQL Server is started with “-x” switch
    if there is no such column, we could right click the column header, select “Select Columns…” and check “Command Line” to display this column

     

    If anything is unclear, please let me know.

    Thursday, July 29, 2010 11:33 AM
  • The server had not been started with the -x switch.  This is a cluster, so no one explicitly stops and starts the service.  We had reason to failover the cluster last night and the service restart resolved the issue for now.  I'm wondering if this isn't related to to the limitation on the amount of data that can be retained in one of the missing index DMVs.

    http://msdn.microsoft.com/en-us/library/ms345485.aspx

     

    • Proposed as answer by Tom Li - MSFT Friday, August 6, 2010 1:57 AM
    Thursday, August 5, 2010 1:19 PM
  • We failed over the affected cluster last week and missing index capture was restored.
    • Marked as answer by Tom Li - MSFT Friday, August 13, 2010 1:05 AM
    Thursday, August 12, 2010 7:08 PM
  • Even though this resolved the issue, why did it break to begin with?
    Friday, August 13, 2010 12:30 PM