Blog - Page 19 of 27 - SQLServerFast

T-SQL Tuesday #99: Dealer’s Choice

February 13, 2018

Back in 2009, Adam Machanic (b|t) started an initiative that is still going strong: T-SQL Tuesday. This is a monthly blog party. Every second Tuesday of each month, lots of people blog about the same topic, selected by the host for that month. And now, in the 99th installment, I decided to finally join in! For February 2018, our host Aaron Bertrand (b|t) has elected to give bloggers a choice: either share some of the non-tech things that we are passionate about; or go all Aaron-style and tell the world what we consider the worst T-SQL habits that we really…

Plansplaining, part 2. Why scan and spool instead of seek?

February 5, 2018

Hugo Kornelis

This is the second post in the plansplaining series. Each of these blog posts focuses on a sample execution plan and details exactly how that plan works. In the first post, I covered each individual step of each operator in great detail, to make sure that everyone understands exactly how operators work in the pull-based execution plans. In this post (and all future installments), I will leave out the details that I now assume to be known to my readers. If you did not read part 1 already, I suggest you start there. Sample query The query in this post…

Plansplaining, part 1. The unexpected aggregation and assert

January 8, 2018

Hugo Kornelis

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…

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

December 24, 2017

Hugo Kornelis

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…

Willkommen, Bienvenue, Welcome

December 14, 2017

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…

Three new sessions in three months

September 14, 2017

Hugo Kornelis

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…

Misconceptions on parameter sniffing

November 3, 2016

Hugo Kornelis

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 –…

The sniffing database

October 23, 2016

Hugo Kornelis

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…

Upcoming presentations. And an offer.

September 15, 2016

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…

The DIY guide for local-global aggregation

August 31, 2016

Hugo Kornelis

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…

T-SQL Tuesday #99: Dealer’s Choice

Plansplaining, part 2. Why scan and spool instead of seek?

Plansplaining, part 1. The unexpected aggregation and assert

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

Willkommen, Bienvenue, Welcome

Three new sessions in three months

Misconceptions on parameter sniffing

The sniffing database

Upcoming presentations. And an offer.

The DIY guide for local-global aggregation

T-SQL Tuesday #99: Dealer’s Choice

Plansplaining, part 2. Why scan and spool instead of seek?

Plansplaining, part 1. The unexpected aggregation and assert

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

Willkommen, Bienvenue, Welcome

Three new sessions in three months

Misconceptions on parameter sniffing

The sniffing database

Upcoming presentations. And an offer.

The DIY guide for local-global aggregation

Hugo Kornelis

Subscribe via Email

Meta

Archives