When you run a query, SQL Server guesses how much memory you're going to need for things like sorts and joins. As your query starts, it gets an allocation of workspace memory, then starts work. Sometimes SQL Server underestimates the work you're about to do, and doesn't grant you enough memory. Say you're working with…
Get Yourself Together
This is one of the most common patterns that I see in stored procedures. I'm going to simplify things a bit, but hopefully you'll get enough to identify it when you're looking at your own code.
Here's the stored procedure:
[crayon-6a6eb98422c90792424334/]
There's a lot of perceived cleverness in here.
For years, when you right-clicked on a database and click Properties, the "Compatibility Level" dropdown was like that light switch in the hallway: You would flip it back and forth, and you didn't really understand what it was doing. Lights didn't go on and off. So after flipping it back and forth a few times,…
App Like That
If you've got the kind of application that sends bare-assed strings to SQL Server, you may end up with a weird choice.
Brent will be sitting on one shoulder telling you to use Forced Parameterization.
Base Table
When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches -- SQL Server's little change of address form for rows that don't fit on the page anymore.
This typically isn't a good thing, though. All that jumping around means extra reads and CPU that can be really confusing to troubleshoot.
Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn't think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…
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're writing a query, and you wanna check to see if rows exist in a table.
I'm using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are:
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.
Innocent Enough
I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate.
What I ended up writing as an example was this query:
[crayon-6a6eb98425651959147840/]
Why this query?
In which I show why you can't trust "actual" execution plans for branching stored procedures, and you're better off using sp_BlitzCache to figure out what branches you need to tune:
https://www.youtube.com/watch?v=y7ypC9YZnTU
Let's say you've got a query, and the point of that query is to take your largest customer/user/whatever and compare their activity to smaller whatevers. If SQL Server doesn't balance that work evenly across multiple threads, you can experience the CXCONSUMER and/or CXPACKET wait types. To show how SQL Server ends up waiting, let's write…
Bailing, Takes Me Away
Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database.
[crayon-6a6eb9842714b144756179/]
Querying a couple of the larger tables, with no indexes on the join columns leads to hash join plans.
If you're wondering why I'm forcing the second plan serial, using the MAXDOP 1 hint...
For this month's T-SQL Tuesday, Bert Wagner asked, "What code have you written that you would hate to live without?"
I bet you think I'm going to say sp_Blitz, and I would indeed hate to live without that, but you already know about that one, dear reader. Lemme show you something else cool.
When It Comes To Simplification Rob Farley has my favorite material on it. There's an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That's why I'd expect a query like this to throw away the join: [crayon-6a6eb98427d0c564936778/] After all, we're joining the Users table to itself…
Slicked Brad The apply operator remains one of my favorite tools, but much like CTEs and windowing functions, they're not totally magical unless you look at your indexes. Starting with a dead simple query on the Users table, it'd be easy to come up with an ideal index for this one thing. [crayon-6a6eb98428112466306929/] If you…
This Is A Parallel Query
This Is A Temp Table
[crayon-6a6eb98428428488073231/]
This Is A Parallel Insert Into A Temp Table
Quite So
There are three in SQL Server, not counting the Adaptive Join which is just a placeholder for either Nested Loops or Hash Joins.
If you look closely at each of those joins, you'll see all sorts of interesting things pop up.
A While Back I started this Q&A on Stack Exchange about ISNULL. It's one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable. Also, I'm saving up points for a Stack Overflow Swiss Army Knife. That's what you…
Trivial Plans Are Weird
They're the optimizer's "Yes, Dear". The thing is, sometimes "Yes, Dear" can miss some important details.
Cost based decisions about operators
Potential index improvements
Contradiction detection for check constraints
