Performance counter Process SqlServ multiple sql instances

Question

Performance counter Process SqlServ multiple sql instances

franky.Piferoen

Say Hey Kid

Points: 692
More actions
February 26, 2015 at 5:49 am

#298052

When there are multiple sql instances installed on a server then for the performance Counter Process - %Processor time you have multiple sqlserv instances.
These are named sqlserv, sqlserv#1, sqlserv#2 ...
How can you make the link which process stands for which sql instance ?
E.g sqlserv = default sql instance, sqlserv#1 = NamedInstanceA, ...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

SELECT @@servicename on each instance will return the instance id, which is the suffix appended to the performance counter names.

-- Gianluca Sartori

franky.Piferoen Say Hey Kid Points: 692 More actions · Answer 2

Thx for the response but when I have a default Sql server MyServer and an extra sql instance Myserver\NamedInstance.

I get MSSQLSERVER and NamedInstance for @@servicename

So I am still missing the link to the process name sqlserv#n in perofmance monitor.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

I see what you mean. Can you please clarify which performance counter you're lokking at?

Processor - %Processor Time has a numeric instance for each logical processor and not for sqlserver instances.

-- Gianluca Sartori

Perry Whittle SSC Guru Points: 233848 More actions · Answer 4

to identify instance names to PIDs use either SQL server configuration manager or windows task manager.

In sql config manager the process id can be seen in the SQL Services window

For task manager go to the processes tab and add the columns "PID" and "Image path name"

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

franky.Piferoen Say Hey Kid Points: 692 More actions · Answer 5

Hi,

I have a default sql instance and 2 named instances on the server.

The PID's of the sqlserver.exe are 1672, 1644 and 1816.

The performance counter is Process, %processor time. (not Processor, %processor time)

Then you can select 'instances of selected object (= which process you want to monitor) where I find sqlservr, sqlservr#1 and sqlserv#2.

So I would like to know how to match the 'instances of selected object' from counter Process - %processor time to the sql instance names (or the PID's of the sqlserver.exe).