{"id":726,"date":"2010-04-06T08:28:00","date_gmt":"2010-04-06T08:28:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(630)-three-null-bitmap-myths.aspx"},"modified":"2017-08-01T10:39:56","modified_gmt":"2017-08-01T17:39:56","slug":"a-sql-server-dba-myth-a-day-630-three-null-bitmap-myths","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-630-three-null-bitmap-myths\/","title":{"rendered":"A SQL Server DBA myth a day: (6\/30) three null bitmap myths"},"content":{"rendered":"

(Look in the Misconceptions<\/a> 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<\/a>, plus my online Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n

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!<\/p>\n

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<\/em> list – thus minimizing CPU cache line invalidations (checkout this link<\/a> for details of how CPU memory caches work\u00a0and the MESI protocol). There are three pervasive myths to debunk here:<\/p>\n

Myth #6a:<\/strong> The null bitmap isn’t always present.<\/em><\/p>\n

TRUE<\/em><\/strong><\/span><\/p>\n

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).<\/p>\n

Here’s a simple script to prove it:<\/p>\n

\r\nCREATE TABLE [NullTest] ([c1] INT NOT NULL);\r\nCREATE NONCLUSTERED INDEX [NullTest_NC] ON [NullTest] ([c1]);\r\nGO\r\nINSERT INTO [NullTest] VALUES (1);\r\nGO\r\n\r\nEXEC sp_allocationMetadata N'NullTest';\r\nGO\r\n<\/pre>\n
You can get my sp_allocationMetadata<\/em> script from\u00a0my\u00a0post Inside The Storage Engine: sp_AllocationMetadata – putting undocumented system catalog views to work<\/a><\/em>.<\/p>\n
Use the page IDs in the output from the script in the First Page<\/em> column. Do the following:<\/p>\n
\r\nDBCC TRACEON (3604);\r\nDBCC PAGE (foo, 1, 152, 3); -- page ID from SP output where Index ID = 0\r\nDBCC PAGE (foo, 1, 154, 1); -- page ID from SP output where Index ID = 2\r\nGO\r\n<\/pre>\n
From the first DBCC PAGE<\/em> dump of the heap data record:<\/p>\n
\r\nSlot 0 Offset 0x60 Length 11\r\n\r\nRecord Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP\r\nMemory Dump @0x685DC060\r\n<\/pre>\n
From the second DBCC PAGE<\/em> dump of the nonclustered index\u00a0record:<\/p>\n
\r\nSlot 0, Offset 0x60, Length 13, DumpStyle BYTE\r\n\r\nRecord Type = INDEX_RECORD           Record Attributes = No null bitmap\r\nMemory Dump @0x685DC060\r\n<\/pre>\n
Myth #6b:<\/strong> The null bitmap only contains bits for nullable columns.<\/em><\/p>\n
FALSE<\/em><\/strong><\/span><\/p>\n
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<\/a><\/em>.<\/p>\n
Myth #6c:<\/strong> Adding another column to the table always results in an immediate size-of-data operation.<\/em><\/p>\n
FALSE<\/em><\/strong><\/span><\/p>\n
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:<\/p>\n