ssis行计数变量_SSIS管道性能计数器指南

ssis行计数变量

SSIS管道性能计数器 (SSIS Pipeline performance counters)

SSIS Pipeline performance counters monitor the processes which are related to the execution of packages and the Data flow engine’s the most crucial feature, the (Data) Pipeline. Their measurements reveal in which way the memory resources are acquired during the execution of the SSIS packages, and also, show the amount of memory used during that events. The proficient monitoring of the SSIS Pipeline memory usage can mitigate the potential issues of memory and data leakage, data transformation interruptions and overall, avoid data integration damage.

SSIS管道性能计数器监视与程序包执行和数据流引擎最关键的功能（数据）管道有关的过程。 他们的测量结果揭示了在执行SSIS包期间以哪种方式获取内存资源，还显示了该事件期间使用的内存量。 对SSIS管道内存使用情况的熟练监控可以减轻潜在的内存和数据泄漏，数据转换中断以及总体问题，避免数据集成损坏。

It should be noted that measurements of these performance counters are always aggregated, and they show the performance of the entire SSIS service, not just package execution.

应该注意的是，这些性能计数器的度量总是聚合在一起的，它们显示了整个SSIS服务的性能，而不仅仅是软件包的执行。

Performance counters of SSIS Pipeline type are grouped in six groups:

SSIS管道类型的性能计数器分为六组：

1. Packages execution

   包执行

2. BLOB

   BLOB

3. Buffer

   缓冲

4. Private buffer

   专用缓冲区

5. Flat buffer

   平面缓冲器

6. Rows performance counters

   行性能计数器

1. Package execution performance counters

   包执行性能计数器

   • SSIS Package Instances: this counter shows current number of simultaneous SSIS Packages running.

     SSIS包实例 ：此计数器显示当前正在运行的同时SSIS包的数量。

   Note: To determine issues related to multiple packages execution, constructing the flow of main packages and subpackages in proper order should be applied (when executing dependent packages e.g.), so all of the transformations fit in memory while processing. Also, the EngineThreads property can be set in according to the number of package executions (threads) needed. The SSIS engine itself will not use more threads than needed, and it will regard the set property’s value.

   注意：要确定与多个程序包执行相关的问题，应采用以正确的顺序构造主程序包和子程序包的流程（例如，在执行相关程序包时），以便在处理时所有转换都适合内存。 另外，可以根据所需的程序包执行（线程）数来设置EngineThreads属性 。 SSIS引擎本身不会使用超出所需数量的线程，它将考虑set属性的值。

   Default value for this counter that is showing in aggregated measurements is -1, which represents the maximum number of package executions, in addition with number of processors, plus two. For example, if packages are executing with quad core processor, the value is 6.

   在总计测量中显示的此计数器的默认值为-1 ，它表示包执行的最大数量，加上处理器数量加两个。 例如，如果程序包使用四核处理器执行，则值为6 。

2. BLOB performance counters

   BLOB性能计数器

   • BLOB bytes read: it displays the sum of bytes read from the BLOB data (Binary Large OBjects) from the all accessible sources in monitoring cycle.

     BLOB字节读 ：它显示的字节从在监视周期中的所有可访问的源的BLOB数据（B inary 大号 ARGE OB jects）读出的总和。

   • BLOB bytes written: this performance counter shows the sum of bytes written to BLOB on all targeted destinations in monitoring cycle.

     写入的BLOB字节 ：此性能计数器显示在监视周期内所有目标目标上写入BLOB的字节总和。

   • BLOB files in use: it displays the number of BLOB files in use (for spooling), during Data flow engine task in period of monitoring.

     正在使用的BLOB文件：在监视期间的数据流引擎任务期间，它显示正在使用（用于假脱机）的BLOB文件的数量。

   Note: BLOB-based temporary data can easily overwhelm one single buffer during the execution of packages, so, the temporary storage is needed to be set just for BLOBs processing. It could slip over to the logical disk storage, if physical RAM is 100% allocated, which slows down the performance of SSIS Pipeline. In order to optimize the usage of that data during the package execution, the BLOBTempStoragePath in parallel with the BufferTempStoragePath properties should be set for particular columns that contain BLOBs, otherwise, the default values will be applied for TEMP variables.

   注意：基于BLOB的临时数据在执行程序包期间很容易使单个缓冲区不堪重负，因此，仅为BLOB处理需要设置临时存储。 如果已将物理RAM分配为100％，它可能会转移到逻辑磁盘存储中，这会降低SSIS Pipeline的性能。 为了在包执行过程中，以优化数据的使用中，与BufferTempStoragePath属性平行脱机BLOBTempStoragePath应该包含BLOB，否则，使用默认值将应用于TEMP变量特定列进行设置。

   Values that are displaying in aggregated measurements are byte strings, numeric values which represent the allocated space on logical disk in bytes (e.g. 2.147.483.648 bytes, which are 2 GB).

   聚合测量中显示的值是字节字符串，数字值表示以字节为单位的逻辑磁盘上已分配的空间（例如2.147.483.648字节，为2 GB）。

