Spotlight on SQL Server 10.0.3 - Getting Started Guide

Select Default Settings.

Note: This must be set to Default Settings to add, edit and delete custom counters.

The list of Custom Counters. The type may be Windows or SQL Server.

Click to add a custom counter.

Fill in the Add Custom Counters Dialog:

• Type a name for the custom counter as it will be displayed in the Custom Counters drilldown.
• Choose the type of counter: SQL Server or Windows.
• Enter a query in the Query field.

For SQL Server 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.

User Connections (SQL Server 2000)

select cntr_value from master.dbo.sysperfinfo

where  object_name like '%:General Statistics%'

and    counter_name = 'User Connections'

Pages Allocated (SQL Server 2005 or later)

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

Note: The Custom Counter is initially not selected when added to the List of Custom Counters. See following for instructions to select / clear a custom counter for a connection.

Click to modify the selected custom counter.

Click to remove the selected custom counter.

Select the name of the connection. You can select / clear custom counters for this connection.

Select to set this connection to other than the default.

Clear to return settings for this connection to the default.

Select or clear each custom counter as appropriate. Selected custom counters are used by the connection.