Michael J. Swart

January 20, 2020

Watching SQL Server Stuff From Performance Monitor

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)
  select @deltaMeasurement = SomeMeasurement - @totalMeasurement
  from SomeTable;
  set @totalMeasurement += @deltaMeasurement;
  exec sp_user_counter1 @deltaMeasurement;
  waitfor delay '00:00:01'


Now you can monitor “User Counter 1” in the object “SQLServer:User Settable” which will look like this:
Example of monitoring a performance counter using Performance Monitor

Don’t forget to stop the running query when you’re done.

1 Comment »

  1. […] 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

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress