Hello and welcome to my first blog. If I'm honest, I've been pondering hard on what my first blog should be and I think I found just the topic to get my feet wet.
 |
One of the common cases that I often see users of Spotlight on SQL Server Enterprise (or SoSSE) run into is, "My Spotlight is broken. I see a yellow triangle in the Index Fragmentation part of the Spotlight dash board."
What is this yellow triangle that shows up in place of where data should be? If you click into the yellow triangle, it will show the following error:
Before Spotlight 11.5, the message would be...
“Collection 'Fragmentation Overview' failed: Timeout expired.
The timeout period elapsed prior to completion of the operation
or the server is not responding.”
From Spotlight 11.5 and onward, you will see...
“Monitored Server - SQL Server Collection Execution Failure
1/11/2016 1:11:00 AM Collection 'Fragmentation Overview' failed :
Collection 'Fragmentation by Index' failed : The SQL query has
been executing for longer than its timeout limit but has not
timed out. It may be hung.”
The query to get Index Fragmentation can potentially be a long running query. If the monitor server is busy, the query for Index Fragmentation can time out. By default the collection schedule collects at 4:15 AM, which we hope is a time when the servers are not too busy; however, that may not be the case. Since Fragmentation only runs once in the morning and it fails because Spotlight can't complete the query, you end up seeing this yellow triangle all day. We are working on a way to make this collection executes in less time. In the meantime, this is how you can tackle the issue.
1. Try to run it now to see if you do get data. Do so by changing the Index Fragmentation collection schedule.
a. Go to "Configure | Scheduling"
b. In the top drop down, change from “Factory Setting” to the SQL Server with the issue
c. Look for the schedule "Fragmentation Overview" and click on the square icon next to it for the pop-up window
Note: If you are running pre 11.5 versions, then highlight the fragmentation schedule and make the changes at the bottom.
d. Uncheck "Factory Settings" and click on "at 4:15 AM every day"
e. Change to 1 Minute and if data does come back than we know maybe 4:15 AM is not an ideal time to run this
2. Once you have determined that 4:15 AM is not a good time to run the this collection, you can either guess, with trial and error, other times to run the collection or run it on intervals. If choosing to run the collection on intervals, make the times between the intervals hours long. For example, maybe 4 or 8 hour intervals.
Looking for more on Spotlight on SQL Server Enterprise? Check out this video on how to optimize and tune SQL Server performance anywhere on any device or download a free trial.
