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 […]
Performance issues in SSIS packages can be tough to track down. You know something is off but trawling through pages of SSIS reporting and the validation noise is frustrating. Adding your own logging is a nice idea but not feasible at scale. An alternative? Querying internal tables within the SSIS database is a quicker way […]
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 […]
One particular performance issue with SSIS data flows can fly under the radar – spilling to disk. This isn’t clearly visible through regular debugging or execution so can go unnoticed. And it hurts. Paging to disk is bad for performance. Disks are much slower to access than memory, so we want to keep our data […]
When running queries, reviewing execution plans, or diving into Query Store it’s not uncommon to see missing index recommendations, particularly for more complex queries. But are they any good? There are many factors when considering an index. However if you’re a situation where these missing index recommendations are enticing, I wanted to cover 3 key areas you […]
When looking to increase performance for a database it can be the smaller and faster queries which can be improved easier and more effectively than their slower counterparts. We can be drawn to tuning long running and complex queries, but these can be a challenge due to their size and complexity. Sometimes though we can reclaim just […]
Last week I shared a script to identify foreign keys which weren’t indexed. What we didn’t discuss in detail was why it can be important to index these columns. Here we’ll demonstrate the two areas we can see great benefits by introducing the indexes. Query optimisation We’ll start with the typical benefit of indexing – query optimisation. The index […]
Bad code smells can run through a system, and one instance can quickly spread when code is recycled. Simon recently looked at a non-sargable example and was able to fix it by using an index and refactoring the query. I wanted to consider an alternative approach if we saw the same issue repeated consistently. We don’t want to […]
Andy Brownsword 