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.

SQL Diagnostic Manager identifies and resolves SQL Server performance problems before they happen. Learn more > >