The User Settable object in Microsoft® SQL Server™ allows you to create custom counter instances. Creating your own user-defined counter instances is useful in monitoring aspects of the server not monitored by existing counters, such as components unique to your SQL Server database (for example, determining the number of customer orders logged or the product inventory).
The SQL Server User Settable object contains 10 instances of the query counter: User counter 1 through User counter 10. User counters 1 through 10 map to the SQL Server stored procedures sp_user_counter1 through sp_user_counter10. As these stored procedures are executed by user applications, the values set by the stored procedures are displayed in Windows NT Performance Monitor. A counter can monitor any single integer value, for example, a stored procedure that counts how many orders for a particular product have occurred in one day.
Note The user counter stored procedures are not polled automatically by Windows NT Performance Monitor. They must be explicitly executed by a user application for the counter values to be updated. This is different from earlier versions of SQL Server in which the user-defined counter query was executed whenever Windows NT Performance Monitor needed to update the counter. Similar functionality can now be achieved by using a trigger to automatically update the value of the counter. For example, to create a counter that monitors the number of rows in a table, create an INSERT and DELETE trigger on the table that executes:
SELECT COUNT(*) FROM table
Whenever the trigger is fired because of an INSERT or DELETE operation occurring on the table, the Windows NT Performance Monitor counter is automatically updated.
These are the SQL Server User Settable counters.
| SQL Server User Settable counters | Description |
|---|---|
| Query | Defined by the user. |
To make use of the user counter stored procedures, simply execute them from your own application with a single integer parameter representing the new value for the counter. For example, to set User counter 1 to the value 10, execute this Transact-SQL statement:
EXECUTE sp_user_counter1 10
The user counter stored procedures can be called from anywhere that other stored procedures can be called from, such as your own stored procedures. For example, you can create the following stored procedure to count the number of connections and attempted connections made since SQL Server was started:
DROP PROC My_Proc
GO
CREATE PROC My_Proc
AS
EXECUTE sp_user_counter1 @@CONNECTIONS
GO
The @@CONNECTIONS function returns the number of connections or attempted connections since SQL Server was started. This value is passed to the sp_user_counter1 stored procedure as the parameter.
Important The queries defined in the user counter stored procedures should be as simple as possible. Queries that are expensive to execute, for example, queries that perform large amounts of I/O or memory-intensive queries that perform substantial sort or hash operations, can impact performance.