Why are queries causing spill to tempdb? 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

 

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.



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