Wednesday, August 31, 2016

Perfmon SSIS Counters missing? Try running as admin...

A colleague and I were hoping to review SSIS perfmon counters on a VM.  We use a logman command with a counters file to log perfmon to csv.

Opened up the csv that was captured on the VM... there were all of my typical SQL Server counters... but the following SSIS counters were missing.

\SQLServer:SSIS Service\SSIS Package Instances
\SQLServer:SSIS Pipeline\Buffer memory
\SQLServer:SSIS Pipeline\Buffers in use
\SQLServer:SSIS Pipeline\Buffers spooled
\SQLServer:SSIS Pipeline\Flat buffer memory
\SQLServer:SSIS Pipeline\Flat buffers in use
\SQLServer:SSIS Pipeline\Private buffer memory
\SQLServer:SSIS Pipeline\Private buffers in use
\SQLServer:SSIS Pipeline\Rows read
\SQLServer:SSIS Pipeline\Rows written


Got on the vm, and used:
typeperf -q | find "SSIS"

That command returned one line:
\SQLServer:SSIS Service 11.0\SSIS Package Instances


I recalled recently learning that the VMware perfmon counters require privilege to display.  As I prattled on about that, my colleague simply launched a cmd.exe as an administrator.

In that elevated cmd.exe, the previous typeperf command returned all of the SSIS counters we expected.

Except they were all versioned, so we updated the counters file like so:
\SQLServer:SSIS Service 11.0\SSIS Package Instances
\SQLServer:SSIS Pipeline 11.0\Buffer memory
\SQLServer:SSIS Pipeline 11.0\Buffers in use
\SQLServer:SSIS Pipeline 11.0\Buffers spooled
\SQLServer:SSIS Pipeline 11.0\Flat buffer memory
\SQLServer:SSIS Pipeline 11.0\Flat buffers in use
\SQLServer:SSIS Pipeline 11.0\Private buffer memory
\SQLServer:SSIS Pipeline 11.0\Private buffers in use
\SQLServer:SSIS Pipeline 11.0\Rows read
\SQLServer:SSIS Pipeline 11.0\Rows written

With the perf counters file updated, and executing the logman command as an administrator, all of the expected counters appeared in our csv.

Now I should point out: there are other reasons the counters may be missing.  Sometimes they need to be reloaded with a lodctr command, like the following post.

Perfmon Counters for SSIS Pipeline

But that'll have to be a story for another day.


No comments:

Post a Comment