SSIS Performance Tuning Course (1 Day)
Includes light lunch and refreshments - 9am - 5pm.
This will be held at Rothamsted Manor, Harpenden which is 5 mins from Junction 9 of the M1, 25 minutes from Kings Cross First Capital Connect as well as easy reach from the M25 and A1 - oh, Luton airport is just 15 minutes up the road too.
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 Micorosft to help in the design of your ETL solution, O.V.A.L.
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
Allan Mitchells Bio
Allan has been using Microsoft's BI suite of tools since 1999. He has a special passion for Data Transformation Services (DTS) and SQL Server Integration Services (SSIS) but has worked on many projects throughout the world that involve Analysis Services and Reporting Services. He is one of the Co-Authors on the Wrox title "Professional SQL Server 2005 Integration Services". He works as a consultant (www.konesans.com) in a wide variety of business areas and when he is not doing that he is helping to run the two ETL community sites www.SQLIS.com and www.SQLDTS.com.