SQL Server Integration Services Performance Tuning

Synopsis

This one day course is designed for those people who have seen how fast SQL Server Integration Services can transfer data but want more. We will show you how to understand performance not only from within SSIS but also from without. We take a good look under the covers to see what SSIS thinks and we will also see the effects of design decisions you make.

SSIS out of the box is wonderfully fast but with understanding that speed can be turned up. In today's world batch windows are becoming smaller and data quantity ever larger so having a truly performing ETL solution is an extremely sound investment.

Course Objectives

The course is designed for those who have successfully built SSIS solutions before but are now wondering how to take performance to the next level. We will show you how the Integration Services pipeline works underneath the covers so you can better understand its way of thinking. We will show you how to be aware of design and environment choices that will affect the performance of your packages. Based roughly on the mnemonic O.V.A.L. we will show you how the following have influences on your ETL design

Operation

Volume

Application

Location

This course is fast paced with lots of demos to hammer home the points being made. At the end of the day students will be able to understand why a package may be performing in the way that it is and have alternative solutions available if needed.

Intended Audience

The intended audience are those who have used SSIS before and who are comfortable navigating their way around the product. The course will best suit those people who want to take their SSIS package performance to another level.

Prerequisites

Students require a good understanding of SQL Server Integration Services. At no point during the day will we cover any basics.

Course Content

There are lots of choices you make when building ETL solutions that can have an effect on their ability to perform well. When thinking about these choices in relation to performance some of these choices are easy but others are more hidden. Some choices are ones that you as the ETL designer can make, like type of provider to use, and others are thrust upon you such as Network segment speed. In this fast paced 1 day course we will run through a rough interpretation of a mnemonic coined by Microsoft to help in the design of your ETL solution, OVAL.

Operation What logic should be applied to the data given what you are trying to do?

Volume How much data are you trying to move and how? Here is where we drill down into SSIS performance.

Application When all you have is a hammer everything looks like a nail. What tool should you be using?

Location Where should the ETL solution run and why?

Operation

This part of the course can really be seen as the planning phase for your ETL solution. We are going to look at things that are under your control, the building of the ETL, and also the things that maybe out of your hands such as the environment. The module is broken down as follows

  • Break Down the work
  • Define operations
  • Identify potential areas for optimisation

Volume

This is by far the largest of the modules and is crammed with useful information on getting the most from your SSIS packages. We focus purely on the pipeline in the package because this is where we are concerned with movement of data and it offers us a wide range of opportunities for tuning.

  • Baseline Performance
  • Optimise Extraction
  • Buffers
  • Look from SSIS's point of view
  • Simple to use Transforms that might hurt
  • Entering Data
  • The Future

Application

In this module we will be looking at whether or not SSIS is the best tool in your armoury to do a certain job. As much as we love SSIS we also recognise that it is sometimes a sledgehammer to crack a nut. We discuss some of your alternatives and also offer advice on when we think you should be designing with SSIS or you should be using a different method.

Location

In this module we have a look at some of the decisions you can make for where your packages will execute. There will always be advantages and disadvantages in every choice but during this part of the course we will get to look at them in a bit more detail.

  • Location, location, Location
  • Where is your package executing
  • Is your data pulled or pushed or both
  • Choices for your ETL server

Prices

We have banded prices and charge a flat rate to deliver this one day course, not a per student rate. Any expenses are charged at cost. All prices exclude VAT.

1-4 Students - Daily Training Rate - £1000 + VAT

5-9 Students - Daily Training Rate - £1250 + VAT

10+ Students - Daily Training Rate - £1500 + VAT

We are currently delivering this course on request, please contact us for more details. We are more than happy to discuss options and any specific requirements with you to ensure the training gives you what you need.

Please download the course outline.