3. Buffer performance counters

   缓冲区性能计数器

   By default, the Integration Services service runs independently from the SQL Server service instance, optimizing and allocating the memory resources for itself when multiple data transformations are occurring. This buffer-oriented framework (where the buffer is defined as a manageable memory object which holds a data) makes the whole process highly efficient. Therefore, the SSIS Pipeline performs in-memory data processing.

   默认情况下，Integration Services服务独立于SQL Server服务实例运行，并在发生多次数据转换时为其自身优化和分配内存资源。 这种面向缓冲区的框架（将缓冲区定义为保存数据的可管理内存对象）使整个过程非常高效。 因此，SSIS管道执行内存中数据处理 。

   It begins with reading a set of data (rows) from the source, then loading selected data in a buffer, transforming the data and writing to the destination. Processing the large amount of data is common in a BI environment, and, as such, it requires using a certain number of buffers (managed by internal SSIS buffer manager), instead of loading the data on physical storage. In accordance to the transformation and already transformed (processed) data, buffers are being reallocated by other buffers which are part of the parallel transformation process (asynchronous transformation), or being deallocated and ready for the next data transformation process.

   首先从源中读取一组数据（行），然后将选定的数据加载到缓冲区中，然后转换数据并写入目标。 处理大量数据在BI环境中很常见，因此，它需要使用一定数量的缓冲区（由内部SSIS缓冲区管理器管理），而不是将数据加载到物理存储上。 根据转换和已经转换（已处理）的数据，缓冲区将由并行转换过程（异步转换）的一部分其他缓冲区重新分配，或者被释放并准备进行下一个数据转换过程。

   • Buffers in use: this displays and details how many pipeline buffers are being used by Data flow engine components during the monitoring cycle.

     正在使用的缓冲区 ：显示并详细说明在监视周期内，数据流引擎组件正在使用多少个管道缓冲区。

   • Buffer memory: this performance counter shows the amount of memory that is currently used in monitoring cycle, including both virtual and physical instances that regard the Data flow engine. If the Buffer memory value is higher than actual physical memory, then the Buffers spooled value will show increased memory swapping (exchange between RAM and physical storage).

     缓冲内存 ：此性能计数器显示监视周期中当前使用的内存量，包括与数据流引擎有关的虚拟实例和物理实例。 如果“缓冲区内存”值高于实际的物理内存，则“缓冲区假脱机”值将显示增加的内存交换（RAM和物理存储之间的交换）。

   • Buffers spooled: this displays the number of buffers currently written to logical disk during the data flow engine process during the period of monitoring. Buffers that aren’t currently used are always loaded to storage and reloaded when needed, typically when the data flow engine is running low on physical memory.

     假脱机缓冲区 ：显示监视期间在数据流引擎过程中当前写入逻辑磁盘的缓冲区数。 当前不使用的缓冲区始终会加载到存储中，并在需要时重新加载，通常是在数据流引擎的物理内存不足时使用。

     If the value of Buffers Spooled counter is 0, the transformation is occurring only in memory, without writing any data on a disk, and if that value is higher than 0, it is a sign that process needs more memory for the package execution.

     如果Buffers Spooled计数器的值为0 ，则仅在内存中进行转换，而不会在磁盘上写入任何数据；如果该值大于0 ，则表明进程需要更多的内存来执行程序包。

   Note: Default buffer-allocated size of a memory is 10MB, and the maximum size is 2GB (both for 32-bit and 64-bit systems). Therefore, the values for Buffer counters (except Buffers spooled) are showing in bytes (as numeric value) in aggregated measurements.

   注意 ：内存的默认缓冲区分配大小为10MB，最大大小为2GB（对于32位和64位系统）。 因此，在汇总测量中，缓冲区计数器的值（假脱机缓冲区除外）以字节（作为数值）显示。

