Some Innocuous Queries
Check out these basic queries, run against the ubiquitous Stack Overflow. Basic like pumpkin spice. Or something with a high pH. Or just, you know, straightforward.
SELECT COUNT(*) FROM dbo.Comments AS c WHERE c.UserId IS NULL AND c.PostId = 38892391 AND 1 = (SELECT 1) SELECT COUNT(*) FROM dbo.Comments AS c WHERE c.UserId IS NOT NULL AND c.PostId = 38892391 AND 1 = (SELECT 1)
The very perceptive reader will note that the only difference between them is the predicate (“IS NULL” vs “IS NOT NULL”) on the UserId field. Field. Column. Whatever, you get it.
Things get a little more interesting when you look at the execution plans in SSMS. They’re both the same general plan shape:
But the predicates on these index seeks tell a different story:
Yes, this was puzzling enough to warrant a freehand interrobang.
Not that it’s super-important, but the index you see there is this little guy:
CREATE INDEX IX_PostId_UserId ON dbo.Comments (PostId, UserId);
You can clearly see the
IS NULL predicate is present in the seek predicates for the first query, but the
IS NOT NULL predicate is completely omitted from the seek predicates of the second query. This seems…weird.
At this point I yelled “enhance!” at the execution plan, but nothing happened. So I decided to look at the XML.
Check out this snippet from the second plan:
<SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference ... Table="[Comments]" Alias="[c]" Column="PostId" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="(38892391)"> <Const ConstValue="(38892391)" /> </ScalarOperator> </RangeExpressions> </Prefix> <IsNotNull> <ColumnReference ... Table="[Comments]" Alias="[c]" Column="UserId" /> </IsNotNull> </SeekKeys>
So there’s that sneaky
IS NOT NULL seek predicate on the UserId column. It really is there, it’s just not being displayed in the graphical execution plan.
IS NULL query (that displays correctly), the “UserId” seek predicate is included in the “RangeColumns” and RangeExpressions” nodes, and there is no “IsNotNull” node. So it appears the display issue is limited to that “IsNotNull” node of the XML.
However, SentryOne Plan Explorer correctly identified and shows the
IS NOT NULL seek predicate:
In defense of SSMS, it does show the predicate if you look in the properties window.
Fine I Won’t Just Whine About It
In addition to this fascinating blog post, I’ve reported this on the SQL Server User Voice site here: SSMS graphical execution plan doesn’t show IsNotNull seek predicate
But I thought I’d post this to make sure folks know they’re not going crazy when they come across it. For reference, this affects SSMS v17.9.1 (build 14.0.17289.0). I didn’t go back and check other versions of SSMS, so I’m not sure if this is a regression, or if it’s always been the case.
By the way, thanks to Erik Darling for pointing this oddity out to me when he first came across it. It was a neat problem to track down.