Handling data modifications to large tables can be tricky. We can end up with long running queries and large transactions. Efficiently handling them comes from batching. Effective batching in general helps us by: In this post we’ll take a look at a key-range approach to solve the issue. With this solution a batch will be […]
Last time out we started to look at optimising SSIS packages by showing how to identify bottlenecks with a handy script. This time we’re turning insights into action to solve those pain points. The solutions are grouped into 3 areas: Data Flows, as they do a lot of heavy lifting; the Execute SQL task, which can […]
When creating SQL Agent jobs to execute SSIS packages we can choose the level of logging to be captured. Different settings are more beneficial under the right circumstances so it’s important to understand the differences to make the right decision. These settings control the internal logging done by SSIS. This is out of the box […]
When using data flows in SSIS packages we want the flow to be fast and fluid. Improving performance of the flows will vary in different packages, but one element can consistently help – tuning memory utilisation. In this post we’ll look at tuning the memory utilisation by altering the number of rows passing through the flow concurrently. […]
Last time out we discussed blocking transformations, what they are, the impact of them, and touched on how to deal with them. In this post we’re going a step further to tackle one of them head on. Here we’ll demonstrate the impact of blocking caused by the Sort transformation, and look at two options for solving […]
We’re on quite a roll with window functions these past few weeks. Last week we looked at the operators we’d see in execution plans when using a window function. This week I wanted to tackle one of the more troublesome ones specifically: the Sort operator. We know that sort operators are expensive in our queries. To use […]
Andy Brownsword 