The Provide Custom SQL Script window of the Add Custom Counter wizard allows you to enter or paste a T-SQL script for your custom counter. For more information about T-SQL scripts, see SQL Server Books Online.
Refer to the following tips when using custom SQL Scripts:
- Dangerous or destructive T-SQL that is entered as a custom counter is executed as entered, which could result in a negative impact on the linked monitored SQL Server instances.
- The
gocommand is not a Transact-SQL statement and you cannot use this command in custom SQL batches. The go command is a batch separator for Management Studio, Query Analyzer, sqlcmd, and osql. - Custom counter scripts run at every scheduled refresh and on-demand on the Custom Counters view. For best performance, custom scripts should run in under 30 seconds. If the script takes longer than two minutes to execute, timeouts may occur.
- Custom SQL scripts must return a single result set with a single numeric field. Additional fields or non-numeric values result in an error.
- Custom SQL scripts are displayed in Queries tab with "User Defined Counter" in their application name.
- The following options are set by default for custom counter scripts. If you need to use a different value, particularly another transaction isolation level, you should add the appropriate SET statement to the beginning of the script.
-
set transaction isolation level read uncommitted -
set lock_timeout 20000 -
set implicit_transactions off -
set language us_english -
set cursor_close_on_commit off -
set query_governor_cost_limit 0 -
set numeric_roundabort off
-
Access the Add Custom Counter wizard
You can open the Add Custom Counter wizard by clicking Administration > Custom Counters, and then clicking Add in the Custom Counters view.