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-6a63926e222d2543551140/] If you…
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.
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
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-6a63926e31b81574699989/]
If we add this filtered index, our query will have a grand ol' time using it;
[crayon-6a63926e31b87790807170/]
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…
Understand Your Plan
When reading query plans, you may sometimes see a sort when you didn't explicitly ask for data to be sorted.
Say you've got a table - in this case, I'm using the Stack Overflow Posts table, which holds all of the questions and answers:
See the clustered index at the bottom? It's on the Id field, which is an identity - starts at one and goes up to a bajillion.
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?
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.
Just Another Way
No matter how you delve into missing index requests -- whether it's the plan level, DMV analysis, or (forgive me for saying it), DTA, the requests will generally be the same.
They'll prioritize equality predicates, the columns may or not may be in the right order, the columns may or may not be in the right part of the index, and the impact...
Thumbs Rule OK?
When writing queries, it's really common to want to take shortcuts to express some logic.
I know, it "works fine", and the results are "right", and you have "more important" things to do.
Scan The Man
There seems to be a perpetual battle waged against the Index Scan.
At some point it was declared that scans were inferior to seeks, and all energy should be dedicated to eradicating them.
Sharing Is Caring
When everything is going well, and queries are behaving responsibly, one need hardly think about memory grants.
The problem becomes itself when queries start to over and under estimate their practical needs.
Second Hand Emotion
Queries ask for memory to do stuff. Memory is a shared resource.
This sentence gets repeated a lot. You know the one: "Functions prevent the use of indexes." Well, knowing you, it's probably just your indexes. I've seen your indexes.
Functions can use indexes, but even so, that doesn't mean that they're going to perform as well as queries without functions.
Which Indexes?
In the SUPERUSER database (yeah, I know, I'm cheating on Stack Overflow), all the tables have a PK/CX on an Id column, which is an Identity.
During Training
We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching.
What's back-searching? It's a word I just made up.
Size Matters?
One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use.
The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it does the actual size in MB or GB of the index.
The Humble Heap
If you don't know this by now, I'm going to shovel it at you:
If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated.
