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 :

  • Performance counters are not working or missing in PerfMon
  • CORRUPT OR BROKEN PERFORMANCE COUNTERS – WINDOWS 2012
  • lost performance counters

  Thanks in advance for you help,
  Luc

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  March 19, 2018 at 12:23 pm

  #1983812

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

• luc.magnee

  SSC Veteran

  Points: 272

  More actions

  March 20, 2018 at 4:42 am

  #1983895

  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: 19135

  More actions

  March 20, 2018 at 11:21 am

  #1984003

  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

  April 17, 2018 at 6:48 am

  #1987127

  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...