The following messages may appear in the application:
"Messages Metric: Maximum Index Fragmentation The following error occurred while collecting data Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. [0xFFFFFFFE] [Error Code: -2]. Table: Fragmentation Overview"
There are two collections relating to Index Fragmentation: Fragmentation Overview and Fragmentation by Index.
The Fragmentation Overview collection provides data for the control on the home page and the alarm and only runs at the scheduled time.
The Fragmentation by Index collection runs as scheduled (once a night in 10.0.1) and when the fragmentation drilldown is viewed.
Both collections have the potential to timeout because they have to traverse some of the index pages to gather their data. So the larger, or more numerous the number of indexes, the longer they will take. At present, there is nothing that can done about this because it is internal to the SQL Server DMV that is used.
In a future version of Spotlight on SQL Server, the Diagnostic Server will be able to execute two collections simultaneously per monitored instance, so the timeout on the Index Fragmentation collections will be increased. However, there is reluctants to do this in the current version because a long running collection blocks any critical monitoring collections from running.
Summary:
If the Index Fragmentation collections are timing out on a connection, it is recommended that they be disabled for that connection only.
To disable the Index Fragmentation collections...
1) Go to the Scheduling Dialog.
2) Find "Fragmentation by Index" and "Fragmentation Overview".
3) Select Override the default settings.
4) Uncheck Collect data when Spotlight is not connected.
