Configure Spotlight to track counters not included in Spotlight.

Spotlight can be configured to raise an alarm on custom counters. For more information, see Add alarms to custom counters and SQL Server | SQL Activity drilldown. To configure how often Spotlight collects Custom Counter data, see Configure | Scheduling.

Configure custom counters from the Spotlight Client

Click Configure Custom Counters Configure | Custom Counters.

Select template or connection to configure

Select the SQL Server, Azure SQL Managed Instance, or Windows connection from the list of available options. Spotlight will show the configuration for that connection. Make changes if required. Optionally use the options at the bottom of the screen to apply the same configuration to other SQL Server (Windows)/Azure SQL Managed Instance connections or save the configuration as a template.

Alternatively, select a template to configure. The templates you have previously saved for Spotlight Custom Counter configuration are listed. On selecting the template you can view / modify / reapply the template configuration. You can delete or rename the template. For more information, see Configuration templates.

Override or use the factory settings

Override the factory settings

Select the Override the factory settings option to modify the settings for the selected template or connection.

Add custom counters to the list. Click Add. Enter the name and query for the custom counter.

To edit the custom counter name and query, select the custom counter and click Edit. To remove a custom counter from the list, select the custom counter and click Remove. To disable a custom counter, deselect the check box next to the custom counter name.

Click Edit Thresholds to open Configure | Alarms. Custom Counters are available as Key values in the Configure | Alarms dialog and thresholds can be independently set for each key value.

Use factory settings

Clear the Override the factory settings option to return the settings for the selected template or connection to the Factory Settings. These are the settings that were shipped with Spotlight.

The default setting is no custom counters for this connection.

Custom Counter properties

Name

The name of the custom counter. The name is displayed in the Custom Counters drilldown.

Query

For SQL Server or Azure SQL Managed Instance queries, use T-SQL syntax. For Windows queries, use WMI. It is recommended that the query return only one column and one row. For all custom counter queries, Spotlight reads only the first column of the first row of data. Any additional data is ignored. To return multiple values, use more than one query. See the following for examples of custom counter queries.

Pages Allocated (SQL Server, Azure SQL Managed Instance)

select cntr_value from sys.dm_os_performance_counters
where  object_name like '%:Access Methods%'
and    counter_name = 'Pages Allocated/sec'

Open Files (Windows)

SELECT FilesOpen FROM Win32_PerfRawData_PerfNet_Server

Reuse this configuration

Apply configuration to…

Optionally, apply this configuration to other connections.

Save to Template

Optionally, save this configuration to a template. Where a template has been selected to configure you will also see options to Delete or Rename the template. For more information on Templates, see Configuration templates.