(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
Today’s myth is a multi-parter especially for the folks in the Designing for Performance class that Kimberly’s teaching on the MS campus today – hellooooo!
The null bitmap keeps track of which columns in a record are null or not. It exists as a performance optimization to allow the Storage Engine to avoid having to read all of a record into the CPU when null columns are part of the SELECT list – thus minimizing CPU cache line invalidations (checkout this link for details of how CPU memory caches work and the MESI protocol). There are three pervasive myths to debunk here:
Myth #6a: The null bitmap isn’t always present.
The null bitmap is *always* present in data records (in heaps or the leaf-level of clustered indexes) – even if the table has no nullable columns – except in the special case where the record is only made up of non-NULL SPARSE columns. The null bitmap is *not* always present in index records (leaf level of nonclustered indexes, and non-leaf levels of clustered and nonclustered indexes).
Here’s a simple script to prove it:
CREATE TABLE [NullTest] ([c1] INT NOT NULL); CREATE NONCLUSTERED INDEX [NullTest_NC] ON [NullTest] ([c1]); GO INSERT INTO [NullTest] VALUES (1); GO EXEC sp_allocationMetadata N'NullTest'; GO
You can get my sp_allocationMetadata script from my post Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work.
Use the page IDs in the output from the script in the First Page column. Do the following:
DBCC TRACEON (3604); DBCC PAGE (foo, 1, 152, 3); -- page ID from SP output where Index ID = 0 DBCC PAGE (foo, 1, 154, 1); -- page ID from SP output where Index ID = 2 GO
From the first DBCC PAGE dump of the heap data record:
Slot 0 Offset 0x60 Length 11 Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Memory Dump @0x685DC060
From the second DBCC PAGE dump of the nonclustered index record:
Slot 0, Offset 0x60, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = No null bitmap Memory Dump @0x685DC060
Myth #6b: The null bitmap only contains bits for nullable columns.
The null bitmap, when present, contains bits for all columns in the record, plus ‘filler’ bits for non-existent columns to make up complete bytes in the null bitmap. I already debunked this one with a long internals blog post last May – see Misconceptions around null bitmap size.
Myth #6c: Adding another column to the table always results in an immediate size-of-data operation.
The only times that adding a column to a table results in a size-of-data operation (i.e. an operation that modifies every row in a table) are:
- When the new column has a non-null default and is not nullable
- When the new column has a non-null default, IS nullable, and you’re using SQL Server 2008 R2 or earlier
In all other cases, the Storage Engine remembers that there are one or more additional columns that may not actually be present in the records themselves. I explained this in a little more depth in the blog post Misconceptions around adding columns to a table.
Back to a single myth-a-day for tomorrow – today was a bulk-bin special!