SSIS 2012 issue with performance counters showing only "0" values

Question

Post reply

SSIS 2012 issue with performance counters showing only "0" values

luc.magnee

SSC Veteran

Points: 272
More actions
March 18, 2018 at 9:03 am

#410331
I'm working on the performance tuning of a big project involving SSIS 2012. The vmware servers are running under Windows Server 2012 R2 and the counters under SQLServer:SSIS Pipeline 11.0 and SQLServer:SSIS Service 11.0 show nothing but "0" while running the query here under definitely shows SSIS activity on the server with counters Buffers in use, Rows read, Rows written, Buffer memory, BLOB bytes read and BLOB bytes written showing non-zero values.
  USE [SSISDB]
    GO
SELECT
        [execution_id]
        ,[counter_name]
        ,[counter_value]
    FROM [catalog].[dm_execution_performance_counters](NULL)
Has anyone faced the same issue ? In the affirmative, did you find a solution to this issue ? I've already tried some solutions like the ones below, but nothing helps :
Thanks in advance for you help,
Luc

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

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

Martin Schoombee SSCoach Points: 19116 More actions · Answer 1

Could it be that your packages aren't logging at the required granularity?

luc.magnee SSC Veteran Points: 272 More actions · Answer 2

Hi Martin,

Thanks for your reply !

I would think that if the SSISDB catalog function dm_execution_performance_counters () returns non-zero value for a series of counter names that :

The granularity of the SSISDB logging is at the required level
That exact same information would be forwarded to the Performance Monitor

It's very difficult to find any information about this subject. I was thinking that this issue might be another virtual machine perf counter problem...

Cheers,
Luc

Martin Schoombee SSCoach Points: 19116 More actions · Answer 3

You would think (hope) that will be the case, but I'm not sure that it uses the same platforms to gather data. I would be inclined to use the system views for pipeline information and perfmon for OS-level info.

luc.magnee SSC Veteran Points: 272 More actions · Answer 4

Hi Martin,
It's been some time but I thought to come back and confirm that to be able to use Performance Monitor counters for SSIS Pipeline (the other one counting the number of concurrently running package just doesn't work), you need to set the logging level of SSISDB to either "Performance", but in that case almost no execution/operation information can be retrieved from the catalog, or "Verbose", and in this case you drown in a mass of information...until your package has run for a few hours : in this last case same as "Performance" but this time because of an "out-of-memory" error.
I can just hope that with customizable logging levels in SQL Server 2016, you'll be able to get performance info from Perf Mon AND retrieve a decent level of information from queries on the catalog...