Links from the video

• Contoso Retail DW - download it from Microsoft at this URL
• sp_indexdetail - grab it here

‘Did you know?’ FILLFACTOR edition

FILLFACTOR is backwards in maintenance plans

In this demo, I set the fillfactor using TSQL. In TSQL, fill factor is specified as the amount full, so FILLFACTOR = 95 is 95% full.

If you use maintenance plans in SQL Server, this is reversed. On the ‘Rebuild Index Task’ in a maintenance plan, the graphic interface offers you the option to “Change free space per page to ___%.” That is asking how much you would like to leave empty on the page. If you say “Change free space per page to 95%”, it generates TSQL with FILLFACTOR = 5.

Setting this incorrectly can be disastrous to performance, so be careful out there!

FILLFACTOR can be configured for intermediate pages using the PAD_INDEX option

I mention in the demos in this course that FILLFACTOR only applies to the leaf of the index. I have yet to run into a case where I needed to change that — but if you do run into such a weird case, setting PAD_INDEX = ON will apply FILLFACTOR to intermediate levels of the index.

Transcript

Our demo today uses a Microsoft sample database called the Contoso Data Warehouse. You can download it from Microsoft at this URL.

This script walks you through a sample command to restore it; you’ll just need to change file locations to match your test instance of SQL Server. Then we configure Query Store, just in case it comes in handy.

I have already set up my database, so I’m going to use ContosoRetailDW, and let’s take a look at the table where we will be creating an index.

The table is named DimEmployee

The table has quite a few columns– we won’t be using all of these in our nonclustered index. It contains information for all of the employees of Contoso, and we’ve got an employee key, we’ve got their names, their hire dates, lots of information about the folks who work at Contoso.

When we look at indexes in SQL Server, the built in sp_helpindex command is only a little bit helpful

It does at least give us information on how many indexes we have on the table. Right now the table only has one index named PK_DimEmployee_EmployeeKey. It is a clustered index on the table. It’s also a primary key. Because it is a clustered index we know this is the table itself, and sp_helpindex does at least give us the list of keys. That clustered index on the table only has one key column, it is EmployeeKey.

So the table itself is all sorted by that EmployeeKey column, which is an integer. If we want to find an employee by, say, last name, the clustered index of the table is not going to help us.

There are not yet any nonclustered indexes on the table.

Let’s create our nonclustered index

We are going to go ahead and create one. Our nonclustered index, I’ve given it a name. And I’ve said I want it to have three key columns. I’m creating it on the DimEmployee table, and here are my three key columns: LastName, then FirstName, then EmployeeKey. I don’t have to have includes, but in this case I am saying I do want to have some included columns. I want three included columns and they are MiddleName, the EmergencyContactName, and the Status.

I’m using a crazy (bad) low fillfactor

Now at the end of this command I’m doing something a little bit wacky.

This is just for the purpose of demo. This is a small index, and I want to show you: what if we need an intermediate level in our index? What I’m telling SQL Server is, on the leaf of this index, leave a ton of empty space. In TSQL when I say with FILLFACTOR=1, I’m saying only fill it up 1%. For the leaf of the index– and we’ll see what that is– leave 99% of the pages empty.

This means we have a lot of empty space on those pages.

The empty space on the pages will be both in storage and in memory. so if we were to do this on a lot of indexes and/or they were big we would waste a TON of space we would bloat out our indexes.

This is just for the purposes of demoing and making it an adventure to explore this little index. It creates our index quickly.

So much for sp_helpindex

We could try to look at this with sp_helpindex, but sp_helpindex is only a little bit helpful. It does have a line here for both of our indexes. Here’s the clustered index, and now here’s the nonclustered index of the table. But it only gives us the index name, a description and then the keys.

I just scrolled all the way to the right here– it doesn’t give me information on: are there included columns? It doesn’t even give me the ID of the index, in case I want to use that in some of my scripts.

I have a custom script, sp_indexdetail

Because of that, I’ve written a custom procedure that looks at the dynamic management views and pulls the index id from sys.indexes. It pulls out of the partition stats DMV information on how many pages it has. It connects to a lot of different dynamic management views. You can grab it and get it; you can write your own.

Essentially, I just want to get some information about my table. Especially, I’m going to need my index ID that this was assigned for some of my later commands.

Some metadata for our nonclustered index

Looking at the details on this, I have my nonclustered index here at line two. I can verify that from the index create statement as well as the name. This was assigned index ID 28.

That doesn’t mean there’s 28 indexes on the table: there’s only two there. Before I restored this database, there used to be other indexes that were dropped. So I have index ID 28, which is just a sign of indexes past.

My index only has 244 pages. Converting that, this is zero gigabytes. This is a very small index.

Notice that the clustered index of the table itself is only 17 pages, because it doesn’t have that crazy fill factor. I have 244 pages because I said, on the leaf of my index fill it only 1% full– and it was only able to put one to two rows per page. Right? I have 293 rows, I have 244 pages.

A lot of these pages in the leaf of my index– are an 8K page that only has one little row on it, and a lot of empty space, just to make our exploration interesting.

I’m going to use that index ID in a command to pull data from the sys.dm_db_index_physical_stats dynamic management view

This will give me a picture of this structure of my index, because I’m saying for my current database, I want to look at the DimEmployee table. And I specifically want to look at index ID 28. This is not a partitioned index, so I have null for that parameter. I want a detailed look, a detailed summary of the physical stats for this index.

This DMV says: here’s a little picture of your index. We have three levels to this index. When you look at index levels, level zero is always the ‘leaf’ of the index. Like a tree, all of the data is in what’s called the leaf of the index. And it’s the leaf of our index that has 244 pages in it. It’s 1.9 megabytes.

Now the level two of our index is the top or bottom of our index, depending on which way your tree is pointing. It only has one page. This is the root page of our index, and when SQL Servers uses this index it goes to that root page to find its way through the index.

We have one intermediate level of the index: that’s level one, and it has two pages on it.

If I go back and change my column width here, we can see information about the average page space used in percent. That’s one of the things our dynamic management view gives us. This is the leaf of our index, this is level zero with 244 pages. It is only 1.3% full. That’s because I put that super low fill factor on my index.

Let’s take a look at some of these pages next.