Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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:

<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Comments]" Alias="[c]" Column="PostId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(38892391)">
<Const ConstValue="(38892391)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
<IsNotNull>
<ColumnReference Database="[StackOverflow2010]" Schema="[dbo]" Table="[Comments]" Alias="[c]" Column="UserId" />
</IsNotNull>
</SeekKeys>

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);
GO

SELECT COUNT(*)
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

8 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Joshua Darnell shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base