Taking a small break from my blogging sabbatical to post one script that I’ve found myself writing from scratch too often.
My hope is that the next time I need this, I’ll look it up here.
The User Settable Counter
Use this to monitor something that’s not already exposed as a performance counter. Like the progress of a custom task or whatever. If you can write a quick query, you can expose it to a counter that can be plotted by Performance Monitor.
Here’s the script (adjust SomeMeasurement
and SomeTable
to whatever makes sense and adjust the delay interval if 1 second is too short:
declare @deltaMeasurement int = 0; declare @totalMeasurement int = 0; while (1=1) begin select @deltaMeasurement = SomeMeasurement - @totalMeasurement from SomeTable; set @totalMeasurement += @deltaMeasurement; exec sp_user_counter1 @deltaMeasurement; waitfor delay '00:00:01' end |
Monitoring
Now you can monitor “User Counter 1” in the object “SQLServer:User Settable” which will look like this:
Don’t forget to stop the running query when you’re done.
[…] Michael J Swart shows how you can create a Perfmon counter which tracks a user-defined value in SQL …: […]
Pingback by User-Defined Performance Counters in Perfmon – Curated SQL — January 21, 2020 @ 8:04 am