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"
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
Best Answer
This:
Is equivalent to pressing
Display Estimated Execution Planon the toolbar (or hitting Ctrl + L). You'll notice that no rows are returned from the query, like there is when you useInclude 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.