Indexing

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. Transact-SQL SELECT u.Id,…

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 Simple parameterization, according to… uh… Official Microsoft documentation? Can only occur in a Trivial Plan. Which makes sense. Simple parameterization is considered…

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 Mr. Optimizer does the rock n roll hoochie koo with Mrs. Optimizer, c. 1953. When reading query plans, you may sometimes see a sort when you didn’t explicitly ask for data to be sorted. Sometimes they show up to support order-preserving operators like stream aggregates, merge joins, or segments. Other times, they…

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…

Not A Single Picture Of A Zipper Image humbly borrowed from https://70srichard.wordpress.com/2014/12/17/beverly-hills-cop/ There are some interesting things about Merge Joins, and Merge Join plans that I figured I’d blog about. Merge joins have at least one interesting attribute, and may add some weird stuff to your query plans. It’s not that I think they’re bad,…

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. But take a minute here for future you. Just think of reading this post as a continuation of the…