Reading operators, properties, plan shape, and actual-versus-estimated behavior.
92 associated posts86 primary posts
This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy!
https://youtu.be/IVqvwNlwXuI
This morning, Greg Gonzalez (who I respect) posted about visualizing the tipping point with Plan Explorer (a product I respect), and he wrote: The tipping point is the threshold at which a query plan will "tip" from seeking a non-covering nonclustered index to scanning the clustered index or heap. The basic formula is: A clustered…
"I hate views," the DBA said. "They kill performance. And nested views are even worse."
Wrong. I'll prove it.
Ever wonder somebody else does it? Watch over my shoulder as I spend 9 minutes in PowerPoint explaining the big picture, and then about 40 minutes working on this stored procedure in the StackOverflow2013 database:
https://www.youtube.com/watch?v=7hv4vD7Cfy0
In our last episode, I introduced the concept of scan predicates: execution plan operations that weren't able to seek directly to the rows they needed. Let's take another query:
[crayon-6a7062c39cf43836271166/]
If we ONLY have the gray pages index on LastAccessDate, Id, DisplayName, and Age, our query plan looks like this:
I'm going to narrate this from bottom up because it makes for easier storytelling:
In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all - just a lightweight index seek:
Ah, index seeks - nothing better than that, right?
We started out the How to Think Like the Engine series with a simple query with a WHERE clause:
Now let's add an ORDER BY:
[crayon-6a7062c39e6a2106603309/]
Here's the updated plan - note that the query cost has tripled to $17.72 Query Bucks. Let's dig into why:
In the first post in my How to Think Like the Engine series, I started by doing a pretty simple query:
But I noted that if you were following along on your computer, you might see a different execution plan. If I tweak just one thing about my SQL Server, I see a different plan:
Precisely 80.3% of you are going to learn something in this post.
Earlier this week, I asked what you thought the arrows in estimated and actual query plans meant. I asked you to just guess without doing any research, and here's what you answered:
Somewhere along the way in your career, you were told that:
Index seeks are quick, lightweight operations
Table scans are ugly, slow operations
Signs and Numbers
When tuning queries, or even finding queries to tune, there's a rather misguided desire to look for queries with a high cost, or judge improvement by lowering query cost. The problem is that no matter what you're looking at, costs are estimates, and often don't reflect how long a query runs for or the actual work involved in processing the query.
I love building demos for our training classes because I'm constantly in a battle of the minds with SQL Server. I try to guess what he's going to do next, and I love it when he surprises me.
I started by building a new parameter sniffing demo with the Stack Overflow database, and in this case using the 50GB 2013 size. I wanted to demo a search stored procedure looking for the first few words of question titles.
FINALLY...
This is the last post I'll write about foreign keys for a while. Maybe ever.
Let's face it, most developers probably find them more annoying than useful, and if you didn't implement them when you first started designing your database, you're not likely to go back and start trying to add them in.
SQL Server automatically chooses when to divide your query's work across multiple CPU cores. It makes that decision based on your query's cost. To see it, let's throw 1,000,000 tiny rows in a table: [crayon-6a7062c3a26e8161699305/] And with my server set at SQL Server's default settings (Cost Threshold of 5, MAXDOP of 0), count how many…
SQL Server, thanks for coming today. Have a seat.
Yes, this might seem odd - there are a lot of us here.
Say your database has a view, and everybody's queries use it. Let's take the Stack Overflow database and create this view:
[crayon-6a7062c3a40c3396825940/]
Not pretty, but that's why you're here, right? Real world code is ugly.
And say my users are running queries like these:
[crayon-6a7062c3a40cf345843572/]
Notice that the select, where, order by etc all have variations in them. They're not all asking for the same fields, which means SQL Server can make different decisions about:
What Did Dinosaurs Watch On TV?
These are some of my favorite books and blogs from Microsoft from the way-back machine.
I can't say every bit of information is still 100% true and should be followed to the letter, but hey, that's what happens.
This trace flag is documented in KB #2413549, which says, "Using large amounts of memory can result in an inefficient plan in SQL Server." The details are a little light, so let's run a quick experiment with:
SQL Server 2017 CU 8 (14.0.3029.16)
VM with 4 cores, 32GB RAM, max memory set to 28GB
Stack Overflow database (circa March 2016, 90GB)
You And Ert
This is a quick post because it came up with a client. I like having stuff to point people to -- that's sort of like automation, right?
Anyway! Lots of plans have Assert operators in them. But they're usually performing modifications.
Party Up
When someone says "this query is slow", and you can rule out contextual stuff like blocking, odd server load, or just an underpowered server, what's the first thing you look at? There's a lot of potential culprits, and they could be hiding in lots of different places.
After several minutes of thinking about it, I decided to call my method QTIP, because I like to look at the:
