If I am running multiple instances of SQL Server, how do I figure out which instance of the sqlservr.exe process data maps to which SQL Server instance?

On machines where multiple instances of SQL Server are installed, you will see multiple instances of the sqlservr.exe process running. You will need a procedure for associating the performance data at the SQL Server process level — which provides processor utilization statistics and overall memory consumption — with the SQL Server performance counters for that instance of the database.

One way to accomplish that is to use one of the User Settable counters that are available in SQL Server to return the process ID of that instance of SQL Server, which can be accessed by executing the SERVERPROPERTY statement in a stored procedure. The process ID provided in the User Settable counters can then be matched against the ID Process field of the corresponding instance of the sqlservr Process record.

Follow these steps to output the process ID in one of the User Settable counters:

	Add the MSSQL:User Settable object to the data collection set that are using to gather performance data from your SQL Server machines. Assign and activate this collection set.
	To set a value for one of the User Settable counters, define and execute a stored procedure that executes one of the system stored procedures in the Master database named sp_User_Counter1 through sp_User_Counter10 so that it contains the value of the Process ID from an execution of the SERVERPROPERTY statement, as follows:

declare @ProcessID as integer
set @ProcessID = (SELECT CONVERT(char(20), SERVERPROPERTY(‘ProcessID’)))
exec sp_user_counter1 @ProcessID

This example sets the value of the User Settable object’s User Counter 1 to the Process ID for that instance of SQL Server. Check the box to indicate that you want this stored procedure to be triggered to execute whenever this instance of SQL Server starts.

After you have created this stored procedure, it will look similar to this:

Note that the SQL Server User Settable User Counters can only report numeric data, which is why the ProcessID from SERVERPROPERTY must be explicitly converted to an integer value before you call one of the sp_User_Counter stored procedures. For example, if you would also like the SQL Server version information available, you could add the following to your store procedure:

declare @ProductVersion as integer
set @ProductVersion = (SELECT CONVERT(char(20), SERVERPROPERTY(‘ProductVersion’)))
exec sp_user_counter2 @ProductVersion

The Product Version information that the SERVERPROPERTY statement returns is a character string with a value something like “8.00.194”, that this SQL statement converts to the closest integer value, which is 8.