Pumpkin Spice
When I'm looking at someone's server, it's easy to tell the kind of care it's been under.
We've written a lot about servers that have gotten the wrong kind of care. You know, no backups, no checkdb, but someone managed to turn auto create stats off for every database, and enable affinity masking. That's not what I'm here to talk about today.
Thinking Of You
When people think about Heaps and the problems they can cause, they don't often think of temp tables and table variables as Heaps.
Of course, without a clustered index, any table is a Heap.
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
Playing The Odds
Assuming that life carries on as usual for a couple more weeks, I'll have been a consultant here for three years.
In that time, I've had some really interesting conversations with people, mostly about why things are the way they are on their servers.
Hell Here! Get Your Fresh Hell, Here!
Let's face it -- as far as developer tools go, SSMS is pretty bad.
Intellisense? The jokes write themselves.
Colonel Clustered
We've blogged a couple times about how clustered index key columns get stored in your nonclustered indexes: here and here.
But where they get stored is a matter of weird SQL trivia. You see, it depends on how you define your nonclustered index.
"It Depends"
We all scream for dependencies! Hooray!
Getting Weirder
In Part 1, we looked at how query plans can tell us little lies.
Now we're going to look at how those little lies can turn into bigger lies.
Adding An Index
Right now, this is our query:
[crayon-6aa5cc8440638924025265/]
If we add this filtered index, our query will have a grand ol' time using it;
[crayon-6aa5cc844063e154537544/]
Leaving aside my own advice about filtered indexes, what happens to the query plan now?
Freaking Out Brent Is a full time job. Some days I can get him to hit the desk drawer tequila, and other days I crash and burn. This day was so monumental I almost hired someone to do an action movie narration of this blog post. Let's Start Easy Over in Stack Overflow, we're going…
Optimizer Studies
I don't have a real job, which means that if something strikes my fancy, like staring at a spot on the wall for an hour, I can do it.
Occasionally things are a bit more inspired, and so I go poking around what SMRT PPL might be up to.
Understand Your Plan
When reading query plans, you may sometimes see a sort when you didn't explicitly ask for data to be sorted.
The short story: when you add a computed column that references another table, like with a scalar user-defined function, you can end up causing concurrency problems even when people didn't really want to go see that other table, and that table is locked by someone else.
Here's my query:
[crayon-6aa5cc84415e9336860240/]
Here's what I occasionally see when the query runs, using sp_BlitzWho:
Birth Control
Debugging, and, heck, even learning about debugging, is a time-consuming and often unhelpful process when it comes to tracking down issues in SQL Server. It is arduous and tedious, and yields little chance of making you any friends. So why write about it?
Mostly because no one else is willing to -- at least not in the context of SQL Server. And, let's face it, "vibrant social life" has been used to describe me exactly zero times Kelvin.
Whatever Man
When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of the query, whether they're joins, predicates, or selected columns.
Hotels on Wheels
Parking garages and predicates have a "lot" in common.
GET IT?
Oh, XML
When we first met, it was about 2011. I had to load files full of you into a table. It wasn't so bad.
You were well-formed, and didn't change from file to file. Even using SQL Server 2005, I could take you.
People seem to care about deadlocks
That's why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock?
Well, I had this song stuck in my head. But enough about that.
Not A Single Picture Of A Zipper
There are some interesting things about Merge Joins, and Merge Join plans that I figured I'd blog about.
Buskets
Much like Sorts, Hash Joins require some amount of memory to operate efficiently -- without spilling, or spilling too much.
And to a similar degree, the number of rows and columns passed to the Hashing operator matter where the memory grant is concerned. This doesn't mean Hashing is bad, but you may need to take some extra steps when tuning queries that use them.
