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 […]
The DISTINCT clause in a query can help us quickly remove duplicates from our results. Sometimes it can be beneficial to stop and ask why. Why do we need to use the clause, why are we receiving duplicates from our data? I see this typically due to a JOIN being used where we don’t really want all of those results. This could […]
Deadlocks are an enduring feature of SQL Server. They’ve been a source of pain for many over the years and there are various ways to diagnose, mitigate or resolve them. Here I want to demonstrate approach I haven’t seen discussed – using an application lock to segregate processes. An example where I’ve used this effectively […]
This month’s invitation from Reitse asks us to talk about learnings from abandoned or failed projects. This one will sit somewhere squarely between those two points. It was an opportunity to learn about scalability. Let’s set the scene. A point of sale system being rolled out across hundreds of physical locations. Transaction data collected each night to […]
Andy Brownsword 