SQL Server Custom User Settable Perfmon Counters

The User Settable Object is one of SQL Server’s hidden gems, and knowledge of how to use and exploit it, is a must for any DBA. The long and short is that SQL allows you to gather custom perfmon counters in system monitor. You do this by calling sp_user_counter sproc series. I call this a series because there are 10 separate sprocs that map to 10 separate counters.

Sp Name Perfmon Name
sp_user_counter1 – SQLServer:User Settable/User counter 1
sp_user_counter2 – SQLServer:User Settable/User counter 2
sp_user_counter3 – SQLServer:User Settable/User counter 3
Etc to 10

These sprocs take one parameter which an integer value. This system sp leverages the undocumented dbcc command “dbcc setinstance” which in turn populates a transient system variable (I assume since it doesn’t survive a restart). When perfmon queries SQL Server for it’s counter it looks at the value set by sp_user_counter* to display in System Monitor. It’s very important to understand that the population of this variable only occurs when sp_user_counter* is called by an external application, not everytime perfmon queries the value. So if you have a heavy query to produce a value, it won’t be executed on the polling interval defined in perfmon. The example below populates User counter 1 with a value of 10. This value will stay in perfmon until a query is executed again.


EXECUTE sp_user_counter1 10

Review it in perfmon, you’ll notice User Counter 1 is now displaying a value of 10. If you run the command to assign the value to 100 this should display pretty much immediately

EXECUTE sp_user_counter1 100


Before moving on to a real world example


If your having a difficult time picturing how would you use this, lets give an example. Let’s say you have a server with 10 separate databases, on it and your perfmon logs show sporadic high cpu usage at night. One way to get at which database is causing this issue, is to look at memory usage during these cpu spikes. It’s safe to assume if one database is consuming more memory. You could schedule the following script to run every minute. Once you get this data you could compare it against the cpu spikes.


Declare @MemUsage1 int
Declare @MemUsage2 int
Declare @MemUsage3 int
--ETC

--Determine Memory Usage in MB
select @MemUsage1 = (count(*) * 8)/1024 from sys.dm_os_buffer_descriptors
where database_id = db_id('AdventureWorks')
select @MemUsage2 = (count(*) * 8)/1024 from sys.dm_os_buffer_descriptors
where database_id = db_id('DB_A')
select @MemUsage3 = (count(*) * 8)/1024 from sys.dm_os_buffer_descriptors
where database_id = db_id('DB_B')
--Etc

EXECUTE sp_user_counter1 @MemUsage1
EXECUTE sp_user_counter2 @MemUsage2
EXECUTE sp_user_counter3 @MemUsage3
--Etc