Filter to select date is grayed out when optimizing indexes for Spotlight Statistic Repository (4246577)

Filter to select date is grayed out when optimizing indexes for Spotlight Statistic Repository

In the Optimize Indexes tab, when a Spotlight Statistics Repository is chosen for a source, after filling in the database of the source, the database, and the monitored instance, it will not allow a date to be selected for the collection date.  That filter is grayed out and has a little warning symbol after it that says "There isn't date collcted in current selected criteria" when hovered over. However, that selected criteria has been collecting.

The procedures that SQL Optimizer runs to gather the date for the specified criteria looks for Top SQL Statements.  If these are not being collected, then it will not see a date to chose from.  By default, Top SQL Statements which is controlled by Query Execution Statistics collection are not collected in Spotlight.

Adjust the Spotlight collection configuration:

1. In Spotlight, select Configure | Schedule option
2. Select a monitored SQL Server connection from the top drop down list
3. Scroll down the list of collections and select Query Execution Statistics collection. Edit the collection.
4. Uncheck Factory Settings optoin and Check Store Data option
5. Adjust the interval collection if needed and click OK and OK to save
6. Wait at least an hour or two and try Optimize Indexes option again  

See attached screen shots for additional details.

SQL Optimizer Screen Shot
Spotlight Screen Shot 2
Spotlight Screen Shot 1