Home > SSIS Best Practices > SSIS – Monitoring Performance by Using Performance Counters

SSIS – Monitoring Performance by Using Performance Counters

The top 1 best practice in SSIS is to ensure that all transformations occur in memory.

image

(From Top 10 SQL Server Integration Services Best Practices)

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.

Here is an extract from this article Something about SSIS Performance Counters:

SQL Server Integration Services provide a set of performance counters. Among them the following few are helpful when you tune or debug your package:

  • Buffers in use
  • Flat buffers in use
  • Private buffers in use
  • Buffers spooled
  • Rows read
  • Rows written
Leaking Buffer?

“Buffers in use”, “Flat buffers in use” and “Private buffers in use” are useful to discover leaks. During package execution time, you will see these counters fluctuating. But once the package finishes execution, their values should return to the same value as what they were before the execution. Otherwise, buffers are leaked. In occasions like that, please contact Microsoft PSS.

Memory swapping? – Bad sign that the SSIS engine is hitting I/O

“Buffers spooled” has an initial value of 0. When it goes above 0, it indicates that the engine has started memory swapping. In a case like that, please follow my previous blog (“Set BLOBTempStoragePath and BufferTempStoragePath to Fast Drives”) to set Data Flow Task properties BLOBTempStoragePath and BufferTempStoragePath appropriately for maximal I/O bandwidth.

Execution progress – Monitor how many rows has been processed

“Rows read” and “Rows written” show how many rows the entire Data Flow has processed. They give you an overall idea about the execution progress.

Now the big question is how do we monitor those performance counters?

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, follow the steps here:

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

image

image

 

3) In the SSISLog dialog box, click Add Counters.

image

 

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

image

 

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

image

Finally, you should see the counters and their value fluctuations on the main System Monitor window during the execution of your SSIS package (my SSIS package is not running so you don’t see the counters in the screen shot).

 

image

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

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: