SpotLight

How to force Spotlight to update Index Fragmentation report

Hello everyone,

My Spotlight on SQL Server reports an index with 99% fragmentation so I used the generated script to rebuild it.  That was half an hour ago and Spotlight still shows the same index with 99% fragmentation.  Is there a way to force Spotlight on SQL Server to update its Index Fragmentation data?

Ken

  • Hi Ken,
    Because the Index Fragmentation collection by default runs only once a day (at 4:15AM), you won't see the reflected changes in Spotlight until the next day. This by design since this collection can cause timeout issues.
    You can view/edit the scheduled time of this collection in Spotlight by going to Configure toolbar and selecting 'Scheduling' icon. In this dialogue, first choose your connection name from drop-down list on the top. Then scroll down the list of collections to 'Fragmentation Overview' and edit it. Disable 'Factory Settings' option to change time interval of this collection.

    Hope that helps.
    Gita
  • When I follow your steps, I don't see a Background Schedule for "Index Fragmentation". There is an entry for "SQL Index Summary" that has the following settings:

    Collect in background: Not collecting

    Collect for reporting: 12:00 AM every day

    Keep for: 3 days

    Is that the one that collects the index fragmentation information?

    Ken
  • Jason Hall replied: Hey Ken, The collection that drives the Fragmentation data is called "Fragmentation Overview". 

    Ken replied: Found it:  It is not the "Index Fragmentation" but the "Fragmentation Overview" schedule that I am interested in.