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

Question

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:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+----------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+--------------------+
|                                                                                                                                                 StmtText                                                                                                                                                 | StmtId | NodeId | Parent |      PhysicalOp      |      LogicalOp       |                                                                                                                       Argument                                                                                                                       |                                                          DefinedValues                                                          | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost |                                                                   OutputList                                                                    | Warnings |   Type   | Parallel | EstimateExecutions |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+----------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+--------------------+
| 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                                                                                                                                                                                                                                                                                         |      1 |      1 |      0 | NULL                 | NULL                 | 1                                                                                                                                                                                                                                                    | NULL                                                                                                                            | 9283.128     | NULL       | NULL        | NULL       | 40.03822         | NULL                                                                                                                                            | NULL     | SELECT   |        0 | NULL               |
|   |--Filter(WHERE:([Expr1004]=(1)))                                                                                                                                                                                                                                                                      |      1 |      2 |      1 | Filter               | Filter               | WHERE:([Expr1004]=(1))                                                                                                                                                                                                                               | NULL                                                                                                                            | 9283.128     | 0          | 0.09364839  | 39         | 40.03822         | [payment].[CLIENT_GUID], [payment].[PMT_AMOUNT], [hist].[DATE_COMPLETED]                                                                        | NULL     | PLAN_ROW |        0 | 1                  |
|        |--Sequence Project(DEFINE:([Expr1004]=row_number))                                                                                                                                                                                                                                               |      1 |      3 |      2 | Sequence Project     | Compute Scalar       | DEFINE:([Expr1004]=row_number)                                                                                                                                                                                                                       | [Expr1004]=row_number                                                                                                           | 195100.8     | 0          | 0.01560807  | 47         | 39.94458         | [payment].[CLIENT_GUID], [payment].[PMT_AMOUNT], [hist].[DATE_COMPLETED], [Expr1004]                                                            | NULL     | PLAN_ROW |        0 | 1                  |
|             |--Segment                                                                                                                                                                                                                                                                                   |      1 |      4 |      3 | Segment              | Segment              | [payment].[CLIENT_GUID]                                                                                                                                                                                                                              | NULL                                                                                                                            | 195100.8     | 0          | 0.003902016 | 47         | 39.92897         | [payment].[CLIENT_GUID], [payment].[DEPOSIT_DATE], [payment].[PMT_AMOUNT], [payment].[CREATE_DATE_TIME], [hist].[DATE_COMPLETED], [Segment1005] | NULL     | PLAN_ROW |        0 | 1                  |
|                  |--Sort(ORDER BY:([payment].[CLIENT_GUID] ASC, [payment].[DEPOSIT_DATE] DESC, [payment].[CREATE_DATE_TIME] DESC))                                                                                                                                                                       |      1 |      5 |      4 | Sort                 | Sort                 | ORDER BY:([payment].[CLIENT_GUID] ASC, [payment].[DEPOSIT_DATE] DESC, [payment].[CREATE_DATE_TIME] DESC)                                                                                                                                             | NULL                                                                                                                            | 195100.8     | 0.01126126 | 15.73772    | 55         | 39.92507         | [payment].[CLIENT_GUID], [payment].[DEPOSIT_DATE], [payment].[PMT_AMOUNT], [payment].[CREATE_DATE_TIME], [hist].[DATE_COMPLETED]                | NULL     | PLAN_ROW |        0 | 1                  |
|                       |--Hash Match(Inner Join, HASH:([payment].[TRANS_GUID])=([hist].[TRANS_GUID]), RESIDUAL:([TestDb].[dbo].[trnHistory].[TRANS_GUID] as [hist].[TRANS_GUID]=[TestDb].[dbo].[trnPMT].[TRANS_GUID] as [payment].[TRANS_GUID]))                                                          |      1 |      6 |      5 | Hash Match           | Inner Join           | HASH:([payment].[TRANS_GUID])=([hist].[TRANS_GUID]), RESIDUAL:([TestDb].[dbo].[trnHistory].[TRANS_GUID] as [hist].[TRANS_GUID]=[TestDb].[dbo].[trnPMT].[TRANS_GUID] as [payment].[TRANS_GUID])                                                       | NULL                                                                                                                            | 195100.8     | 0          | 14.81137    | 55         | 24.17609         | [payment].[CLIENT_GUID], [payment].[DEPOSIT_DATE], [payment].[PMT_AMOUNT], [payment].[CREATE_DATE_TIME], [hist].[DATE_COMPLETED]                | NULL     | PLAN_ROW |        0 | 1                  |
|                            |--Clustered Index Scan(OBJECT:([TestDb].[dbo].[trnPMT].[imp_clpk_trnPMT] AS [payment]), WHERE:([TestDb].[dbo].[trnPMT].[REVISED] as [payment].[REVISED]=(0) AND [TestDb].[dbo].[trnPMT].[MPMT_GUID] as [payment].[MPMT_GUID]{guid'00000000-0000-0000-0000-000000000000'})) |      1 |      7 |      6 | Clustered Index Scan | Clustered Index Scan | OBJECT:([TestDb].[dbo].[trnPMT].[imp_clpk_trnPMT] AS [payment]), WHERE:([TestDb].[dbo].[trnPMT].[REVISED] as [payment].[REVISED]=(0) AND [TestDb].[dbo].[trnPMT].[MPMT_GUID] as [payment].[MPMT_GUID]{guid'00000000-0000-0000-0000-000000000000'}) | [payment].[TRANS_GUID], [payment].[CLIENT_GUID], [payment].[DEPOSIT_DATE], [payment].[PMT_AMOUNT], [payment].[CREATE_DATE_TIME] | 231167.7     | 6.566088   | 0.2730604   | 80         | 6.839149         | [payment].[TRANS_GUID], [payment].[CLIENT_GUID], [payment].[DEPOSIT_DATE], [payment].[PMT_AMOUNT], [payment].[CREATE_DATE_TIME]                 | NULL     | PLAN_ROW |        0 | 1                  |
|                            |--Index Scan(OBJECT:([TestDb].[dbo].[trnHistory].[IX_trnHistory_DATE_COMPLETED_TRANS_GUID_TRANS_NUMBER] AS [hist]))                                                                                                                                                          |      1 |      8 |      6 | Index Scan           | Index Scan           | OBJECT:([TestDb].[dbo].[trnHistory].[IX_trnHistory_DATE_COMPLETED_TRANS_GUID_TRANS_NUMBER] AS [hist])                                                                                                                                                | [hist].[TRANS_GUID], [hist].[DATE_COMPLETED]                                                                                    | 503270       | 1.753495   | 0.553754    | 31         | 2.307249         | [hist].[TRANS_GUID], [hist].[DATE_COMPLETED]                                                                                                    | NULL     | PLAN_ROW |        0 | 1                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+----------------------+----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+--------------+------------+-------------+------------+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+--------------------+

SHOWPLAN_XML:

(Plan XML here)

Have I stumbled upon some kind of bug or am I doing something wrong on my end that I should be doing differently?

(P.S. I know what the warning means and how to fix it, I am more interested in the warning showing up in one place but not another.)

EDIT:
Here is the version information for my SSMS from the "About" help page.

Microsoft SQL Server Management Studio: 11.0.3128.0
Microsoft Analysis Services Client Tools: 11.0.3128.0
Microsoft Data Access Components (MDAC): 6.3.9600.16384
Microsoft MSXML: 3.0 4.0 6.0 
Microsoft Internet Explorer: 9.11.9600.16521
Microsoft .NET Framework: 4.0.30319.34011
Operating System: 6.3.9600

And I am running against a database engine version of 11.0.3128

Answer 1

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