Sql-server – What happens at the data page level when the record length changes

data-pagesdatabase-internalssql serversql-server-2008

I've been digging around the net at work when I can for a few days now trying to make sense of how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

At a high level you could think: I can just put a pointer at the end that points to the new column. However, at the page level aren't data pages filled with individual records? Wouldn't adding a column mean that every page that was already full would require a split?

Even pages that weren't full would require a lot of data juggling to add that column to the end of each record, update all the slot arrays, then cascade all the pointer changes through any existing indexes and/or the IAM and GAM pages?

The only thing I can think of is that all new column data is added to new pages, without the rest of the record, and pointers are added throughout the table tree structure to reference the new column pages. However, this seems like it would ruin spatial locality. If this is it, does the DBMS juggle data behind the scenes even when we don't specifically request a REBUILD?

I'm talking about the bit level of DBMS memory management with pages and asking how DBMSs are able to add a column (with or without allowing NULL values) to a set of existing records so quickly, even though the records already exist as a set of bits in a data page.

Best Answer

how a DBMS (SQL Server 2008 R2 and others) handles adding a column to the end of a huge table so quickly.

Well, there is a false assumption here, that being that adding a new column is always done quickly. This is not a true statement.

Now, when adding a column that allows NULLs, that can be done quickly as the meta-data of the Table definition gets updated but the NULL isn't physically added to the datapages in that moment. SQL Server can return the correct NULL to queries since it is logically obvious what the "value" is. When rows are inserted or updated, the records that are written to the datapages do include the NULL (for fixed-length columns, unless the SPARSE option was used for the new column or Data Compression is enabled on the Clustered Index). But the rest of the rows that have not been updated will not have the NULL physically added until an index REBUILD.

However, when adding a column marked as NOT NULL, then, prior to SQL Server 2012 (and even then, only if the new value was a runtime constant), then the actual value was written physically to the datapages in that moment, and that operation could take a looooooooooong time, depending on how many rows and/or how much data was in the table. You can find plenty of questions and articles about trying to overcome this issue as tables with many GBs of data and/or hundreds of millions of rows could take hours to add a new NOT NULL column.

Then came a truly wonderful new feature in SQL Server 2012 (Enterprise Edition-only, which also implies Developer Edition) whereby adding a new NOT NULL column with a default value could be an instantaneous, meta-data only operation, just like adding a column marked as NULL. The only caveats are that the datatype not be a LOB (e.g. MAX-types, XML, etc) or CLR-based type, and that the value be a runtime constant (i.e. mainly literal values). Something like NEWID() would not be instantaneous since it would need a different value per each row. But for values that are runtime constants, SELECT operations can easily get the correct value by just looking at the meta-data of the DEFAULT which gives the logically-obvious value.

The MSDN page for ALTER TABLE, in the Locks and ALTER TABLE section (under "Adding NOT NULL Columns as an Online Operation"), talks about this behavior.

Related Solutions

Sql-server – Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting

Well, do you want to keep track of all the versions of the image data, or not? If not, can't you specify only certain columns for CDC instead of just enabling all columns? That will let you keep track of the changes to other columns but ignore the image data changes.

In other words, if all you care about is the current state of the image data, you can get that from the base table. If you care about the previous states of the image data, there isn't any way to store that data without storing it somewhere.

Sql-server – Will FILLFACTOR = 99 slow down the SQL Server

Fill factor is critical in fragmentation

What does the author mean with "page splits occur when there is no room for data"?

There are basically two types of splits good page split or sequential page split which occurs when leaf level of index page is full and when new record is inserted page split occurs which results in allocation of new page and data is written in sequential order because new page is added at last of all pages.

The other one is bad page split or non sequential page split which occurs due to insert or update operation on a page resulting in page split in between. What happens is when record is updated a space for new record is created and this might splits page and move some of record on new page now this new page would be just after page that was updated hence severely creating a mismatch in order by which Logical clustering keys are arranged and how data pages are arranged. In fact depending on how fast new page can be allocated for page split it can also cause performance issue. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. This article aptly demonstrates how page split can occur. You can also read this article by Paul about how to see bad page splits. Both of these use undocumented commands if you have SQL Server 2012 and above you can use Extended events trace to track nasty page splits

One more drawback associated is when huge number of page splits occur they could saturate I/O by utilizing it. Consider a scenario where update causes page split now one I/O would be required for update to index key one for update to page(normally) it would also require additional I/O for new page addition and updating index after new page is created, on a busy system with not so upto date hardware this could cause performance issue

There is other way using DMV to find number of page splits happening in database but it wont tell you which one is good and which one is bad

--Script to check page split for index
SELECT
IOS.INDEX_ID,
O.NAME AS OBJECT_NAME,
I.NAME AS INDEX_NAME,
IOS.LEAF_ALLOCATION_COUNT AS PAGE_SPLIT_FOR_INDEX,
IOS.NONLEAF_ALLOCATION_COUNT PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(N'DB_NAME'),NULL,NULL,NULL) IOS
JOIN
SYS.INDEXES I
ON
IOS.INDEX_ID=I.INDEX_ID
JOIN
SYS.OBJECTS O
ON
IOS.OBJECT_ID=O.OBJECT_ID
WHERE O.TYPE_DESC='USER_TABLE'

Please let me know if you need further explanation

Best Answer

Related Solutions

Sql-server – Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting

Sql-server – Will FILLFACTOR = 99 slow down the SQL Server

Related Question