4. Private buffer performance counters

   专用缓冲区性能计数器

   Instead of being created by an SSIS instance, private buffers are created by particular transformation process themselves. Their purpose is to support and optimize the performance of temporary tasks in Pipeline, like the Aggregation transformation, Sort transformation etc.

   私有不是由SSIS实例创建的，而是私有的 缓冲区是由特定的转换过程自己创建的。 它们的目的是支持和优化管道中的临时任务的性能，例如聚合转换，排序转换等。

   • Private buffer memory: this performance counter shows the full amount of physical or virtual memory used by all of the current private buffers within the Data Flow engine task in the monitoring cycle. During a data transformation, if the Data flow engine creates a buffer to support itself (not the transformation), that buffer is not considered as private. Value in aggregated measurements for this counter is showing in bytes (as a numeric value, e.g. 100.000.000 bytes, which is 100 MB).

     专用缓冲区内存 ：此性能计数器显示监视周期中“数据流”引擎任务中所有当前专用缓冲区使用的物理或虚拟内存的总量。 在数据转换期间，如果数据流引擎创建了一个缓冲区来支持自身（而不是转换），则该缓冲区不被视为私有。 此计数器的合计测量值以字节显示（作为数值，例如100.000.000字节，即100 MB）。

   • Private buffers in use: this displays the amount of blocks of memory that data transformations use for itself certain point in time at a certain point in time.

     正在使用的专用缓冲区 ：这将显示数据转换在特定时间点在特定时间点上用于其自身的内存块数。

     Value in aggregated measurements for this counter is displaying as a numeric value (e.g. 2 memory blocks).

     该计数器的汇总测量值显示为数值（例如2个存储块）。

5. Flat buffer performance counters

   平面缓冲区性能计数器

   Flat buffers are the special type of private buffers, which use blocks of memory independently. Transformation processes that occur in flat buffers process intact blocks of data, undivided into rows/columns.

   平面缓冲区是专用类型的专用缓冲区，它们独立使用内存块。 在平面缓冲区中发生的转换过程将处理完整的数据块，这些数据块未分为行/列。

   • Flat buffer memory: this shows which amount of memory that all of flat buffers allocate in the monitoring cycle.

     平面缓冲存储器 ：显示监视周期中所有平面缓冲区分配的内存量。

   • Flat buffers in use: this performance counter calculates the amount of memory blocks used by the Data flow engine task at a certain point in time.

     正在使用平面缓冲区 ：此性能计数器计算数据流引擎任务在特定时间点使用的内存块数量。

   Note: Value in aggregated measurements for these counters is displaying as a numeric value, same as for the each of Private buffer performance counters.

   注意 ：这些计数器的合计测量值显示为数值，与每个专用缓冲区性能计数器相同。

6. Rows performance counters

   行性能计数器

   These performance counters provide information about the progress of package execution.

   这些性能计数器提供有关程序包执行进度的信息。

   • Rows read: this counter calculates the full amount of rows during transformation which are read from the source at a certain point in time. They are also called “input rows”, and the rows read from reference tables (within Lookup transformation) are not included by default.

     读取的行数 ：此计数器计算转换期间在特定时间点从源读取的全部行数。 它们也称为“输入行”，并且默认情况下不包括从参考表读取的行（在Lookup转换内）。

   • Rows written: this displays the total number of rows that are written to the destination. They are also called the “output rows”.

     已写入的行数：这将显示写入目标的总行数。 它们也称为“输出行”。

   Note: The values that are showing in aggregated values can be from 0 to 10.000, because the default maximum number of processed rows per transformation is 10,000.

   注意 ：聚合值中显示的值可以是0到10.000 ，因为每个转换的默认最大处理行数是10,000。

用于监视的重要SSIS管道性能计数器 (Important SSIS Pipeline performance counters for monitoring)

For discovering the source of memory or data leakage, Buffers in use, Private buffers in use and Flat buffers in use performance counters should be in focus. Their values at the beginning of the packages execution should be the same in the end of the process, otherwise, buffers may be “leaking” memory and data integration could be damaged.

为了发现内存或数据泄漏的源，应重点关注正在使用的 缓冲区，正在使用的专用缓冲区和正在使用的 平面缓冲区性能计数器。 在程序包执行开始时，它们的值在过程结束时应相同，否则，缓冲区可能“泄漏”了内存，并且可能损坏了数据集成。

In addition, Rows Read and Buffers Spooled are also the important SSIS Pipeline performance counters, because they provide the insight for which amount of transformed data and to point on the potential risk of memory leakage and impact on the logical disks.

此外，“ 行读取”和“ 缓冲池”也是重要的SSIS流水线性能计数器，因为它们提供了有关已转换数据量的信息，并指出了内存泄漏和对逻辑磁盘的影响的潜在风险。

To understand better what is happening while data is transformed and processed in-memory, from the source to the destination, including the schema of theoretical Performance counter monitoring solution see the illustration below.

为了更好地了解从源到目的地在内存中进行数据转换和处理时发生的情况，包括理论性能计数器监视解决方案的架构，请参见下图。

有用的链接 (Useful links)

• Performance Counters 性能计数器
• Data Flow Performance Features 数据流性能功能
• SSIS Service performance counter guide SSIS服务性能计数器指南

翻译自: https://www.sqlshack.com/ssis-pipeline-performance-counters-guide/

ssis行计数变量