Execution Plans Archives - Page 8 of 11

Performance Tuning

Optional Parameters and Missing Index Requests

September 14, 2017

That's when it all gets blown away
At one point or another in everyone's SQL-querying career, they end up writing a query that goes something like this:
[crayon-6a6e7b25376ab555108681/]
These are often called optional parameters, and if you spend any time looking at queries, this will make you shudder for many reasons. Poor cardinality estimates, full scans, etc.

Performance Tuning

Indexing for Windowing Functions: WHERE vs. OVER

September 13, 2017

Life Is Messy
Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it's easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky me!) because there's nothing all that woogy or wonky to dance around.

Performance Tuning

How Much Can One Row Change A Query Plan? Part 1

September 12, 2017

Last week, Erik showed two queries that were aaaaalmost identical, with only one extra column - and the execution plans were dramatically different. Adding just one eensy column made all the difference in the world.

Now, check out these two queries - the first asks for top 100, and the second asks for top 101:
[crayon-6a6e7b2541892209189693/]
They produce estimated plans that seem identical (PasteThePlan), even down to the estimated costs - note that they're both 50%:

Performance Tuning

How Much Can One Column Change A Query Plan? Part 2

September 8, 2017

What happened in Part 1?
Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn't have a single foreign key in it, anywhere.

If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley.
2. Duplicated rows

Performance Tuning

How Much Can One Column Change A Query Plan? Part 1

September 5, 2017

Beyond Key Lookups
I'm going to show you two queries, and two query plans. The two queries are only different by one selected column, but the two query plans are wildly different.

Unless I've talked to you in the last few days, these may surprise you as well.

Performance Tuning

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

August 23, 2017

Yazoo
Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn't need, like skipping over the dialog in, uh... movies with really good fight scenes.

Car chases?

Performance Tuning

Five Mistakes Performance Tuners Make

August 8, 2017

There's no Top in the title

And that's because a TOP without an ORDER BY is non-deterministic, and you'll get yelled at on the internet for doing that. This is just a short collection of things that I've done in the past, and still find people doing today when troubleshooting performance. Sure, this list could be a lot longer, but I only have the attention span to blog.

Performance Tuning

Locking When There’s Nothing To Lock

August 3, 2017

Demo Day

We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it's pretty awesome.

Performance Tuning

SQL 2016 SP1 Shows You Wait Stats in Execution Plans. Or Does It? [UPDATED]

July 18, 2017

SQL Server 2016 Service Pack 1 brought us performance tuners all kinds of shiny goodies - like the ability to see wait stats inside a query's actual execution plan. I was really excited when this first came out, but I keep seeing some real oddities. Let's keep this one really simple: I'm using the Stack…

Performance Tuning

A Better Way To Select Star

July 5, 2017

Mindless Self Promotion
I liked writing this blog post so much that I wrote an entire presentation on it. If you'd like to see it at GroupBy, click the link and vote.

Update: You can watch the video for this session here.

Performance Tuning

Query Plans: Trivial Optimization vs Simple Parameterization

June 16, 2017

Facemaking
You know when you think you know something, and the obviousness of it makes you all the more confident that you know it?

That's usually the first sign that there's a giant gotcha waiting for you.

Performance Tuning

Why Your Biggest Query Plans Don’t Show Up in Some DMVs

June 2, 2017

SQL Server has three ways to get execution plans from the plan cache:

sys.dm_exec_query_plan - around since SQL Server 2005
sys.dm_exec_text_query_plan - added in 2005 SP2
sys.query_store_plan - new in SQL Server 2016

Performance Tuning

Logical Errors And Query Performance

June 1, 2017

Is that a nice way of saying typo?
People will often tell you to clearly alias your tables, and they're right. It will make them more readable and understandable to whomever has to read your code next, puzzling over the 52 self joins and WHERE clause that starts off with 1 = 2. It can also help solve odd performance problems.

Do SQL Server 2017’s Adaptive Joins Work with Cross Apply or Exists?

May 19, 2017

I think I've mentioned that the most fun part of new features is testing them with old ideas to see how they react.

It occurred to me that if Adaptive Joins didn't work with APPLY, I might cry.

Performance Tuning

How Scalar Functions Can Stop You From Getting Adaptive Joins

May 17, 2017

If you're not aware of the performance problems scalar valued functions can (and often do) cause, well, uh... click here. We'll talk in a few days.

If you are, and you're worried about them crapping on Adaptive Joins, follow along.

SQL Server 2017: Interleaved MSTVFs Vs Inline Table Valued Functions

May 16, 2017

But is it faster?
Now, I know. There are very few "always" things out there in SQL Server. This is also true for functions.

A lot of the time -- I might even say most of the time, inline table valued functions are going to be faster that scalar and multi statement table valued functions.

SQL Server 2017: Interleaved Execution for MSTVFs

May 15, 2017

What I don't want you to take away from this
Is that I want you to start using Multi Statement Table Valued Functions all over the place. There are still problems with them.

Backed by table variables
Lots of hidden I/O cost
Number of executions may surprise you

Performance Tuning

Breaking: SQL Server 2016 SP1 CU2 Supports ENABLE_PARALLEL_PLAN_PREFERENCE Hint

May 12, 2017

Hot Diggety!
After reading an excellent tip by Dmitry Pilugin about the new hint for forcing a parallel plan in SQL Server 2017, I figured I'd test it out on SQL Server 2016's latest CU to see if it had been back ported yet.

Good news! It has. Obviously. That's the title of the blog post.
Puddin'
Here are the test queries I ran.
[crayon-6a6e7b2547cfd653563670/]
And here are the boring results!

Performance Tuning

SQL Server 2017: Statistics Information Comes To Query Plans

May 12, 2017

Warm fuzzies
I think this is how my mom would have felt if I went to college.

You see, once you stop cargo culting around index fragmentation, and disk queue length, you start to realize which things are actually important to query performance.

Adaptive Joins And SARGability

May 5, 2017

There's a famous saying
Non-SARGable predicates don't get missing index requests.

And that's true! But can they also stifle my favorite thing to happen to SQL Server since, well, last week?