Monitoring Decimal Values on Perfmon Using User Settables

sql serversql-server-2012t-sql

I am currently using sp_user_counter1 to store database information on Perfmon, which can be recovered by AWS CloudWatch, but the values are Decimal, and the sp_user_counter1 parameter supplied must be an int.

So when I give the stored procedure a value of 12.34567 it report 12.00000 to the perfmon counter….

The sp code is:

    CREATE PROCEDURE sys.sp_user_counter1 @newvalue INT AS
    DBCC setinstance ('SQLServer:User Settable', 'Query', 'User counter 1', @newvalue)

Can I just call DBCC setinstance and give it the decimal value???
I never tried this because I didnt found anyone doing this, I dont know the consequences….

Can this be done?? Is this secure??

Here is the User Setabble documentation:
https://msdn.microsoft.com/en-us/library/ms187480.aspx

Best Answer

Running this:

CREATE PROCEDURE dbo.set_user_counter1 
(
    @newvalue DECIMAL(18,2)
)
AS
BEGIN
    DBCC setinstance (
        'SQLServer:User Settable'
        , 'Query'
        , 'User counter 1'
        , @newvalue
    );
END;
GO

EXEC dbo.set_user_counter1 12.5

results in:

Msg 2560, Level 16, State 9, Procedure set_user_counter1, Line 7
Parameter 4 is incorrect for this DBCC statement.

Running the following shows you the "help" entry for the DBCC setinstance:

DBCC TRACEON (2588); /* show DBCC Help */
DBCC TRACEON (2520); /* show undocumented command help */
DBCC HELP ('setinstance')

This returns:

dbcc setinstance (objectname, countername, instancename, value)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Unfortunately, that is no help determining if value can only be an int.

In fact, the MSDN page you linked states the following:

A counter can monitor any single integer value

Related Question