Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"

enter image description here
(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:
enter image description here
(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

share|improve this question
    
Both management studio and analysis tools are both 11.0.3128.0 which matches my version number of SQL server exactly, I have included all of the version numbers from my About... window in to the question. –  Scott Chamberlain Mar 20 at 16:06
    
I'm having a hard time here - what exactly is different about the first execution (where the warning showed up) and the second execution (where it did not)? Is it just the difference in using the toolbar button vs. SET commands? It's possible, too, that memory pressure, data not being in cache, and other variables required a spill on the first execution but not subsequent once. Is this a busy box or are you alone? What happens if you run DBCC DROPCLEANBUFFERS; first? –  Aaron Bertrand Mar 20 at 16:32
    
@AaronBertrand It only shows up using the toolbar button, and it shows up every time consistently. The only difference is using the toolbar button or the set commands. Doing DBCC DROPCLEANBUFFERS before each run did not change the behaviors. One thing I just did notice, clicking "Show Estimated Execution Plan" looks identical to the SHOWPLAN_XML plan, also when SHOWPLAN is on I don't get result sets when I click Execute. Is SHOWPLAN more equivalent to clicking the "estimated" button instead of clicking the "actual" button and running the query? –  Scott Chamberlain Mar 20 at 17:48
    
@AaronBertrand I get just the plan information, I get no results when I have any of the SHOWPLAN items set to ON. –  Scott Chamberlain Mar 20 at 18:55
    
That's what I suspected (and just confirmed myself as well). –  Aaron Bertrand Mar 20 at 19:01

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.