Why Are You Here?
Within the realm of cursor-y oddness, there are a couple things to be extra-aware of.
Yes, I know that Aaron Bertrand has written about cursors. Thank you, Aaron.
This post is about a slightly different …
Category: Common Query Plan Patterns
Common Query Plan Patterns: Sorts From Nowhere
Choices, Choices
The optimizer has a lot of choices. As of SQL Server 2019, there are 420 of them.
You can see how many are available in your version of SQL Server by doing this:
SELECT
total_transformations
= COUNT_BIG(*)
FROM … Common Query Plan Patterns: Spools From Nowhere
Stuffy Bear
The first time I heard the term “common subexpression spool” my brain went numb for a week.
It’s not a particularly mellifluous phrase, but it is helpful to understand what it is.
One easy way to think about …
Common Query Plan Patterns For Joins: Prefetching Lookups
Counterpoint
Shockingly, there hasn’t been a lot written about Prefetching. Hard to imagine why.
Women want it, men want to be it, James Bond goes to see movies about it, and so forth.
The few reliable sources of information out …
Common Query Plan Patterns For Joins: Sorting Lookups
Pantsuit
Most people see a lookup and think “add a covering index”, regardless of any further details. Then there they go, adding an index with 40 included columns to solve something that isn’t a problem.
You’ve got a bright future …
Common Query Plan Patterns For Joins: Index Union
I’m On My Smoke Break
Index union is a little bit different from index intersection. Rather than joining two indexes together, their result sets are concatenated together.
Just like you’d see if you wrote a query with union or union …
Common Query Plan Patterns For Joins: Index Intersection
Incomplete
The optimizer has a few different strategies for using multiple indexes on the same table:
- Lookups (Key or Bookmark)
- Index Intersection (Join)
- Index Union (Concatenation)
Lookups are between a nonclustered index and either the clustered index or heap, and …
Common Query Plan Patterns For Joins: No Equality Predicate
Data Dribbling
The longer you work with data, the more weird stuff you see. I remember the first time I saw a join on a LIKE I immediately felt revolted.
But not every query has an available equality join predicate. …
Common Query Plan Patterns For Joins: DOP and Bitmaps
1/10
Bitmaps can be really useful in parallel hash and merge join plans. They can be used like sargable, but not seekable, predicates.
Where they get created and where they get used is a bit different,
10/10
When bitmaps …
Common Query Plan Patterns For Joins: Semi Join Problems
But You Should
This post isn’t meant to dissuade you from using EXISTS or NOT EXISTS when writing queries. In fact, most of the time I think they make a lot of sense.
But weird things can happen along …