Предоставление избыточной памяти для сортировки

Почему этот простой запрос предоставляет столько памяти?

-- Demo table
CREATE TABLE dbo.Test
(
    TID integer IDENTITY NOT NULL,
    FilterMe integer NOT NULL,
    SortMe integer NOT NULL,
    Unused nvarchar(max) NULL,

    CONSTRAINT PK_dbo_Test_TID
    PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
    (FilterMe, SortMe)
SELECT TOP (100 * 1000)
    CHECKSUM(NEWID()) % 1000,
    CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO    
-- Query
SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe;

Для оцененных 50 строк оптимизатор резервирует почти 500 МБ для сортировки:

Расчетный план

39 голосов | спросил Paul White 17 WedEurope/Moscow2014-12-17T13:12:48+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 13:12:48 +0300 2014, 13:12:48

3 ответа


37

Это ошибка в SQL Server (с 2008 по 2014 включительно).

Мой отчет об ошибках здесь .

Условие фильтрации переносится в оператор сканирования как остаточный предикат, но память, предоставленная для сортировки, ошибочно вычисляется на основе оценки оценки фильтра перед фильтрованием .

Чтобы проиллюстрировать эту проблему, мы можем использовать флаг трассировки (недокументированный и неподдерживаемый) 9130, чтобы фильтр не был нажат на кнопку сканирования . Память, предоставленная сортировке, теперь корректно основана на оценочной мощности выхода фильтра, а не на сканировании:

SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T 
WHERE 
    T.FilterMe = 567
ORDER BY 
    T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

Расчетный план

Для производственной системы необходимо предпринять шаги для избегать проблемной формы плана (фильтр вставляется в сканирование с сортировкой в ​​другом столбце). Один из способов сделать это - предоставить индекс условия фильтра и /или предоставить требуемый порядок сортировки.

-- Index on the filter condition only
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe
ON dbo.Test (FilterMe);

При наличии этого индекса желаемое выделение памяти для сортировки только 928 КБ :

С индексом фильтра

Далее, следующий индекс может полностью избежать сортировки ( ноль ):

-- Provides filtering and sort order
-- nvarchar(max) column deliberately not INCLUDEd
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe_SortMe
ON dbo.Test (FilterMe, SortMe);

С индексом фильтрации и сортировки

Протестировано и исправлена ​​ошибка в следующих сборках SQL Server x64 Developer Edition:

 2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

Это было исправлено в SQL Server 2016 с пакетом обновления 1 . Примечания к выпуску включают следующее:

  

Ошибка VSTS номер 8024987
  Сканирование таблиц и сканирование индексов с помощью предиката push down имеют тенденцию переоценивать выделение памяти для родительского оператора.

Проверено и подтверждено исправлено:

  • Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
  • Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

Обе модели CE.

ответил Paul White 17 WedEurope/Moscow2014-12-17T13:12:48+03:00Europe/Moscow12bEurope/MoscowWed, 17 Dec 2014 13:12:48 +0300 2014, 13:12:48
3

Начиная с SQL 2012 вы могли искать большое расхождение между SerialRequiredMemory и SerialDesiredMemory, например, примерно так:

-- Search plan cache for Memory Grant issues
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Collect more info about the plan here if required, eg usecounts, objtype etc, 
SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan
INTO #tmp
FROM sys.dm_exec_cached_plans cp WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(plan_handle)
GO


;WITH cte AS
(
SELECT
    rowId,
    query_plan,
    m.c.value ('@SerialRequiredMemory', 'INT' ) AS SerialRequiredMemory,
    m.c.value ('@SerialDesiredMemory', 'INT' ) AS SerialDesiredMemory

FROM #tmp t
    CROSS APPLY t.query_plan.nodes ( '//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) m(c)
), cte2 AS (
SELECT *,
    CAST( CAST( SerialDesiredMemory AS DECIMAL(10,2) ) / CAST( SerialRequiredMemory AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) Desired_to_Required_ratio
FROM cte
)
SELECT TOP 20
    rowId,
    query_plan,
    SerialRequiredMemory SerialRequiredMemory_KB,
    SerialDesiredMemory SerialDesiredMemory_KB,
    CAST( SerialRequiredMemory / 1024. AS DECIMAL(10,2) ) SerialRequiredMemory_MB,
    CAST( SerialDesiredMemory / 1024. AS DECIMAL(10,2) ) SerialDesiredMemory_MB,
    Desired_to_Required_ratio
FROM cte2
WHERE Desired_to_Required_ratio > 100
ORDER BY Desired_to_Required_ratio DESC

Некоторые дополнительные примечания по этим новым атрибутам здесь . Этот запрос немного грубый и готовый, но он забрал избыточный запрос на сортировку из моего ящика SQL Server 2014 с коэффициентом 975,47 плюс несколько других привлекательных планов. «Нормальное» соотношение (по крайней мере, от моего ограниченного тестирования) составляет ~ 1.

НТН

ответил wBob 18 ThuEurope/Moscow2014-12-18T18:15:37+03:00Europe/Moscow12bEurope/MoscowThu, 18 Dec 2014 18:15:37 +0300 2014, 18:15:37
2

Спасибо за помощь. Я думал, что отправлю обновленную версию вышеуказанного запроса, который мы нашли полезным.

-- Search plan cache for Memory Grant issues
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

-- Collect more info about the plan here if required, eg usecounts, objtype etc, 
SELECT IDENTITY( INT, 1, 1 ) rowId, query_plan, db = DB_NAME(CAST(pa.value AS int))
INTO #tmp
FROM sys.dm_exec_cached_plans cp WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa 
    WHERE pa.attribute = 'dbid' 
GO

;WITH cte AS
(
SELECT
    rowId,
    query_plan,
    m.c.value ('@SerialRequiredMemory', 'INT' ) AS SerialRequiredMemory,
    m.c.value ('@SerialDesiredMemory', 'INT' ) AS SerialDesiredMemory,
    db
FROM #tmp t
    CROSS APPLY t.query_plan.nodes ( '//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]' ) m(c)
), cte2 AS (
SELECT *,
    CAST( CAST( SerialDesiredMemory AS DECIMAL(10,2) ) / CAST( SerialRequiredMemory AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) Desired_to_Required_ratio
FROM cte
)
SELECT TOP 20
    rowId,
    query_plan,
    SerialRequiredMemory SerialRequiredMemory_KB,
    SerialDesiredMemory SerialDesiredMemory_KB,
    CAST( SerialRequiredMemory / 1024. AS DECIMAL(10,2) ) SerialRequiredMemory_MB,
    CAST( SerialDesiredMemory / 1024. AS DECIMAL(10,2) ) SerialDesiredMemory_MB,
    Desired_to_Required_ratio,
    db
FROM cte2
WHERE Desired_to_Required_ratio > 100
ORDER BY Desired_to_Required_ratio DESC
ответил Doug B 15 J0000006Europe/Moscow 2015, 20:02:14

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132