The 2015 Swedish SQL Server usergroup challenge

2015-12-102016-11-26 / Daniel Hutmacher / Leave a comment

It’s been absolutely ages since I last wrote a blog post, mostly because I’ve been busy getting my shiny new own consultancy up to speed, but I’ll admit that writer’s block has also been a factor.

But here’s something to write home about. This year’s annual Swedish SQL Server usergroup challenge was as interesting as ever, and it marks my third stab at this prestigious competition. In this post, I’ll go through my contribution, highlighting some of the techniques that I’ve applied to make it go really fast.

Continue reading →

The 2014 Swedish SQL Server usergroup challenge

2014-12-112014-12-11 / Daniel Hutmacher / 4 Comments

Sunday came early this week. In this longer-than-usual post, I’m going to show you the details of my contribution to the 2014 edition of the annual Swedish SQL Server usergroup challenge.

Continue reading →

Manual transaction management

2014-10-122014-08-05 / Daniel Hutmacher / Leave a comment

Transactions are great. They keep your data together atomically, so you’re not in for any nasty surprises. But even a novice knows better than to leave transactions open, waiting for user interaction. If you do, lock waits and probably deadlocks will pile up in no time.

So how do you book a flight without blocking all the other users or losing your seat to somebody else while you make up your mind?

Continue reading →

How to build a histogram in T-SQL

2014-09-142014-07-25 / Daniel Hutmacher / 1 Comment

Talk to SQL Server developers or DBAs about histograms, and they’ll inevitably think of index statistics. However, a task you may encounter some day is to calculate the distribution of numbers in a table. And although there’s no quick built-in function to do this, it’s not as difficult as you may think.

Continue reading →

Using MERGE as a single INSERT or UPDATE

2014-09-072014-07-23 / Daniel Hutmacher / Leave a comment

The MERGE statement is a very powerful way to combine INSERT, UPDATE and/or DELETE in a single statement. But there’s more than meets the eye. There are situations where you could use a MERGE statement to perform just one of those tasks.

Continue reading →

Blocking/non-blocking aggregate operators

2014-06-152014-06-05 / Daniel Hutmacher / 6 Comments

Some database operations can be performed in distinctly different manners, with different impacts on query performance. One important example of such an operation is calculating an aggregate. In this article, we’ll take a look at how aggregates can be “blocking” or “non-blocking”, how it affects memory allocation, and ultimately, what impact this has on your query.

Continue reading →

HASH JOIN deep-dive

2014-06-012014-05-13 / Daniel Hutmacher / 4 Comments

Among the three different types of join operators used by SQL Server, the HASH JOIN does some of the hardest work. It scales pretty well and is very suitable for parallel processing. As such, it can be very powerful in many applications, but hash joins can potentially consume quite a bit of memory, so seeing on in your query plan could be an indicator of a performance tuning issue in your query or data.

Continue reading →

An introduction to filtered indexes

2014-05-182014-04-07 / Daniel Hutmacher / Leave a comment

Filtered indexes are an excellent performance tuning feature found on SQL Server 2008 and later. Basically, by adding a WHERE clause to a CREATE INDEX statement, you can make the index smaller, more specific and faster.

Continue reading →

Minimally logged operations

2014-04-272014-06-29 / Daniel Hutmacher / 2 Comments

Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.

Continue reading →