The message means that the time to send a simple query to the SQL Server instance, have it processed and get the answer back is more than 20 seconds where it really should be sub-second. What it doesn't tell user is where the time is being taken; it could be the network or the SQL Server instance is very unresponsive.
The Response Time SQL alarm exists for the purpose of being able to allow the user to provide a piece of SQL representative of their normal workload. Spotlight executes that SQL and times how long it takes to execute – this time is then compared against thresholds to fire an alarm.
This is not an error. This is Spotlight telling user that it is taking over 20 seconds to execute the response time query in their environment. This could be indicative of:
- Response time SQL blocking
- A network issue between the Diagnostic Server and the target server(s) -monitored ones
- A problem with the target SQL Server(s)
It is suggested to find out what the response time SQL is (it will be “select 1” if the default has not been changed) and execute it from SSMS from the Diagnostic server machine against one of the servers that is exhibiting the issue. If after a few executions the time take to execute the query is in the 10 of seconds, this will help eliminate Spotlight as a cause.
Additionally one can try Ping command between the Diagnostic Server host and the monitored hosts to see if network travel time is an issue. If one is getting a lot of these alarms then connection could be the problem.
