Provide custom SQL script

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 go command 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.