Clustered and nonclustered indexes, keys, included columns, and access paths.
113 associated posts109 primary posts
These days, I jump back & forth a lot between SQL Server and Postgres. (We use AWS Aurora Postgres to store SQL ConstantCare®'s data.) Whenever I come back to the sweet, sweet graphical execution plans in SQL Server Management Studio, I breathe a sigh of relief. Dang, these things are so much easier for me to interpret. It's like coming home.
In our last cliffhanger episode, I said that if we ran this query: [crayon-6a70643009b52741143626/] And we had this index: [crayon-6a70643009b62710643575/] Then we would have to do all of these things: Look up user #643 on the clustered index by doing a seek - because thankfully our kind developer included the clustered primary key in the…
In our last episode, in between crab rangoons, I had you create either one of these two indexes:
[crayon-6a7064301ba2f838813131/]
And I said that the leaf pages of either index would look the same:
In terms of the space they take up on the leaf pages, it doesn't matter whether columns are in the keys of an index or in the includes. It's the same amount of space on the leaf pages. To see it, run sp_BlitzIndex focused on that table:
In our last episode, we were running into problems with these two queries: [crayon-6a7064301c9b0440017762/] When SQL Server saw the function in the second query's WHERE clause, it punted out with a 1-row estimate, which caused us problems. SQL Server did an index seek + key lookup where it wasn't appropriate. Well, if we're not allowed…
In our last episode, I'd expanded our query to include DisplayName and Age - two columns that weren't in our nonclustered index: [crayon-6a7064301d488059265497/] So as a result, I was getting key lookups in the execution plan: And I spent a lot of time talking about the overhead that each key lookup incurs. Astute readers among…
In our last couple of queries, we've been using a simple query to find the Ids of everyone who accessed the system since mid-2014:
[crayon-6a7064301e020185383978/]
But Ids alone aren't all that useful - so let's add a few more columns to our query:
[crayon-6a7064301e02b251519767/]
Now think about how you're going to execute this query plan in plain English, as a human being. You have two copies of the table: the nonclustered index (black pages) with LastAccessDate, Id:
When we left off in the last post, our users kept running this query, and they want it to be really fast:
[crayon-6a7064301ec21747556076/]
Let's pre-bake the data by creating a copy of the table sorted in a way that we can find the right rows faster:
[crayon-6a7064301ec2a189266721/]
This builds a separate copy of our table (also stored in 8KB pages) that looks like this:
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server.
In the next several posts, you’ll learn:
In the last episode, we looked at your index designs with the output of sp_BlitzIndex. You might have gotten a little overwhelmed what with all the different warnings (and all the learning resources!)
You might have thought, "Is there an easier way?"
In our last episode, we used sp_BlitzIndex @Mode = 2 to get to know the contents of our database, sorted our indexes from biggest to smallest, and started asking questions about leftover backup tables that we probably didn't need to keep around anymore.
Now that you've taken out the trash, let's have a closer look at what's left. This time, run sp_BlitzIndex without a Mode parameter:
[crayon-6a7064301fc29452284157/]
You get back a prioritized list of index design issues like:
So far in the DBA Training Plan, we've been working hard to make sure the contents of our MDF files are backed up, corruption-free, and secured. Now, uh...what's actually inside them?
Inside each of your database data files (MDFs, although technically you can use any extension you want, even PDF or MP3), SQL Server stores your data in 8KB pages. That's kilobytes - not megabytes, not gigabytes, but just mere kilobytes.
When you index a nullable field, are the rows with nulls stored in the index? It's easy enough to find out by creating a table with a nullable field, and then creating an index on it: [crayon-6a706430216c2704742138/] Here's the plan: Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner…
Azure SQL DB's Automatic Tuning will create and drop indexes based on your workloads. It's easy to enable - just go into your database in the Azure portal, Automatic Tuning, and then turn "on" for create and drop index:
Let's track what it does, and when. I set up Kendra Little's DDL trigger to log index changes, which produces a nice table showing who changed what indexes, when, and how:
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.
!erehT iH
I know what you're thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster.
Just watch the video.
Master Plan
Sometimes you see a query, and it's hitting one table, and then the query plan looks like a vase full of spaghetti.
Usually, there's a view involved.
This week, we're all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues.
Dawn Of The Data
I don't know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful to abide by, depending a bit on how they're used.
The more SQL Server knows about your data, the better your query plans can get.
Say you've got an app that's designed to store multiple companies in a single database - but you don't actually use it that way. All of the data in a given database is actually for the same company.
A Reminder
With a new baby in the house, I often find myself comparing her to various things.
Barnacles. Ham. Pugs.
This week, we're all about tuning indexes. So far, we've covered Brent's 5 and 5 Rule and The D.E.A.T.H. Method. Today, let's talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…
