Query Memory Spills

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

When you sometimes look at Execution Plans, you can see that the SELECT operator has sometimes a so-called Memory Grant assigned. This Memory Grant is specified in kilobytes and is needed for the query execution, when some operators (like Sort/Hash operators) in the Execution Plans need memory for execution – the so called Query Memory.

This query memory must be granted by SQL Server before the query is actually executed. The Query Optimizer uses the underlying Statistics to determine how much Query Memory must be acquired for a given query. The problem is now, when the Statistics are out-of-date, and SQL Server underestimates the processed rows. In this case, SQL Server will also request to less Query Memory for the given query. But when the query actually executes, the query can’t resize its granted Query Memory, and can’t just request more. The query must operate within the granted Query Memory. In this case, SQL Server has to spill the Sort/Hash-Operation into TempDb, which means that our very fast in-memory operation becomes a very slow physical On-Disk operation. SQL Server Profiler will report those Query Memory Spills through the events Sort Warnings and Hash Warning.

Unfortunately SQL Server 2008 (R2) provides you no events through Extended Events to track down those Memory Spills. In SQL Server 2012 this will change, and you will have additional events available inside Extended Events for troubleshooting this problem. In this posting I will illustrate you with a simple example how you can reproduce a simple Query Memory Spill because of out-of-date statistics. Let’s create a new database and a simple test table inside it:

The table TestTable contains the primary key on the first column, and the second column is indexed through a Non-Clustered Index. The third column is a CHAR(4000) column which isn’t indexed. We will use that column afterwards for an ORDER BY, so that the Query Optimizer must generate an explicit Sort Operator inside the Execution Plan. In the next step I’m just inserting 1500 records, where we have an even data distribution across all the values in the second column – each value exists once in our table.

With that test data prepared we can now execute a simple query, which must use a separate Sort operator in the Execution Plan:

This query uses the following Execution Plan:

When you look into SQL Server Profiler and you have enabled the above mentioned events, nothing happens. You can also use the DMV sys.dm_io_virtual_file_stats and the columns num_of_writes and num_of_bytes_written to find out if there was some activity in TempDb for a given query. This works – of course only – when you are the only person who currently uses the given SQL Server instance:

Again you will see no activity in TempDb, which means the output from sys.dm_io_virtual_file_stats is the same before and after executing the query. The query takes on my system around 1ms of execution time.

Now we have a table with 1500 records, means that our table needs 20% + 500 rows of data changes so that SQL Server will update the statistics. If you’re doing the math, we need 800 data modifications in that table (500 + 300). So let’s just insert 799 additional rows where the value of the second column is 2. We are just changing the data distribution and SQL Server WILL NOT update the statistics, because one additional data change is still missing, until Update Statistics is triggered automatically inside SQL Server!

When you now execute the same query again, SQL Server will now spill the Sort operation to TempDb, because SQL Server will only request a Query Memory Grant of 1024 kilobytes, which is estimated for just 1 record – the memory grant has the same size as before:

If you check the Estimated Number of Rows in the Execution Plan, they are differing completely from the Actual Number of Rows:

When you track the query execution time you will also see that the execution time increased – in my case it increased up to 200ms, which is a huge difference to the earlier execution time of just 1ms! The DMV sys.dm_io_virtual_file_stats will also report some activity inside TempDb, which is also the evidence that SQL Server spilled the Sort operation into TempDb! SQL Server Profiler will also show you a Sort Warning event.

If you now insert one additional record, and you run the query again, everything is fine, because SQL Server will trigger the Statistics Update and estimate the Query Memory Grant correctly:

So this is a very basic example which shows you how you can reproduce Sort Warnings inside SQL Server – not really a magic. Adam Machanic (http://sqlblog.com/blogs/adam_machanic) has done last week at the SQLPASS Summit in Seattle a whole session about Query Memory at a 500 level, where he went into more details on this complicated topic, especially in combination with Parallel Execution Plans.

Thanks for reading!

-Klaus

SQLpassion

Copyright © 2015 by SQLpassion · Klaus Aschenbrenner · Imprint · Offerings · Academy · Contact · Go to Top