sql-server optimize query with lots of null checks

Question

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.sometime and 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 ?

Answer 1

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).