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 […]
Rounding out T-SQL Tuesday for 2024, Kevin’s invitation brings it together with the Festive Tech Calendar (more on that at the end), and asks us to write about a Microsoft Data Platform announcement that could be considered a gift. For context, this post is targeted at developers or engineers who don’t have the time to dig into SQL Server […]
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 […]
Foreign keys provide us with confidence in the integrity of the code we develop. In this post we identify indexing opportunities around foreign keys to increase performance of their usage. Creating an index on a foreign key column (or columns) can provide additional performance in two key situations: That’s all well and good if we’re […]
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 […]
I’ve recently been reviewing SSIS packages to make some performance fixes and needed a way to validate the results of those changes. I thought I’d share the scripts as they may be useful for others. Rather than relying on run times from the SQL Agent running the packages I wanted to dive deeper into the […]
Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query. Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we […]
In the last post we looked at how TOP and MAX operators compared. We saw the execution plan for a MAX function used a Stream Aggregate operator which is one of two which we can use for aggregation I wanted to look at the two operators and how they perform the same tasks in different ways. The way they function is key […]
Both TOP (1) and MAX can be used to identify the largest value in a data set. Whilst they get the same result it isn’t necessarily in the same way. Firstly, what is the difference between the two? The TOP clause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. […]
Blocking in SQL Server will reduce throughput. Excessive blocking can be cause bottlenecks on our environments so helping to mitigate it. Here we’re going back to basics to look at how it happens and how having effective indexes can reduce it. Blocking We’ll start off with an example of blocking. Here’s our customer table and […]
Andy Brownsword 