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:
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
-- Create a new
database
CREATE DATABASE InsufficientMemoryGrants
GO
SUE InsufficientMemoryGrants
GO
-- Create a test
table
CREATE TABLE TestTable
(
Col1 INT IDENTITY PRIMARY KEY,
Col2 INT,
Col3 CHAR(4000)
)
GO
-- Create a Non-Clustered
Index on column Col2
CREATE NONCLUSTERED INDEX idxTable1_Column2 ON TestTable(Col2)
GO
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:
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER BY Col3
GO
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:
-- Check the activity
in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- Select a record
through the previous created Non-Clustered Index from the table.
-- SQL Server retrieves
the record through a Non-Clustered Index Seek operator.
-- SQL Server estimates
for the sort operator 1 record, which also reflects
-- the actual number
of rows.
-- SQL Server requests
a memory grant of 1024kb - the sorting is done inside
-- the memory.
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER BY Col3
GO
-- Check the activity
in TempDb after the execution of the sort operation.
-- There was no
activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
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!
-- Insert 799 records
into table TestTable
SELECT TOP 799 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO TestTable (Col2, Col3)
SELECT 2, REPLICATE('x', 4000) FROM #nums
DROP TABLE #nums
GO
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:
-- Check the activity
in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server estimates
now 1 record for the sort operation and requests a memory grant of 1.024kb for the
query.
-- This is too less,
because actually we are sorting 800 rows!
-- SQL Server has
to spill the sort operation into TempDb, which now becomes a physical I/O operation!!!
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER BY Col3
GO
-- Check the activity
in TempDb after the execution of the sort operation.
-- There is now
activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
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:
-- Insert 1 records
into table TestTable
SELECT TOP 1 IDENTITY(INT, 1, 1) AS n INTO #Nums
FROM
master.dbo.syscolumns sc1
INSERT INTO TestTable (Col2, Col3)
SELECT 2, REPLICATE('x', 2000) FROM #nums
DROP TABLE #nums
GO
-- Check the activity
in TempDb before we execute the sort operation.
SELECT num_of_writes, num_of_bytes_written FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
-- SQL Server has
now accurate statistics and estimates 801 rows for the sort operator.
-- SQL Server requests
a memory grant of 6.656kb, which is now enough.
-- SQL Server now
spills the sort operation not to TempDb.
-- Logical reads:
577
DECLARE @x INT
SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER BY Col3
GO
-- Check the activity
in TempDb after the execution of the sort operation.
-- There is now
no activity in TempDb during the previous SELECT statement.
SELECT num_of_writes, num_of_bytes_written FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), 1)
GO
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
