I know this is gonna sound crazy, but let's say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let's say your application frequently ran a query looking for dates in that column, like this: [crayon-6a61ea575d6d3706834760/] Even if…
In my last post, we started with a memberships table, and each membership had start & end dates. I'm going to create the table and populate it with everyone having an active membership - their StartDate is the same as their Stack Overflow account creation date, and their EndDate is around a year or two…
Say you've got a memberships (or policies) table, and each membership has start & end dates:
[crayon-6a61ea5768d5b369284140/]
If all you need to do is look up the memberships for a specific UserId, and you know the UserId, then it's a piece of cake. You put a nonclustered index on UserId, and call it a day.
This week, our SQL ConstantCare® back end services started having some query timeout issues. Hey, we're database people - we can do this, right? Granted, I work with Microsoft SQL Server most of the time, but we host our data in Amazon Aurora Postgres - is a query just a query and an index just…
When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let's do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space.
I'm going to measure each method 3 ways:
In our last episode, I introduced the concept of scan predicates: execution plan operations that weren't able to seek directly to the rows they needed. Let's take another query:
[crayon-6a61ea576aebe948189582/]
If we ONLY have the gray pages index on LastAccessDate, Id, DisplayName, and Age, our query plan looks like this:
I'm going to narrate this from bottom up because it makes for easier storytelling:
We've been working with the clustered index of the Users table, which is on the Identity column - starts at 1 and goes up to a bajillion:
And in a recent episode, we added a wider nonclustered index on LastAccessDate, Id, DisplayName, and Age:
[crayon-6a61ea576b18a703999636/]
Whose leaf pages look like this:
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-6a61ea576b40e464775592/] And we had this index: [crayon-6a61ea576b415430757588/] 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-6a61ea576b6c8703131423/]
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-6a61ea576bda0091597381/] 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 couple of queries, we've been using a simple query to find the Ids of everyone who accessed the system since mid-2014:
[crayon-6a61ea576bfce750729503/]
But Ids alone aren't all that useful - so let's add a few more columns to our query:
[crayon-6a61ea576bfd3567670675/]
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-6a61ea576c448673471512/]
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-6a61ea576c44e461630423/]
This builds a separate copy of our table (also stored in 8KB pages) that looks like this:
In the last episode, I talked about knowing when it's time to scale out: identifying when our data is getting to be so large that we have to split it across multiple servers, and I explained why that is so challenging. But what about table partitioning - SQL Server's ability to break up a single table into smaller ones on the same server?
Before I give you the link to the next research paper in this week's series, I wanna give you a few questions to think about: If you had to build something to automatically add indexes to SQL Server databases, where would you start? What data would you use to make your decisions? How would you…
This week, I'm sharing some of my favorite papers that I've read. Sometimes they're about future technologies that haven't shipped yet - and may never ship! Sometimes, like this one, they're not from Microsoft at all, but from someone else in the industry who's explaining a problem that we face in SQL Server. SQL Server…
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-6a61ea576ceb7018604003/]
You get back a prioritized list of index design issues like:
In the last episode of the DBA Training Plan, we talked about what's inside the MDF file: a series of pages that store a database's tables. Now, it's time to check out the contents of YOUR database to understand what's using the most space, what's getting used the most often, and what you might be…
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.
