{"id":1177,"date":"2007-10-01T01:53:53","date_gmt":"2007-10-01T01:53:53","guid":{"rendered":"\/blogs\/paul\/post\/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx"},"modified":"2013-01-01T19:36:51","modified_gmt":"2013-01-02T03:36:51","slug":"inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-the-storage-engine-using-dbcc-page-and-dbcc-ind-to-find-out-if-page-splits-ever-roll-back\/","title":{"rendered":"Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back"},"content":{"rendered":"


\n

Time for the first post in the Inside the Storage Engine<\/EM> series. I’m going to focus on SQL Server 2005 in this series and I’ll point out major differences between 2005 and previous versions. Please drop me a line if there’s something you’d like to see explained and demo’d.<\/P>
\n

Before jumping into how things work, I’d like to go over two commands I’ll be using a lot – DBCC PAGE and DBCC IND. These are both undocumented and unsupported commands, but are very safe to use as they’re used extensively inside and outside Microsoft when troubleshooting. Nevertheless, use at your own risk. They’re quite well known in the SQL community and I and others have publicized them before (I even demo’d them last year at ITForum in Spain).<\/P>
\n

To illustrate their use, I’m going to use a simple script I wrote to prove that page splits never roll back. I was having a discussion with someone a while ago about this question and the answer is always no. A page split occurs when an insert or update has to happen at a certain point in an index page, and there’s no room on the page to accomomodate the new or updated record. Page splits are done internally as separate ‘system’ transactions. Once a system transaction commits, it cannot be rolled back – even if the user transaction it was part of rolls back.<\/P>
\n

So, let’s run through the script. First thing to do is create a database containing a table with an index (as page splits only happen in indexes).<\/P>
\n


\n

USE<\/FONT><\/FONT> <\/FONT>MASTER<\/FONT>;<\/P><\/FONT><\/FONT>
\n

GO<\/P><\/FONT>
\n

IF<\/FONT><\/FONT> <\/FONT>DATABASEPROPERTY<\/FONT> <\/FONT>(<\/FONT>N<\/FONT>‘pagesplittest’<\/FONT>,<\/FONT> <\/FONT>‘Version’<\/FONT>)<\/FONT> <\/FONT>><\/FONT> 0 <\/FONT>DROP<\/FONT> <\/FONT>DATABASE<\/FONT> pagesplittest<\/FONT>;<\/P><\/FONT><\/FONT>
\n

GO<\/P><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>DATABASE<\/FONT> pagesplittest<\/FONT>;<\/P><\/FONT><\/FONT>
\n

GO<\/P><\/FONT>
\n

USE<\/FONT><\/FONT> pagesplittest<\/FONT>;<\/P><\/FONT><\/FONT>
\n

GO<\/P><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>TABLE<\/FONT> t1 <\/FONT>(<\/FONT>c1 <\/FONT>INT<\/FONT>,<\/FONT> c2 <\/FONT>VARCHAR<\/FONT> <\/FONT>(<\/FONT>1000<\/FONT>));<\/P><\/FONT><\/FONT>
\n

CREATE<\/FONT><\/FONT> <\/FONT>CLUSTERED<\/FONT> <\/FONT>INDEX<\/FONT> t1c1 <\/FONT>ON<\/FONT> t1 <\/FONT>(<\/FONT>c1<\/FONT>);<\/P><\/FONT><\/FONT>
\n

GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

Now I’m going to fill up a page in the index, but leave a gap in the c1 values so that I can force a page split by inserting the missing key value.<\/P>
\n


\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>1<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘a’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>2<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘b’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>3<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘c’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>4<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘d’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

— leave a gap at 5<\/P><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>6<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘f’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>7<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘g’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>8<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘h’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

INSERT<\/FONT><\/FONT> <\/FONT>INTO<\/FONT> t1 <\/FONT>VALUES<\/FONT> <\/FONT>(<\/FONT>9<\/FONT>,<\/FONT> <\/FONT>REPLICATE<\/FONT> <\/FONT>(<\/FONT>‘i’<\/FONT>,<\/FONT> 900<\/FONT>));<\/P><\/FONT><\/FONT>
\n

GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

I can find out what the first index page is using the DBCC IND command:<\/P>
\n


\n

DBCC<\/FONT><\/FONT> IND <\/FONT>(<\/FONT>‘pagesplittest’<\/FONT>,<\/FONT> <\/FONT>‘t1’<\/FONT>,<\/FONT> 1<\/FONT>);<\/P><\/FONT><\/FONT>
\n

GO<\/FONT><\/P><\/BLOCKQUOTE>
\n

This command list all the pages that are allocated to an index. Here’s the output in this case:<\/P>
\n

SEQA3.jpg<\/P>
\n

The columns mean:<\/SPAN><\/P>
\n