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 […]
In the previous post we looked at the functions TRY_CAST, TRY_CONVERT, and TRY_PARSE and how they compared. I wrapped up and said that my preference for new developments would be to use TRY_PARSE due to the tighter control which it provides us. As with everything in SQL Server however, there is no ‘best’ approach, it depends. I therefore wanted […]
We’re on quite a roll with window functions these past few weeks. Last week we looked at the operators we’d see in execution plans when using a window function. This week I wanted to tackle one of the more troublesome ones specifically: the Sort operator. We know that sort operators are expensive in our queries. To use […]
Our SQL Server environments are home to many workloads. In these situations one size doesn’t always fit all when it comes to configuration. Let’s take an example. On one hand we want small, high performance, transactional processing during the day. Through the evening this could change to larger, batch processing tasks. I’ve seen the configuration […]
We’ve recently looked at how single threaded concurrency was handled in SQL Server and followed up last time by diving into multithreaded concurrency. This time we’re going to go a bit further with the multithreading to look more into how the contention is handled when the number of cores available doesn’t line up with our workload. We’ll also […]
Recently we looked at single threaded concurrency in SQL Server for workloads which only need a single core to execute. We saw how they ran at the same time with the queries effectively being round-robined on the CPU. This time out I wanted to look at how the concurrency looks when we’re working with multithreaded workloads. Setting […]
The question for today: if we have three queries – one which takes 1 second to run, one which takes 2 seconds to run, and one which takes 3 seconds to run – how long will it take to return data for the first and last query if they all start at the same time? […]
Whilst we have front-end code which requires faster response times, there are also times we build code with the purpose of running for extended periods of time. Examples of this could be maintenance routine, or custom built queuing mechanisms. The way in which we design longer running processes like this can be key when it […]
Andy Brownsword 