Bagikan melalui

  • Artikel

Monitoring the Performance of the Data Flow Engine

Integration Services provides two methods for monitoring the performance of the data flow engine:

  • You can use logging to capture events that help you calculate execution times and identify performance issues in your packages.

  • You can monitor performance counters that measure multiple items related to data flow performance.

For information about how to improve performance, see Improving the Performance of the Data Flow.

Monitoring Performance by Using Logging to Capture Events

You can capture the PipelineComponentTime run-time event to troubleshoot data flow performance. For information about how to use logging to capture events, see Implementing Logging in Packages.

Monitoring Performance by Using Performance Counters

Integration Services installs a set of performance counters that you can use to monitor the performance of the data flow engine. For example, you can watch the "Buffers spooled" counter to determine whether data buffers are being written to disk temporarily while a package is running. This swapping reduces performance and indicates that the computer has insufficient memory.

Note

If you install Integration Services on a computer that is running Windows Server 2003, and then upgrade that computer to Windows Server 2008, the upgrade process removes the Integration Services performance counters from the computer. To restore the Integration Services performance counters on the computer, run SQL Server Setup in repair mode.

The following table describes the performance counters.

Performance counter

Description

BLOB bytes read

The number of bytes of binary large object (BLOB) data that the data flow engine has read from all sources.

BLOB bytes written

The number of bytes of BLOB data that the data flow engine has written to all destinations.

BLOB files in use

The number of BLOB files that the data flow engine currently is using for spooling.

Buffer memory

The amount of memory that is in use. This may include both physical and virtual memory. When this number is larger than the amount of physical memory, the Buffers Spooled count rises as an indication that memory swapping is increasing. Increased memory swapping slows performance of the data flow engine.

Buffers in use

The number of buffer objects, of all types, that all data flow components and the data flow engine is currently using.

Buffers spooled

The number of buffers currently written to the disk. If the data flow engine runs low on physical memory, buffers not currently used are written to disk and then reloaded when needed.

Flat buffer memory

The total amount of memory, in bytes, that all flat buffers use. Flat buffers are blocks of memory that a component uses to store data. A flat buffer is a large block of bytes that is accessed byte by byte.

Flat buffers in use

The number of flat buffers that the Data flow engine uses. All flat buffers are private buffers.

Private buffer memory

The total amount of memory in use by all private buffers. A buffer is not private if the data flow engine creates it to support data flow. A private buffer is a buffer that a transformation uses for temporary work only. For example, the Aggregation transformation uses private buffers to do its work.

Private buffers in use

The number of buffers that transformations use.

Rows read

The number of rows that a source produces. The number does not include rows read from reference tables by the Lookup transformation.

Rows written

The number of rows offered to a destination. The number does not reflect rows written to the destination data store.

You use the Performance Microsoft Management Console (MMC) snap-in to create a log that captures performance counters.

To add a new log that includes all performance counters for the data flow engine

  1. In Control Panel, click Administrative Tools, and then click Performance.

  2. In the Performance dialog box, expand Performance Logs and Alerts, right-click Counter Logs, and then click New Log Settings. Type the name of the log. For example, type MyLog.

  3. In the MyLog dialog box, click Add Counters.

  4. In the Add Counters dialog box, select SQL Server:SSISPipeline in the Performance object box.

  5. Select the All Counters check box, select Add, and then close the dialog boxes.

    Note

    You must start the Performance Logs and Alerts service using a local account or a domain account that is a member of the Administrators group.

External Resources

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.