SQL Server provides a wide range of performance counters that allow you to monitor various parameters of the SQL Server engine. These performance counters can be extremely useful when analyzing the performance of your queries and databases. However, have you ever wondered if it’s possible to see the value of a performance counter for a specific query that you have executed? In this blog post, we will explore the concept of user settable counters in SQL Server and how they can be used to plot custom values against standard performance counters.

User settable counters are a set of ten system procedures available in SQL Server, namely sp_user_counter1, sp_user_counter2, and so on up to sp_user_counter10. These procedures allow users to set specific counter values that can be plotted against standard performance counters. The counter object name and the stored procedure names are the same, making it easy to associate a specific counter with its corresponding stored procedure.

Let’s take a look at a quick example to demonstrate the usage of user settable counters:

-- Drop database, if already present
USE MASTER
GO

IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SQLAuthority')
BEGIN
    ALTER DATABASE SQLAuthority SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE SQLAuthority;
END
GO

-- Create New Database
CREATE DATABASE SQLAuthority
GO

-- Create Objects Needed for Demo
USE SQLAuthority
GO

CREATE TABLE TrackMe (i INT)
GO

-- while loop to populate the data and populate the counter
SET NOCOUNT ON

DECLARE @NumberOfRows INT = 0, @NumberOfRows_2 INT = 0

WHILE (@NumberOfRows < 25)
BEGIN
    INSERT INTO TrackMe VALUES (1), (2), (3)
    SELECT @NumberOfRows = COUNT(*) FROM TrackMe
    EXECUTE sp_user_counter1 @NumberOfRows -- Counter 1: Number of rows in the table
    WAITFOR DELAY '00:00:02'
    
    SELECT @NumberOfRows_2 = @NumberOfRows * 2
    EXECUTE sp_user_counter2 @NumberOfRows_2 -- Counter 2: Double the number of rows
    WAITFOR DELAY '00:00:02'
END

-- Loop finished, reset the counters
EXECUTE sp_user_counter1 0
EXECUTE sp_user_counter2 0

In this example, we create a new database called SQLAuthority and a table called TrackMe. We then use a while loop to populate the table with data and update the user settable counters. Counter 1 represents the number of rows in the table, while Counter 2 represents double the number of rows. We introduce a delay of 2 seconds between each counter update to visualize the steps. Finally, we reset the counters to zero.

By executing the above code and monitoring the performance counters, you will be able to see the values plotted on the performance counter graph. This allows you to visualize the changes in the number of rows in the table over time.

It’s worth mentioning that the user settable counters can also be set using the undocumented command DBCC SETINSTANCE. This provides an alternative way to update the counter values directly.

Now that you understand the concept of user settable counters, you can explore more innovative ways to utilize them in your SQL Server environment. For example, you can plot the execution time of specific queries against the number of rows affected, or monitor the fragmentation level of a table over time. The possibilities are endless!

Remember, user settable counters are a powerful tool for monitoring and analyzing the performance of your SQL Server environment. They provide a way to customize and plot specific values against standard performance counters, giving you deeper insights into the behavior of your queries and databases.