Sunday, February 21, 2010

Creating a custom performance monitor counter for SQL Server

image_thumb_1.png

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
http://support.microsoft.com/kb/933564

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.  

Read more: Jay's notes on SQL

Posted via email from jasper22's posterous