Using the sp_user_counterX stored procedure (where X is a number between 1 and 10) you can set internal SQL Server counters. This values are read by the Performance Monitor, the standard Windows utility that monitors system activity. The Performance Monitor is able to monitor not only the activities like these, but also other useful information for correctly measuring the database engine activity, through the counters labeled as “SQL Server: User Settable” and their instances (“User counter 1-10”). Thus the PM will display in real time these values, on the same or a remote machine. For example, the following T-SQL statements load the register #5 of user counters with the number of processes that are currently active:

declare @valore as integerset @valore = (select count(*) from sysprocesses)exec sp_user_counter1 @valore

########################################################

This tip has been originally published on Microsoft Italia’s web site.
It has been translated and re-published on VB2TheMax with the permission of Microsoft Italia.
You can find more tips like this one (in Italian) at http://www.microsoft.com/italy/sql/articoli

########################################################