How does Perfmon gather SQL Server statistics?

[Patrick Shroads]

I am trying to figure out how perfmon retrieves values for SQL Server
counters -- for instance the number of user connections or the number
of locks. I would think that SQL Server would have to be queried for
that information but perfmon doesn't seem to connect to SQL Server at
all. If I run a Profiler trace and then gather SQL Server counters in
perfmon I see nothing that looks like it's perfmon connecting to SQL
Server. If I do a "netstat -a" on the remote machine running SQL
Server and then connect to it with perfmon the only connection from my
machine is:

Proto Local Address Foreign Address State
TCP devsql:netbios-ssn ppc-025932:1924 ESTABLISHED

Any idea where the SQL Server counter data is being retrieved from?

Thanks

 

[Greg Linwood]

Hi Patrick

As SQL Server runs, it publishes performance data in two ways - Windows
Performance Objects & Counters (which Perfmon collects via the Win32
Performance APIs) and via SQL Trace (which provides queues which SQL
Profiler connects to / collects its data from).

The Windows Performance Objects & Counters are a standardised way for
Windows applications to publish performance data & SQL Trace is a
proprietary mechanism for SQL Server to publish trace data. Any application
can create Performance Objects / Counters via the Win32 API.

So, Perfmon doesn't connect to SQL Server at all - it calls the Windows APIs
on a regular basis, picking up the counter values at each call as they were
last published by SQL Server.

HTH

Regards,
Greg Linwood
SQL Server MVP