As a database developer, when I started using Power BI, I was concerned about it retrieving reams of data only to perform transformations downstream. The Power Query editor misleads us into thinking the retrieval and transformations are applied sequentially. Thanks to Query Folding, that’s not usually the case. And that gives us more power to […]
Being a Data Engineer, this month’s invitation from Meagan resonates. She’s asked us to share a tip, technique, or lesson about detecting changes – which can be crucial to reduce workload across a large dataset. I’ll rewind back a number of years to an overnight batch process which ingested key transactional data for a business. Due to […]
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 […]
Andy Brownsword 