If you have ever needed to monitor a value in SQL Server, or follow the trend of anything that can be expressed in a numerical value – then creating a custom performance monitor counter could be just what you need….
For this example, I am going to use a query that calculates the size of the USERSTORE_TOKENPERM cache in SQL Server. This was an issue in SQL Server 2005 before SP3. The details of this are outlined in the following kb article:
FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
This issue has now been resolved, but it provides a perfect use and example of using a custom performance counter.
The query for finding the size of this cache is taken from the kb article and is below:
select sum(single_pages_kb+multi_pages_kb) 'total memory for tokeperm' from sys.dm_os_memory_clerks where type = 'USERSTORE_TOKENPERM'
We take the value returned by this query and store it in a variable as below:
declare @cache_size int;
set @cache_size = (select sum(single_pages_kb+multi_pages_kb) from sys.dm_os_memory_clerks where type = 'USERSTORE_TOKENPERM')
Now that we have the size of the cache, we use the first of 10 special stored procedures called sp_user_counter1 for our first counter. There are 10 sequentially numbered/named stored procedures up through sp_user_counter10 to allow you to have 10 unique custom performance monitor counters. Now, to update our counter, we simply call the stored procedure with our value:
declare @cache_size int;
set @cache_size = (select sum (single_pages_kb+multi_pages_kb)from sys.dm_os_memory_clerks where type = 'USERSTORE_TOKENPERM')
exec sp_user_counter1 @cache_size;
Then we can execute this piece of code every few minutes inside a SQLAgent job to continually update our new counter.
Next, we need to find the User Settable object and our counter in Performance Monitor:
Then, add that counter and we see our graph… The size of my cache is not changing here, so the line remains flat…
To show the graph reflects our value, we’ll 1/2 the value manually to see the line drop by adding the following to our TSQL code just before updating the counter:
set @cache_size = @cache_size / 2;
And now our graph is reduced by 1/2 as expected:
There are many possibilities for this cool, but little-known feature in SQL Server. However, use caution when executing queries that update this value – don’t create a large memory or I/O intensive queries that will degrade server performance every time it runs.
More information on the User Settable object and counters is available here: