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 […]
In the previous post I explained Join Operators in SQL Server. Whilst compiling that I dug a little deeper and came across a few interesting points I thought were worth sharing. Let’s look at behaviour of the operators which may occur under specific conditions. Hopefully you find them as interesting as I did: Optimised nested loop […]
When reviewing our execution plans we’ll see joins executed using different operators. The type of operator is chosen based on the data that’s available to join and how the optimiser wants to execute it. In this post we’ll take a look at what the operators are, when they are used, and how they work. These […]
This month’s T-SQL Tuesday is being hosted by Taiob and he’s posed a great point to consider: How is AI changing our careers? Oh boy, this could be a divisive topic with plenty of opinions – I’m seeing submissions roll in as I’m finalising this and I’m very much looking forward to the community’s views on the topic. […]
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 […]
Code is an ever moving target. Version control and documentation only go so far, if they even exist. Sometimes all you have is the code in front of you. This is why I always start stored procedures with a header. Here’s a template as a starter: Name seems redundant as we have the proc name below […]
Uploading into a SharePoint list from an Excel document is straightforward to do, but populating multi-value fields isn’t so easy. This can be solved using Power Automate and I wanted to share how easily it can be done (⚠️ Warning: lots of images ahead). In brief, we’ll be doing the following: Let’s take an order […]
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 […]
Andy Brownsword 