I have a query such as:
select something from somewhere join stuff on condition join more_stuff on condition where somewhere.sometime is null and somewhere.someone is null and stuff.whatever is null
and so on.
I try nonclustered group indexing
somewhere.someone, the query slows down. I read somewhere that MSSQL keeps a bitmap of nulls for each row.
The question is, how to speed up the query so that access to rows with the specified
null values is faster and indexed? (so you only scan rows with the specified
nulls, not full table scan) Can I somehow take advantage of this
null bitmap ?
NULL bitmap is indeed used for query optimization that involve columns with NULL values, but there are a few considerations here:
- NULL bitmap is not present on nonclustered indexes (you’ll see why), on the other hand it is always present on the leaves of clustered indexes, and heap tables.
- NULL bitmap cannot be built for index records, but exclusively for data records (this is why you cannot have it for nonclustered indexes). That means you can have a NULL bitmap either on the table (organized as heap) or on a clustered index (leaves).
- NULL bitmap is not present for tables with only SPARSE columns.
You should check if you have a NULL bitmap indeed. My guess is that yours is missing. You could use DBCC to check the record attributes:
Open a query window and issue:
DBCC IND (DATABASE_NAME, 'TABLE_NAME', -1) DBCC TRACEON (3604) DBCC PAGE (DATABASE_NAME, PageFID, PagePID, 3) DBCC TRACEOFF (3604)
where PageFID and PagePID are displayed by the first command
DBCC IND then check for the
Record Attributes and check if
NULL_BITMAP is present.
If there isn’t any, then create a clustered index on the table, and check again.
You could also give us the execution plan, and the actual definition of the table (with dummy column names if confidential).