Page Splitting & Rollbacks

April 9, 2009 by Michelle Ufford
Filed under: Internals, Performance & Tuning, Syndication 

So while I was at the grocery store last night, my mind wandered to SQL Server. This time, I was pondering what happens to a page split if the transaction is rolled back. I didn’t know the answer, but my guess was that the page split remained, since it would be less expensive for SQL Server to leave the data where it was sitting. Also, in theory, if the page split occurred once, it could need to occur again, so why undergo that same expense twice?

I decided to run a simple test to see what would happen. First, I created a really wide table and tossed 4 rows into it:

CREATE TABLE myTable
(
      id            INT PRIMARY KEY
    , wideColumn    CHAR(2000) 
);
 
INSERT INTO myTable
SELECT 1, REPLICATE('A', 2000) UNION All
SELECT 2, REPLICATE('B', 2000) UNION All
SELECT 4, REPLICATE('C', 2000) UNION All
SELECT 5, REPLICATE('D', 2000);

I left an open spot for id=3, so I can force a page split. Next, I looked at the page data using the script I posted in February.

Here’s what I saw:

Before

Before

Pay attention to the 2 items boxed in red. m_slotCnt tells us how many records are on the page, and m_nextPage tells us the address of the next page. As you may have guessed, a m_nextPage value of 0:0 means you’re looking at the last page allocated to the object.

Now let’s insert a record, roll it back, and see what happens:

BEGIN TRANSACTION;
 
INSERT INTO myTable
SELECT 3, REPLICATE('E', 2000);
 
ROLLBACK TRANSACTION;

I ran my DBCC Page command again and here’s what I saw:

After

After

As you can see, m_slotCnt is now 2, and m_nextPage is no longer 0:0 (although your actual page number will probably be different than mine). If I pull up the new page, I find my 2 relocated records, id’s 4 and 5.

So what’s this all mean? In short, page splits are NOT reversed when a transaction is rolled back. Why should you care? Well, you probably wouldn’t care much, unless you roll back a lot of transactions. But this is also a good thing to keep in mind if you have to abort a really large insert or update; if you don’t plan to re-execute the aborted script, you may want to defrag your indexes to fix the splits.

Source: http://sqlfool.com/2009/04/page-splitting-rollbacks/

Tags: , , ,

Comments

9 Comments on Page Splitting & Rollbacks

  1. Scott Whigham on Thu, 9th Apr 2009 7:30 am

    2. Very cool – thanks for the good info. I would’ve thought that it rolled everything back. Neat – thanks for sharing!

  2. Adam Machanic on Thu, 9th Apr 2009 10:16 am

    3. Great post!

  3. Bob Frasca on Fri, 10th Apr 2009 9:27 am

    4. Great post and interesting thing to keep in mind. I hope you didn’t forget the milk because you were thinking deep SQL Server thoughts in the grocery store. :-)

  4. Log Buffer #142: a Carnival of the Vanities for DBAs | Pythian Group Blog on Fri, 10th Apr 2009 9:42 am

    5. [...] Michelle Ufford, the SQL Fool, wrote, “[While] I was at the grocery store last night, my mind wandered to SQL Server.” (Yes, that happens to me too.) “This time, I was pondering what happens to a page split if the transaction is rolled back.” [...]

  5. RBarryYoung on Fri, 10th Apr 2009 9:35 pm

    6. “So while I was at the grocery store last night, my mind wandered to SQL Server…” This is why I always end up with Maraschino Cherry and Mayonnaise sandwiches. :-)

  6. Interview with Michelle Ufford | SQLBatman.com on Mon, 13th Apr 2009 4:39 pm

    7. [...] are indeed a “fool” when it comes to all thing MS SQL Server. You even think about SQL while shopping for groceries. What other interests do you have outside of the obvious [...]

  7. Gail on Tue, 14th Apr 2009 12:31 am

    8. Glad I’m not the only one who muses over SQL internals while shopping. It makes the entire shopping process so much less painful.

  8. balebond on Tue, 14th Apr 2009 11:45 am

    9. Bookmarked. Thanks for the sharing.

  9. Joshua on Sun, 3rd May 2009 7:06 pm

    10. Men always wonder what women are thinking about during… well.. now we know. ;)

Tell me what you're thinking...
and oh, if you've enjoyed the post, please consider subscribing to my blog.