SQLServer User-Defined Counters Object

The SQLServer User-Defined Counters object contains SQL User Counter 1 through SQL User Counter 10. Values for these counters can be defined by a user. A counter can monitor any information that is returned by an SQL statement or any operation that is performed from SQL Server, such as executing a stored procedure.

In SQL Server 6.5, "empty" stored procedures sp_user_counter1 through sp_user_counter10 correspond to the user-defined counters of the same number in SQL Performance Monitor. To create a user-defined counter, replace one of the empty stored procedures with a new stored procedure. The new stored procedure must return a single integer as its result set. The new stored procedure should have execute permission granted on it for the probe account.

    To create a counter stored procedure

1. Choose one of the stored procedures sp_user_counter1 through sp_user_counter10.
2. Drop the selected stored procedure.

   For example, to use sp_user_counter1, enter:

   DROP PROC sp_user_counter1
   go
     

3. Create a new stored procedure sp_user_counter1. The results set must return only one integer.

For example, you can create the following stored procedure to count the number of machines that are connected to SQL Server:

DROP PROC sp_user_counter1
go
CREATE PROC sp_user_counter1
AS SELECT count(distinct net_address) from sysprocesses 
go
  

The SELECT statement counts each distinct net_address that is connected to the server. As a result, the user-defined counter SQL User Counter 1 in SQL Performance Monitor will display the number of machines that are connected to SQL Server.

In the following example, user-defined counters are used to track stock prices of four stocks. For this example, assume stock_price has an int dataype and all stock prices are multiplied by 100.

DROP PROC sp_user_counter1
DROP PROC sp_user_counter2
DROP PROC sp_user_counter3
DROP PROC sp_user_counter4
go
CREATE PROC sp_user_counter1
AS SELECT stock_price from stocks where stock_name = 'A'
go
CREATE PROC sp_user_counter2
AS SELECT stock_price from stocks where stock_name = 'B'
go
CREATE PROC sp_user_counter3
AS SELECT stock_price from stocks where stock_name = 'C'
go
CREATE PROC sp_user_counter4
AS SELECT stock_price from stocks where stock_name = 'D'
go
  

As a result, in SQL Performance Monitor SQL User Counter1 through SQL User Counter4 will display the prices of stocks A, B, C, and D, respectively.