T-SQL Tuesday #101: A few of my favorite tools

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! The April 2018 edition is hosted by Jens Vestergaard (b|t). His challenge for this month: write about the tools, other than SSMS and VSDT, that we use for our work. Essential tools The full invitation even includes words such as “essential” tools, and “depend on…
Read More

Plansplaining, part 4. Let’s repartition the streams.

This is the fourth post in the plansplaining series. Each of these blog posts focuses on a sample execution plan that exposes an uncommon and interesting pattern, 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.…
Read More

T-SQL Tuesday #100: Looking forward

The monthly blog party that is known as T-SQL Tuesday has hit 100th episode (and I only missed 98 of them!). In true Olympic fashion, it returns to its roots for its centennial celebration. In other words, our host this month is Adam Machanic (b|t) himself. And even though 100 is a perfectly valid number to look back, he decided that looking ahead is more fun. He asks all bloggers to whip out the crystal ball and predict what our world will look like in another 100 months, when (hopefully) T-SQL Tuesday #200 will be celebrated. Looking ahead I must…
Read More

Plansplaining, part 3. How repeating work saves time

This is the third post in the plansplaining series. Each of these blog posts focuses on a sample execution plan that exposes an uncommon and interesting pattern, 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.…
Read More

SSMS hidden gem: Edit Query Text

When I am tuning a query, I usually start with the query text. I execute it to retrieve an execution plan, and then the fun starts. However, very often I first need to find the specific query (or queries) to tune. There are multiple ways to do this, depending on what monitoring tools and which version of SQL Server the customer uses. In some cases, I at first find only an execution plan. Now I do like to see execution plans when tuning, but I also need to have the query! The old method So here is an example of…
Read More

Execution plans 101: (back to) The basics

3 Comments
I recently received an email from a reader of this blog. Specifically of the plansplaining posts. He wrote that he enjoyed reading it, but he still had some questions. Those questions made me realize that I made a mistake when I started this series. I dove in at the deep end. I assumed a lot of prior knowledge and experience with reading execution plans, but not every reader has that knowledge and that experience. So I decided that for this one post, I will move from the deep end of the pool to the shallow water. In this post I…
Read More

T-SQL Tuesday #99: Dealer’s Choice

No Comments
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…
Read More

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

4 Comments
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…
Read More

Plansplaining, part 1. The unexpected aggregation and assert

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

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

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