Indexing

Home
Indexing

Management Studio Hides Missing Indexes From You.

July 3, 2018

SQL Server Management Studio only shows you the first missing index recommendation in a plan. Not the best one. Not all of them. Just whichever one happens to show up first. Using the public Stack Overflow database, I’ll run a simple query: Transact-SQL SELECT c.CreationDate, c.Score, c.Text, p.Title, p.PostTypeId FROM dbo.Users me INNER JOIN dbo.Comments c…

Does It Matter Which Field Goes First in an Index?

June 28, 2018

Let’s take the dbo.Users table from the Stack Overflow database, which holds exactly what you think it holds – the list of users: StackOverflow.dbo.Users Say I want to count up all of the people with 1 reputation point: Transact-SQL SELECT COUNT(*) FROM dbo.Users WHERE Reputation = 1; 1 SELECT COUNT(*) FROM dbo.Users WHERE Reputation =…

Can Non-SARGable Predicates Ever Seek?

June 15, 2018

Development, Indexing

Cheating At Candy Crush The short answer is that yes, they can. But only with a little extra preparation. Before I show you what I mean, we should probably define what’s not SARGable in general. Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc. Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col),…

Indexing Strategies For Cross Apply

June 12, 2018

Execution Plans, 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,…

Mysterious Forwarded Records

June 1, 2018

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. This isn’t an argument for or against indexing temp tables, but while working with a client we came…

Behind Every Trivial Plan Is A Good Demo

May 29, 2018

Execution Plans, Indexing

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, Simple Parameterization and Check Constraints

May 22, 2018

Execution Plans, Indexing

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…

Where Clustered Index Keys Dare

May 11, 2018

Indexing, SQL Server

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! If you…

When Query Plans Lie Part 2

May 9, 2018

Execution Plans, Indexing, SQL Server

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: Transact-SQL SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; 123 SELECT u.UpVotes,…

When Query Plans Lie Part 1

May 7, 2018

Execution Plans, Indexing

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…

Hey, That’s Not My Sort!

May 3, 2018

Execution Plans, Indexing

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…

Does an Index on Just The Clustering Key Ever Make Sense?

April 20, 2018

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: StackOverflow.dbo.Posts 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. So it would be…

Index Key Column Order And Supporting Sorts

April 18, 2018

Execution Plans, Indexing

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…

Parking Garages And Predicates

April 17, 2018

Execution Plans, Indexing, SQL Server

Hotels on Wheels Parking garages and predicates have a “lot” in common. GET IT? No no but seriously. They do. If you’ve ever lost your ticket, forgotten where you’ve parked, or just driven around hopelessly waiting to see an empty spot, you probably wish there were a better way to do things. Transact-SQL CREATE TABLE…

The Many Mysteries of Merge Joins

April 4, 2018

Execution Plans, Indexing

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,…

Missing Index Impact and Join Type

March 27, 2018

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…

If You Can’t Index It, It’s Probably Not SARGable

March 20, 2018

Execution Plans, Indexing

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…

Then Why Doesn’t SQL Always Seek?

February 16, 2018

Execution Plans, Indexing, SQL Server

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. Much like asking why the whole plane isn’t made out of the stuff the black box is made out…

Memory Grants: SQL Server’s Other Public Toilet

February 15, 2018

Execution Plans, Indexing, SQL Server

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. What kind of stuff? Well, usually…

Functions and “the use of indexes”

February 1, 2018

Execution Plans, Indexing, SQL Server

Wordy Bird 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. 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…

Menu