SSMS graphical execution plan doesn't show IsNotNull seek predicate
The IsNotNull node under the SeekKeys node of execution plan XML is not being displayed at all in the graphical execution plan for an Index Seek. This makes it look like the IS NOT NULL portion of the predicate is being ignored unless you look at the XML.
SentryOne Plan Explorer correctly parses and displays the IsNotNull node - see the attached screenshot pe-vs-ssms.PNG. That's hovering over the Index Seek operator for the same execution plan in both tools.
The problematic XML looks like this in my example query:
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Comments]" Alias="[c]" Column="PostId" />
<Const ConstValue="(38892391)" />
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Comments]" Alias="[c]" Column="UserId" />
And here's the query, which was run against a local copy of the StackOverflow database:
CREATE INDEX ix_whatever ON dbo.Comments (PostId, UserId);
FROM dbo.Comments AS c
WHERE c.UserId IS NOT NULL
AND c.PostId = 38892391
AND 1 = (SELECT 1);
Note that the "IS NULL" version of this query looks correct in the graphical execution plan, because the IsNotNull node is removed, and the UserId ColumnReference is moved under the RangeColumns node in the XML.
You can see the difference in the XML here on Paste The Plan: https://www.brentozar.com/pastetheplan/?id=HJ3VUpSkV