Performance

New SSIS Whitepapers from the SQLCAT Team

New SSIS Whitepapers from the SQLCAT Team

Two new SSIS SQL Server Technical Articles written by the SQLCAT team have been posted to the MSDN Library. They both focus on hybrid/cloud data movement, but they also contain design patterns and guidance that applies to on-premises data flows as well. The SSIS Operational and Tuning Guide has a great section on designing a … Continued

SSIS Performance Design Patterns

Here are the slides (all 177 of them) for the SSIS Performance Design Patterns talk that I’ve delivered at three different pre-conference events over the past month. Each presentation was slightly different, but the core content remained the same. These events included: SQL Saturday #105 | Dublin SQL Bits X, where I co-presented with John … Continued

Resources for SSIS Performance Best Practices

Simple post today. Here are a number of links to resources that I reference in my Performance Design Patterns presentation. Top 10 SQL Server Integration Services Best Practices The Data Loading Performance Guide Integration Services: Performance Tuning Techniques We Loaded 1TB in 30 Minutes with SSIS, and So Can You SSIS 2005: A Strategy for … Continued

Queries for Data Flow Component Performance in SQL 2012

Queries for Data Flow Component Performance in SQL 2012

The built-in SSIS Catalog reports in SSMS show a breakdown of task and component performance of a package execution when you run with a logging level of “Performance”. For a more detailed or customized view, you can query the catalog tables directly. The key table to get data flow performance information is [catalog].[execution_component_phases]. Here are … Continued

How Can I Improve the Performance of my Destination Component

Question: I’ve created a custom destination component, but I’m not happy with the performance. I tried using multiple threads, but the pipeline buffer doesn’t allow free traversal. What can I do?   I’d start by profiling the component to make sure you identify the correct bottlenecks. The most common source of problems is accessing the … Continued

Too Many Sources in a Data Flow

Too Many Sources in a Data Flow

This was a recent customer issue that I wanted to share. The customer was transferring a large amount of data between two tables, without performing any transformations on it. They wanted to run multiple transfer streams in parallel, and created a way to split up their source data into six partitions. In their original design, they had … Continued