SQL Server Magazine

In last month's column, "The Fill-Factor Truth," I demonstrated how you can use two undocumented commands—DBCC IND and DBCC PAGE—to answer the question, What is the difference between a fill factor of 0 and fill factor of 100? Using the DBCC IND command, I found the page numbers of the pages within each level of an index; I then used the DBCC PAGE command to examine each page's contents to see how many rows were on the page. For my investigations, I didn't even need to see the data on the page; I only needed to see the page header that contained a value called m_slotcnt, which denotes the number of rows on the page. Using the m_slotcnt value, I established that as of SQL Server 7.0, no difference exists between a fill factor of 0 and a fill factor of 100.

My next test was to run the same script I used to create the table and its clustered index with a fill factor of 100, but this time I added the PAD_INDEX option:

CREATE UNIQUE CLUSTERED INDEX
 indx1 on ff1(col1)
WITH FILLFACTOR = 100, PAD_INDEX
GO

Using DBCC IND and DBCC PAGE, I determined that the PAD_INDEX option caused SQL Server to completely fill all levels of the index instead of leaving an extra row—as it would if I didn't specify this option.

When you use DBCC PAGE to look at the m_slotcnt values on the pages in this new index, you'll see that all the pages except one are full (i.e., contain 20 rows). The table in my example had 1000 rows, so it should have 50 completely full data pages. The DBCC IND output from last month shows that this table did in fact have 50 data pages. (You can determine this fact by counting the number of pages in the output where the PageType value is 1.) However, the first level of index pages contains one index row for each data page, resulting in 50 index rows, so you'll have two full index pages and one page with 10 rows.

You can identify the last page of an index by looking at the m_nextPage and m_prevPage values in the page header. When I ran the above script and created a clustered index with a fill factor of 100 and PAD_INDEX enabled, the non-root pages of the index were 310, 321, and 284. However, page 321 isn't the last page of the index as you might expect it to be. Using DBCC PAGE to look at page 321, I found that the value for m_nextPage in the page header was (1:310), which means file 1 and page 310. The m_prevPage value for page 321 was (0:0), which means that 321 was the first page in its index level. Page 284 had an m_nextPage value of (0:0) and an m_prevPage value of (1:310), so it was the last page in the index level, and its value for m_slotcnt was 10, as I expected.

A New Mystery
Something interesting happened to me just as I was finishing last month's column. A colleague who teaches SQL Server classes called with a question from one of her students. According to the student, an instructor in a previous SQL Server class had told the class that if SQL Server inserts a row that causes a page to split but then rolls back the INSERT transaction, SQL Server usually leaves the page split. (I agreed with the statement.) But the former instructor had gone on to say that if the index into which you're inserting a new row was created with a fill factor of 100, SQL Server undoes the split if it rolls back the transaction. I was positive that the former instructor was incorrect in the second half of her premise, but I decided to prove it. For this month's column, I wanted to use some additional fill-factor examples about page splitting, so this problem was right on time.

   Prev. page   [1] 2     next page