You know about page splits... if a table has a clustered index, any new row has to be inserted into the appropriate page, based on the clustered index key order. However, if the page where the new row belongs is full, SQL Server will split the page into two pages, and then put the new row on one of the now half empty (I know, I know, I should say "half full") pages.
But what if the new row is bigger than 4000 bytes (half a page) in size? I always knew that sometimes a page might have to split into three to allow big rows to be inserted, but I just found out it could be worse than that. If a page is split into two, and the new row still won't fit, one of the half full pages is split again. But if the row is so big, it still won't fit, another split could occur. And if the row is still too big.... well, you get the idea. One of the SQL Server engineers at Microsoft made note of what he called a 'corner case' where one page split into 17!
I read about that right before I shut down my machine one night, but I couldn't stop thinking about it. I woke up early the next morning, and my first thought was, "Why am I such a geek?" But I am what I am, and I was still thinking about a 17 way split. I wondered if I could duplicate that...So I got up and started trying. I spent several hours on it, but the most I could get was one page splitting into 10. It's always good to have something to work towards. Maybe next time I'm on a long plane flight (see my next year's schedule for possibilities), I'll try some more.
So here's how I did it. I probably should have called the table Sybil, but I called it split_page instead.
You can use any database; I used tempdb.
-- Create the table
USE tempdb;
GO
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'split_page')
DROP TABLE split_page;
GO
CREATE TABLE split_page
(id int identity(0,2) PRIMARY KEY,
id2 bigint default 0,
data1 varchar(33) null,
data2 varchar(8000) null);
GO
-- fill page until no more rows fit
INSERT INTO split_page DEFAULT VALUES;
GO 385
-- verify that there is only one data page
DBCC IND(tempdb, split_page, -1);
GO
DBCC IND should return one IAM page (PageType = 10) and one data page (PageType = 1). If you want to see how full the page is, you can run DBCC PAGE and look at just the header. For your third parameter to DBCC PAGE, you should use the number for the data page that DBCC IND returned.
DBCC TRACEON(3604);
DBCC PAGE(tempdb, 1, 177, 0);
GO
The header has a value it it called m_freeCnt, and it told me there were only 11 free bytes left on the page, which is not enough for another one of these rows. (The INT primary key is always 4 bytes, and the BIGINT is always 8, so even without any overhead bytes, we need more than 11.)
-- Now insert one more row, this time filling the VARCHARs to the maximum length.
SET IDENTITY_INSERT split_page ON;
GO
INSERT INTO split_page (id, id2, data1, data2)
SELECT 111, 0, replicate('a', 33), replicate('b', 8000);
GO
SET IDENTITY_INSERT split_page OFF;
GO
When you look at DBCC IND again, you should see that the table now has 10 data pages! (Plus one IAM page, and one index page for the clustered index root, which has PageType = 2)
DBCC IND(tempdb, split_page, -1);
GO
Here's my output:
(I really don't know how to make the screen shot any clearer. If you click on it, it should open in a browser window by itself and be a little easier to read. If I try to enlarge it when pasting it here, it becomes very blurred.)
Have fun!
~Kalen
Source Click Here.
Post a Comment