Промежуточная материализация (Query Memory Spills)

Автор: Klaus Aschenbrenner, Query Memory Spills

Иногда, когда вы смотрите на планы выполнения, вы можете увидеть, что у оператора SELECT иногда есть так называемый грант памяти (Memory Grant). Этот грант памяти указывается в килобайтах и необходим для выполнения запроса, когда некоторым операторам (например, Sort/Hash) в планах выполнения требуется память для выполнения — так называемая память запроса (Query Memory).

Эта память запроса должна быть выделена SQL Server до того, как запрос будет фактически выполнен. Оптимизатор запросов использует базовую статистику, чтобы определить, сколько памяти запроса должно быть выделено для данного запроса. Проблема возникает, когда статистика устаревает и SQL Server недооценивает количество обрабатываемых строк. В этом случае SQL Server также запросит слишком мало памяти для данного запроса. Но когда запрос фактически выполняется, он не может изменить размер выделенной памяти и не может просто запросить больше. Запрос должен работать в пределах выделенной памяти. В этом случае SQL Server вынужден сбросить операцию сортировки или хэширования в TempDb, что означает, что наша очень быстрая операция в памяти становится очень медленной физической операцией на диске. SQL Server Profiler будет сообщать о таких сбросах памяти запросов через события Sort Warnings и Hash Warning.

Начиная с SQL Server 2012 в Extended Events появились события для анализа и устранения этой проблемы. В этой статье я покажу на простом примере, как можно воспроизвести сброс памяти запроса из-за устаревшей статистики. Давайте создадим новую базу данных и простую тестовую таблицу:

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Create a new database
CREATE DATABASE InsufficientMemoryGrants
GO

USE 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

Таблица TestTable содержит первичный ключ в первом столбце, а второй столбец индексируется через некластерный индекс. Третий столбец — это CHAR(4000), который не индексируется. Мы будем использовать этот столбец для ORDER BY, чтобы оптимизатор запросов должен был создать явный оператор Sort в плане выполнения. На следующем шаге я вставляю 1500 записей с равномерным распределением данных по значениям во втором столбце — каждое значение существует в таблице единожды.

С подготовленными тестовыми данными мы можем выполнить простой запрос, который должен использовать отдельный оператор Sort в плане выполнения:

DECLARE @x INT

SELECT @x = Col2 FROM TestTable
WHERE Col2 = 2
ORDER BY Col3
GO

Этот запрос использует следующий план выполнения:

Когда вы заглянете в SQL Server Profiler и включите вышеупомянутые события, ничего не произойдёт. Вы также можете использовать DMV sys.dm_io_virtual_file_stats и столбцы num_of_writes и num_of_bytes_written, чтобы узнать, была ли активность в TempDb для данного запроса. Это работает — конечно, только — когда вы единственный человек, использующий данный экземпляр SQL Server:

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

Опять же, вы не увидите активности в TempDb, что означает, что вывод sys.dm_io_virtual_file_stats одинаков до и после выполнения запроса. Запрос на моей системе выполняется около 1 мс.

Теперь у нас есть таблица с 1500 записями. Для автоматического обновления статистики SQL Server требуется, чтобы в таблице произошло 20% + 500 изменений данных. Если посчитать, нам нужно 800 изменений данных в этой таблице (500 + 300). Итак, давайте вставим 799 дополнительных строк, где значение второго столбца равно 2. Мы изменяем распределение данных, и 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

Когда вы теперь снова выполните тот же запрос, SQL Server выполнит сброс операции сортировки в TempDb, потому что SQL Server запросит грант памяти всего 1024 килобайта, что оценивается для 1 записи — размер гранта памяти остался прежним:

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

Если вы проверите «Estimated Number of Rows» в плане выполнения, они будут полностью отличаться от «Actual Number of Rows»:

Когда вы отслеживаете время выполнения запроса, вы также увидите, что оно увеличилось — в моём случае оно увеличилось до 200 мс, что является огромной разницей по сравнению с предыдущим временем выполнения всего в 1 мс! DMV sys.dm_io_virtual_file_stats также покажет некоторую активность в TempDb, что является доказательством того, что SQL Server сбросил операцию сортировки в TempDb! SQL Server Profiler также покажет событие Sort Warning.

Если вы теперь вставите одну дополнительную запись и снова выполните запрос, всё будет в порядке, потому что SQL Server инициирует обновление статистики и правильно оценит грант памяти:

-- Insert 1 record 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

Это очень простой пример, который показывает, как можно воспроизвести Sort Warnings в SQL Server — никакой магии.