INF: User-Defined Counters Do Not Show Up Correctly in Perfmon (161347)

SUMMARY

Microsoft SQL Server version 6.5 allows you to define your own performance counters. See the "SQL Server User-Defined Countered Object" section in the SQL Server Books Online for complete details.

The documentation states that the stored procedure you create should return a single integer as a result. However, you can generate multiple result sets in a stored procedure. In that case, the SQL Server performance DLL (Sqlctr60.dll) interprets each result set as a counter.

MORE INFORMATION

The following is an example of a stored procedure that generates multiple result sets:

   create procedure spTest
   as
      select 1
      select 2
   go

If your procedure generates two result sets, counter 1 may appear in counter 2.

You can use the "set nocount on" command to reduce the number of DONE_IN_PROC messages. You can also use trace flag 3640. The following is an excerpt from the Trace Flags section of the SQL Server Books Online.

Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting NOCOUNT, but when set as a trace flag every client session is handled this way.