Clustered and nonclustered indexes, keys, included columns, and access paths.
113 associated posts109 primary posts
This week I'm running a series of posts introducing the fundamentals of index tuning. First up, let's talk about understanding how many indexes make sense on a table.
The more nonclustered indexes you have on a table,
the slower your inserts and deletes will go.
Base Table
When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches -- SQL Server's little change of address form for rows that don't fit on the page anymore.
This typically isn't a good thing, though. All that jumping around means extra reads and CPU that can be really confusing to troubleshoot.
When you're looking at an index recommendation - whether it's in an execution plan or the missing index DMVs - it helps to understand Clippy's blind spots. Let's start with the small StackOverflow2010 database so you can follow along. (It's just a 1GB direct download, and it expands to a 10GB database that reflects StackOverflow.com…
This week, Brent, Tara, Erik, and Richie discuss error log issues, issues with moving a 2-node AG to a different VM, adding a rowversion column gotchas, using indexes, Docker & CI/CD, RESOURCE_SEMAPHORE query compile, table locks, Query Store, and best places to eat in NYC.
Here’s the video on YouTube:
Let's take the dbo.Users table from the Stack Overflow database, which holds exactly what you think it holds - the list of users:
Say I want to count up all of the people with 1 reputation point:
[crayon-6a71e9eb0ad9d755274828/]
Without any indexes, that would scan the whole table. So to make it go faster, let's create an index on Reputation:
[crayon-6a71e9eb0ada8143141962/]
SQL Server uses the Reputation index for the count query:
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.
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-6a71e9eb0ca1a098105026/] If you…
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!
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?
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.
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.
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.
No, not that
It's not the F word.
Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when it fails.
It turns out I can be dumb In a previous blog post about Adaptive Joins, I thought that EXISTS wasn't supported by the new feature. It turns out that EXISTS is totally 100% supported, as long as your indexes support EXISTS. To show this, I need to show you a query that gets an Adaptive…
Life Is Messy
Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it's easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky me!) because there's nothing all that woogy or wonky to dance around.
My public SQL Server database copy of the Stack Overflow data dump only includes clustered indexes by default. I want to keep your database size as small as possible for quick downloading.
But like any database - when you create it, should you add your own indexes to make queries go faster? The answer lies in knowing your workloads, but we don't usually know our database workloads until they start.
