Return
-
Title
How to disable running Spotlight collection query against a specific SQL instance -
Description
Some of Spotlight query collections can be quite expensive on the server environment, so you can disable these Spotlight query collections completely. -
Cause
Disabling the Spotlight query collections in the front end Spotlight client might not completely stop the Spotlight query collections for a specific SQL Instance, because there are some other collection and scripts that depend on it; and will trigger this script when running some alarms.
For example: alarms like BlockedProcesses, Deadlocks, PageLifeExpectancy and SQLRecompiles are depend on the QS_FullConnectionsList.sql Spotlight collection script.
The reason for this product architecture design is to make the alarm and drilldown data consistent, for example if a blocking alarm is raised, the user can check the session drilldown which collected at the same time and thus be able to diagnose the issue.
-
Resolution
1. Check the SQL Server version, as an example we will use a SQL Server 2016.2. Open the collection script file for SQL Server 2016 which is located in your Spotlight Diagnostic Server machine under this path:{Diagnostic Server installation path}\Agent\conf\Package\sqlserver_spotlight\2012\CollectionScript.sql3. Locate the script file to the end part, and insert below script to it and update the servername value accordingly:if @@servername = domain\database' -- Change the servername value here to your expected instance nameset @SQL = 'select 1'4. Save the script file and restart the Spotlight Diagnostic Server service.