SQLServerFast

Plansplaining, part 1. The unexpected aggregation and assert

Hugo Kornelis
7 Comments
When I look at an execution plan I sometimes right away see how the operators work together. Other times I need to dive into the details and put in effort before I really understand it. And occasionally, I think I understand but then am proven wrong after I start looking at the details. However, understanding all the details of an execution plan is really important when you want to optimize performance. If I see an execution plan where I do not understand the role of each and every operator, I know I do not truly understand how the query is…
Read More

The many (too many?) reads of a many to many Merge Join

Hugo Kornelis
3 Comments
SQL Server’s optimizer can choose between four different physical operators for joining data. The Merge Join operator is usually the best choice if the input data is already sorted by the join columns. It processes both inputs at the same time, every time advancing the input that has the lowest value of the join key. This algorithm, known as “balance line processing” back in the olden days of magnetic tape storage, always finds all matches with just a single pass over each of the inputs. One to many One of the most important properties in a Merge Join operator is…
Read More

Willkommen, Bienvenue, Welcome

Hugo Kornelis
No Comments
It may be surprising to see a word of welcome posted in December 2017, on a blog that has posts going back as far as 2006. And yet, this is my very first new blog post on this site. All the content before this post was originally published at SQLblog.com. SQLblog.com is a fantastic website that hosts many bloggers. It has hosted me for almost twelve years as well. But it was time to move house, to create a new website for the content I have planned for the future. For now, this website is just my blog. But that…
Read More

Three new sessions in three months

Hugo Kornelis
1 Comment
Waking up from my blog-hibernation at this time is probably not ideal. I should spend my time on other things. For instance, on creating slide decks and demo code for the three completely new sessions that I will be delivering at various conferences within the next three months. So why am I still taking the time to write this? Because I am super excited about these new sessions, that’s why! Hash Match, the Operator The idea for this session is now almost two years old. I cannot remember why, during break time at another conference, I found myself wondering whether…
Read More

Misconceptions on parameter sniffing

Hugo Kornelis
6 Comments
In my previous post, I explained the basic of parameter sniffing, and then built on that to explain the lesser known mechanics of variable sniffing and cardinality sniffing. However, I also sneakily inserted a few comments on misconceptions about the performance impact of parameter sniffing, with the promise to explain in more detail later. Well … it is later now, so here is the promised explanation! Parameter sniffing’s bad rep Lots of people in the SQL Server field will claim that parameter sniffing is a bad feature. That is not the case. Just think about it for a moment –…
Read More

The sniffing database

Hugo Kornelis
1 Comment
Your SQL Server instances, like people with hay fever that forget to take their antihistamines during summer, is sniffing all the time. Sniffing is a trick employed by the optimizer in an attempt to give you better execution plans. The most common form of sniffing is parameter sniffing. Many people know about parameter sniffing, but there are a lot of misconceptions about this subject. I have heard people describe parameter sniffing as a bad thing, and I know people who claim that parameter sniffing is mostly good with some exceptions that they then call “bad parameter sniffing”. Neither of these…
Read More

Upcoming presentations. And an offer.

Hugo Kornelis
No Comments
It’s not my habit to announce my future presentations on this blog. But I sometimes make exceptions to this rule, and today is a good day for such an exception. Especially since I have been allowed to offer some money off for one of my upcoming presentations. I’ll fly to Copenhagen tomorrow, for SQL Saturday Demark, where I will present two sessions: a 60-minute talk (“T-SQL User-Defined Functions, or: Bad Performance Made Easy”) about how T-SQL user-defined functions can wreck your performance, and a 10-minute lightning talk (“Managing Execution Plans”) about the similarities between execution plans and my coworkers. Three…
Read More

The DIY guide for local-global aggregation

Hugo Kornelis
5 Comments
If you have read the title, you will not be surprised when I tell you that this blog is about a thing called “local-global aggregation”, and on how to do that yourself. So let’s start with the two obvious questions: what the heck is local-global aggregation anyway, and why the heck would you ever want to do it yourself? What is local-global aggregation The term local-global aggregation may sound scary, but it’s not that hard to understand. You might even already know the concept if you have ever heard the arguably most used explanation of parallelism. You have a jar…
Read More

How TOP wrecks performance (part 2)

Hugo Kornelis
No Comments
In my previous post, I showed how the vanilla version of TOP can really help performance by nudging the optimizer into picking an execution plan that is optimized for returning just a few rows instead of the entire result set. But I also showed that adding WITH TIES causes a cardinality misestimate, which may lead to suboptimal or downright terrible plan choices. Luckily, this is fixed in SQL Server 2014 and later, provided you use the new cardinality estimator. In this post I will show how the PERCENT option causes even worse performance, and how you can avoid that performance…
Read More

How TOP wrecks performance (part 1)

Hugo Kornelis
2 Comments
The TOP keyword in the SELECT clause may not be ANSI standard, but I am sure we have all used it. But do you know that it comes with two optional keywords? And have you used them? They are PERCENT and WITH TIES. TOP Let’s first look at the based functionality of TOP, and how it affects performance – which in fact, contrary to the title of this post, is usually quite good for performance. Here is a simple example, using the AdventureWorks2012 sample database (which can be downloaded from Codeplex). Let’s start with a very basic example: SELECT   CustomerID,…
Read More

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close