27

Background

I am in the process of migrating a 160gb database from MSSQL 2008 (standard) on a Win 2008 server with 48gb RAM to a new server running MSSQL 2012 (64-bit web edition) on Win 2012 with 64gb of RAM. The old server is live and under load; the new server is not in production. The new server has 8 tempdb files (4GB each).

Problem

In testing on the new server I am seeing steps in numerous queries cause alerts mentioning "operator used tempdb to spill data during execution". I have been able to avoid sorts by rewriting some of the queries, but this is not really addressing the issue. The same queries on the old server do not cause spills. I have read that spills happen when MSSQL can't complete an operation in memory and has to spill/page into tempdb. Should I be concerned about spills?

Examples

enter image description here

I have run sp_updatestats on the database, so statistics should be up to date, but you will note there are some discrepancies between the estimated and actual numbers of rows.

Memory concern

I have set a max memory setting for MSSQL of 58 of the 64gb. Currently MSSQL has consumed around 35gb of this memory, but has a working set of only 682mb. The old server (albeit in production, handling load) has 44gb of memory committed to MSSQL of which 43.5gb is in its working set.

enter image description here

I do not know if the spills might be related to a memory setting - anyone have any ideas? MSSQL currently has acres of RAM to spare, so why is it spilling into tempdb for some sorts and hash matches?

Improve this question
4
  • 7
    The alert in the execution plan is new in 2012. Have you checked it wasn't spilling all along on the old server? Were you monitoring for this?
    – Martin Smith
    Jan 30, 2014 at 16:00
  • @MartinSmith ah, didn't realise the alert was new. I was not monitoring for spills on the old server. Will investigate that.
    – Andy W
    Jan 30, 2014 at 16:02
  • 1
    Slightly tangent point but I'm sitting in front of a 10 table join that by default mostly uses merge joins with very good row estimates that causes tempdb level 0 spills and runtime 25s. Forcing hash joins (and order) removes the spills and runs in 9s. I'm left wondering whether its the merge vs hash or the spill causing difference and whether the optimizer is properly weighting the effect of a seemingly known upcoming spill (because row estimates are very good).
    – crokusek
    May 23, 2014 at 17:50
  • The new server has hardware numa?
    – stacylaray
    May 30, 2014 at 1:48

1 Answer 1

Reset to default
28

There's several different questions in here:

Q: Why weren't the queries spilling before?

They were, but SQL Server Management Studio didn't surface this as a clear error prior to SQL 2012. It's a great example of why when you're doing performance tuning, you have to go deeper than the graphical execution plan.

Q: Why do queries spill to disk?

Because SQL Server didn't grant them enough memory to complete their operations. Perhaps the execution plan underestimated the amount of memory required, or perhaps the box is under memory pressure, or they're just big queries. (Remember, SQL Server uses memory for three things - caching raw data pages, caching execution plans, and workspace for queries. That workspace memory ends up being fairly small.)

Q: How can I reduce spills?

By writing sargable T-SQL statements, having up-to-date statistics, putting enough memory in the server, building the right indexes, and interpreting the execution plans when things don't work out the way you expected. Check out Grant Fritchey's book SQL Server Query Performance Tuning for detailed explanations of all of those.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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