Performance Counters In SSIS

 

There are a few key performance counters to watch when you’re trying to monitor the performance of your SSIS package. These counters greatly help you troubleshoot issues if you have memory contention or you need to tweak your SSIS settings. Inside the System Monitor (also known to old-school administrators as perfmon) is a performance object called SQLServer: SSIS Pipeline. (There are quite a few other objects as well, but they are not useful enough to describe here.)

Learn automate maintenance of SQL Server database, Using SSIS. Join Mindmajix SSIS Training and Get Started Today!

If you’re trying to benchmark how many rows are being read and written, you can use the Rows Read and Rows Written counters. These counters show you the number of rows since you starting monitoring the packages. It sums all rows in total across all packages and does not allow you to narrow the results down to a single package.

MindMajix Youtube Channel

The most important counters are the buffer counters. The Buffer Memory counter shows you the amount of memory, in total, being used by all the packages. The Buffers In Use counter indicates how many buffers are actually in use. The critical counter here, though, is Buffers Spooled. This shows you how many buffers have been written from memory to disk. This is critical for the performance of your system. If you have buffers being spooled, you have a potential memory contention, and you may want to consider increasing the memory or changing your buffer settings in your package. This is covered in more detail in Understanding and Tuning the Data Flow Engine Topic, but you should never see this number creep above a 5 if not 0.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses:

 SSRS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

 

Course Schedule
NameDates
SSIS TrainingOct 12 to Oct 27View Details
SSIS TrainingOct 15 to Oct 30View Details
SSIS TrainingOct 19 to Nov 03View Details
SSIS TrainingOct 22 to Nov 06View Details
Last updated: 28 Sep 2024
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read less
  1. Share:
SSIS Articles