Data Structure Storytime

I'm going to commit a huge breach of blogger etiquette today: I'm going to read to you (more or less) directly out of SQL Server Books Online.  Whether you haven't read these important parts, or just need a refresher, everyone should get back to the basics once in a while.  And we're gonna rock this Miss Lippy style!

Understanding Pages and Extents: Once, there was a little database engine that needed to organize its database storage physical hard drive, so it could keep track of objects and data, and find rows easily. "I'll sort everything in my database into big pieces, and then into smaller units," said the database engine. So it did, starting with the largest, and separating it into smaller and smaller chunks:

• Database files (.mdf and .ndf)
• 8 page (64Kb) extents (which can be owned by a single object, or shared among objects)
• 8 kb pages, numbered 0 to n

All things in the database – data, indexes, and organization structures (like the GAM) – were stored in these 8Kb pages, and there was much rejoicing. Yay. 

Page Splits and Logical Fragmentation: So the users came, and they made a database with a table called "MyTable". They declared a clustered index on MyTable, and the little database engine sorted the table on the disk, in the order of the clustered index. Then the users began inserting data to the table. The users were happy.

Over time, the pages owned by MyTable became full of data. One day, a user inserted a row that was supposed to go into MyTable's page 2. But page 2 was full!  "Oh no!" said the database engine, "I'd better move some data off of that page!"  So it created a new page, and took the time to move half of the rows from page 2 to the new page. And it called that operation a "page split". The new row got inserted!  "Yay!" said the little database engine, "But could you get off the storytime format? It's wearing thin!"

Fine then.

From Reorganizing and Rebuilding Indexes: When this scenario happens again and again (as it does in any growing database that has nonsequential clustered indexes), you'll have more and more page splits – which are resource intensive. Plus, if an extent is full when a page split happens, the new page can't be created in the same extent; it must be created elsewhere, which move the page out of logical page order (remember, pages are numbered 0 to n). This is logical fragmentation – the pages are in a different order than their logical sort order. 

Happily ever after: There are strategies and solutions to prevent and fix logical fragmentation (see the further reading section), but we really must understand what these things actually mean before we go ALTER REINDEX ALL on our databases.

The end.

-Jen McCown
http://www.MidnightDBA.com

Further Reading:

• Everything in SQL Server Books Online on primary keys, clustered indexes, and defragmenting 🙂
• Kimberly Tripp "Index Defrag Best Practices"
• Paul randal "Why You Should Not Shrink Your Data Files"