In "More Fill-Factor Mysteries," May 2001, I demonstrated that SQL Server performs a page split when it needs to insert a row on a full page. To determine whether a split occurred, I examined the output from the DBCC IND command, which shows how many pages are in a table. If the number of pages increases after you insert one row, you know that a split occurred.
Page splitting raises many interesting questions, such as, When a split occurs, which rows does SQL Server move to the new page and which ones stay on the original page? You might also want to know where the split occurs; does SQL Server split the page in half?
If so, does "half" mean half the number of rows or half the number of bytes? Before I show you some techniques you can use to discover the answers to these questions for yourself, let's examine the DBCC PAGE command in more detail.
Splitting the Data Page
Page splits can occur anywhere SQL Server needs to keep data in an ordered sequence, so pages can split at any level of a clustered or a nonclustered index. A data-page split is the most common type of split, and it's probably the only type that you need to be concerned with.
Data-page splits introduce most of the fragmentation that can slow down an ordered data scan. (Ordered scans occur whenever you need to access a range of data in the order of the clustered index key.) Because the leaf level of a clustered index is the data itself, SQL Server must keep the data rows in sorted order according to which table columns make up the index keys. If no clustered index exists, SQL Server places a newly inserted row on any page that has room for that row. Therefore, data pages split only when a clustered index exists on the table and only as a result of insert activity.
Although only insert activity causes splits, that activity can result from either an UPDATE statement or an INSERT statement. If an updated row can't remain on the same page after the update, SQL Server performs the update operation as a delete of the original row followed by an insert of the new version of the row. The insertion of the new row might then cause a page split.
Splitting a data page is a complicated operation. When a new row needs to go on a certain page but that page doesn't have room, SQL Server allocates a new page. SQL Server must then determine whether to link in the newly allocated page before the full page and move rows from the beginning of the full page to the new page, or to link in the new page after the full page and move pages from the end of the full page to the new one.
Let's look at what happens to a page when it splits. The code in Listing 1 creates a table containing both large and small rows. The first column is the primary key on which the code builds a clustered index. The value in this column determines a row's position on a data page. The second column is a variable-length character column. If filled to their maximum length of 1600 bytes, only five of these rows will fit on a page.
After creating the table and populating it with four large and four small rows, you can find its first page by looking at the column called first in the sysindexes table.
SELECT first FROM sysindexes WHERE id = OBJECT_ID ('splitrows')
You have to convert the address of the table's first page from a hexadecimal to a decimal value, then use DBCC PAGE to examine the page's contents. For instructions about performing this conversion, see the sidebar "Using DBCC PAGE."
To run DBCC PAGE, you must first turn on traceflag 3604, which directs SQL Server to send all special DBCC output to the client application—in this case, Query Analyzer. If the value in the sysindexes.first column for the splitrows table were 215 decimal, you could run the following commands to see the data on the page:
DBCC TRACEON(3604) GO DBCC PAGE(pubs, 1, 215, 1, 1) GO
In the DBCC PAGE output, character columns are the easiest type to read because SQL Server displays them in human-readable format. Because the page contains more than 8000 bytes of data, I won't show you the output. But if you scan through the right side of the output, you'll see the character data in column b. The large rows take up about 100 lines of output; the small rows take up about 2 lines.
In the page header, note the two values for the previous and the next pages. Because this table contains only one page, both m_prevPage and m_nextPage show a 0, meaning that no other pages exist.
Now let's insert another large row and find out what happens to the page:
INSERT INTO splitrows
VALUES (22, REPLICATE('x', 1600))
GO
DBCC PAGE(pubs, 1, 215, 1, 1)
GO
This new row needs to go right after the four original large rows, but it completely fills the page, so the table no longer has room for the four small rows. When you examine the original page after the insert takes place, you'll see only the five large rows. The page header now contains a nonzero value for m_nextPage in the format file_number:page_number, meaning that SQL Server added a new page after the original. For example, 1:224 means file 1, page 224 (decimal). If you use DBCC PAGE to examine the new page, you'll see that it contains the original four small rows.
In this case, the first few rows stayed on the original page, and SQL Server linked a new page in after the original. Now use the code in Listing 2 to change the table so that the small rows are before the large rows. You get slightly different behavior if you insert a new large row between the small rows and the large rows:
INSERT INTO splitrows
VALUES (22, REPLICATE('x', 1600))
GO
Using the following command to check the page data
DBCC PAGE(pubs, 1, 215, 1, 1) GO
shows that the original page (215) now has the original large rows with keys 25, 30, 35, and 40, but it doesn't have the new large row with key 22. The page header shows no pages following the original page (m_nextPage is 0), but a previous page exists. Using DBCC PAGE to look at the contents of the page that m_prevPage points to, you'll find the original four small rows and the new large row. In this case, SQL Server added the new page that it allocated for the split before the table's original full page.
How It Works
In both of the previous examples, you inserted a new row between the original large rows and the original small rows. But what happens if you insert a new large row between two of the small ones? For example, what happens if you use the code in Listing 1 to create the original table, then insert a large row with a clustered key value of 33? In that case, the four large rows and one small row stay on the original page; the new page has one small row, followed by the new large row, followed by the last two original small rows. For a new row with a clustered key value of 37, I found that two of the original small rows stay on the original page, and the new page contains one small row, the new large row, then the last original small row.
SQL Server decides where to link the new row into the table based on an estimate of the amount of data it will have to move, and it tries to minimize the logging overhead. So when the first few rows on a page are smaller, SQL Server moves them to the new page, which it adds before the original page. If the larger rows are at the beginning of the page, they stay on that page, and the smaller rows, which have less data to log, are the ones that move.
The algorithm for determining exactly where the split occurs isn't easily quantifiable. But it seems to work something like this: After SQL Server decides whether to add the page before or after the original page, it determines where the new row will go in the ordered sequence, then it decides where to make the split. For example, the first table had keys 5, 10, 15, and 20 as large rows and 25, 30, 35, and 40 as small rows. Then I inserted key 22 as a large row, but the page had no room for this new row. So the keys needed to be in the sequence 5, 10, 15, 20, 22, 25, 30, 35, 40. SQL Server placed the first row, 5, on the first page, and the last row, 40, on the second page. SQL Server then alternated back and forth, taking one row for the first page (in this case, the next row has key 10) and one row for the second page (key 35). When one page became full, all the remaining rows went on the other, non-full page. That explanation isn't perfect, and some cases appear to have additional variations, but it seems close to an explanation of how SQL Server determines the split point.
Double Splits
Finally, let me show you an example of a different type of split. If column b in my example table had a maximum length of 8000 characters, I could insert a new row such that no two-way split would accommodate all the rows in order. The code in Listing 3 creates a table only slightly different from the previous two, then inserts exactly the same rows. The table originally has no rows containing 8000 bytes in column b. If you then insert a row with an 8000-byte column immediately after the first large row, SQL Server can't accommodate the insert with just one split:
INSERT INTO splitrows
VALUES (7, REPLICATE('x', 8000))
GO
Using DBCC PAGE to examine the original page shows that the page now contains the last three original large rows and the four small rows. Its previous page contains only the new extra-large row, and the page before that one holds the first original large row. If possible, SQL Server tries to avoid having to allocate two new pages. But sometimes, as in this example, a double split is unavoidable because SQL Server needs to maintain the row order in the clustered index.
Typically, a page split isn't too expensive if it occurs once, but if you get hundreds of page splits a minute, you'll want to reduce this frequency in your production system, at least during peak usage times. Using the Performance Monitor counter Page Splits/sec, which you can find under the SQLServer:Access Methods object, you can monitor the number of page splits per second to see whether it increases over time or starts out high immediately after you create a clustered index. To avoid system disruption during busy times, you can reserve some space on pages by using the FILLFACTOR clause when you create a clustered index on existing data.
You can use the FILLFACTOR clause to your advantage during your least busy operational hours by periodically recreating the clustered index with a FILLFACTOR value that leaves enough room on your data pages to minimize splitting until you have time to rebuild the index. If your system has no slow times, you can use DBCC INDEXDEFRAG (SQL Server 2000 only) to reorganize an index's pages and readjust the FILLFACTOR without making the entire table unavailable. For more information about fragmentation and defragmenting, see "Keep SQL Server Up and Running," December 2000.
