2

I have a T-SQL query (SQL Server 2012) that does the job, but when I look at the execution plan, I see a sort operator with a warning: "Operator used tempdb to spill data during execution with spill level 1."

The reading I have done so far suggests that if I use an "order by" clause I can eliminate this sort iterator. This isn't an option for me, because I can't do a subquery sort and sorting the outermost query not remove the sort iterator.

I added non-clustered indices where previous execution plans suggested I should.

Is there anything else I could to do address this "tempdb spill" warning? I have no more ideas at this point.

Thank you for any ideas.

enter image description here

USE MIA_2014_15_v1;
GO

/*
    Notes:
    * The outer query exists so that I can filter by a windowed function (Date_Count).   
*/

SELECT q.Campus,
    q.Student_ID,
    q.Student_Name,
    q.DATEIN,
    q.TIMEIN,
    q.[TIMEOUT],
    q.Date_Count
FROM (
    SELECT TC_Hours.Campus,
        TC_Hours.[Student ID] AS Student_ID,
        Students.Student_Name,
        TC_Hours.[Date] AS DATEIN,
        TC_Hours.[Time In] AS TIMEIN,
        TC_Hours.[Time Out] AS TIMEOUT,
        count(TC_Hours.[Date]) OVER (
            PARTITION BY TC_Hours.Campus,
            TC_Hours.[Student ID],
            TC_Hours.[Date]
            ) AS Date_Count
    FROM dbo.TC_Hours_District TC_Hours
    LEFT JOIN dbo.Base__Student_Name_by_FY Students ON TC_Hours.Campus = Students.Campus
        AND TC_Hours.[Student ID] = Students.Student_ID
    WHERE (NOT students.Student_Name IS NULL)
        AND Students.FY = 'FY15'                
    ) q
WHERE q.Date_Count > 1;
GO
  • If its an option you could try and increase the memory available for the SQL Server you are running the code against. Apart from that I would look into the SQL covering the OVER (PARTITION BY TC_Hou... etc) to see if that can be split up better. – kevchadders Jan 30 '15 at 16:50
  • The available memory is out of my control, so the second option looks like my target. –  Jan 30 '15 at 16:57

2 Answers2

5

Actual number of rows is greater then estimated one. SQL Server grants memory before execution, looking on estimated values. At run time it gets more rows then expected so sort spills in temp db. All you can do in this situation is to make sure that estimated values are correct. Try to update statistics on involved tables. Remove predicates one-by-one to find one which leads to wrong estimates. You can try to create new statistic from there.

Such spills are not an unusual things. I wouldn't bother much if it is not some super-important query.

Alsin
  • 1,514
  • 1
  • 14
  • 18
  • I never considered updating statistics. I used "exec sp_updatestats;" Unfortunately, that didn't do the trick. I suppose I can ignore the warning but I figure it's there for a reason. –  Jan 30 '15 at 20:30
0

Spills to TempDB are essentially spills to disk. You could use the sp_updatestats as you mentioned but it probably does more work than you need per the info in this post.

http://sqlperformance.com/2013/07/sql-statistics/statistics-updates

If statistics are updated but with sampling then SQL Server may not be able to correctly determine the data distribution and the histogram will not be accurate. Try running update statistics and use the WITH FULLSCAN option.

https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/

Lee M
  • 101
  • 1