Performance

Data Flow Optimization Checklist

Data Flow Optimization Checklist

I put this together for an SSIS performance presentation I did a while ago, and thought it warranted its own post. In addition to looking for appropriate Design Patterns, I have a checklist of things to look for when trying to optimize an SSIS Data Flow. This list is by no means extensive, but it … Continued

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

Lookups with really big reference tables

Lookups with really big reference tables

Recent customer issue: We are using the Lookup Transform to do joins in our Data Flows. Most Data Flows have more than one Lookup. The process worked fine in our development environment, but fails when we run in production because the source of the lookups have between 4-6 billion records each. How can we resolve … Continued

PASS Summit 2012 – SSIS Pre-Conference Event

PASS Summit 2012 – SSIS Pre-Conference Event

Well, I guess Andy, Tim & I did a good job at PASS last year, as we’ve been invited back to present another SSIS pre-conference event at the 2012 PASS Summit. This time we’ll be joined by the co-authors of our SSIS Design Patterns book, Jessica Moss and Michelle Ufford.   SSIS Design Patterns Speaker(s): … Continued

Should I use the SQL Server Destination?

Short answer: No. Long answer: Noooooooooooooooooooooooooooooooooooooooooooooooooo. UPDATE: I was told that some people would like an explanation instead of blindly trusting my advice (weird), so here goes: In SQL 2005 RTM, the SQL Server Destination was the fastest way to load data into SQL Server with SSIS. Somewhere along the way (in the 2005 SP2 … 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

Data Quality Services Performance Best Practices Guide – now available!

The Data Quality Services (DQS) Performance Best Practices Guide (or DQSPDPG for short) is now available on the Microsoft Download Center. It covers hardware and setup considerations, how matching policies will impact your performance, and some best practices when using the DQS Cleansing transform in SSIS. I was also happy to see a section in … 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