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