SHOWPLAN does not display a warning but "Include Execution Plan" does for the same query

I was running the following query in SSMS and it showed me a warning in the "Include Execution Plan" window

select CLIENT_GUID, PMT_AMOUNT, DATE_COMPLETED
from
(
    select
     payment.CLIENT_GUID, payment.PMT_AMOUNT, hist.DATE_COMPLETED, ROW_NUMBER() over (partition by payment.client_guid order by payment.deposit_date desc, payment.create_date_time  desc) rn
     from 
     trnPMT payment WITH (NOLOCK)
     inner join trnHistory hist WITH (NOLOCK) on payment.TRANS_GUID = hist.TRANS_GUID
     Where 
     payment.REVISED = 0 and
     payment.mpmt_guid <> '00000000-0000-0000-0000-000000000000'
) pmt
where pmt.rn = 1

The warning was "Operator used tempdb to spill data during execution with spill level 1"

(Plan XML here)

I went to investigate more, but neither doing SET SHOWPLAN_ALL ON nor SET SHOWPLAN_XML ON show the warning in their versions.

SHOWPLAN_ALL:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+----------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+--------------------+
|

This:

SET SHOWPLAN_XML ON;
GO
SELECT * FROM sys.objects;
GO

Is equivalent to pressing Display Estimated Execution Plan on the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you use Include Actual Execution Plan (Ctrl + M).

The spill warning is only a runtime warning. There is no way that SQL Server can know, when displaying the estimated plan, that a spill will happen at runtime. This is because a spill is caused by factors that might only be present during certain invocations of the query (for example, when there is memory pressure). The estimated plan knows roughly how much memory it's going to ask for, but it can't know until execution that it isn't going to get it.

As an aside, may I recommend* our free tool, SQL Sentry Plan Explorer? I think it provides much more obvious information than Management Studio. I recently wrote a lengthy blog post that can act as a tutorial, and Jonathan Kehayias has a great PluralSight course on it as well.

* Disclaimer: I work for SQL Sentry.

StackExchange Database Administrators Q#61369, answer score: 4