Concessão excessiva de memória de classificação

Este é um bug no SQL Server (de 2008 a 2014 inclusive).

Meu relatório de bug está aqui .

A condição de filtragem é enviada para o operador de varredura como um predicado residual, mas a memória concedida para a classificação é calculada erroneamente com base na estimativa de cardinalidade pré-filtro .

Para ilustrar o problema, podemos usar o sinalizador de rastreamento 9130 (não documentado e sem suporte) para impedir que o filtro seja enviado para baixo no operador de digitalização. A memória concedida para a classificação agora é baseada corretamente na cardinalidade estimada da saída do filtro, não na varredura:

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!

Para um sistema de produção , será necessário tomar medidas para evitar a forma de plano problemática (um filtro inserido em uma varredura com uma classificação em outra coluna). Uma maneira de fazer isso é fornecer um índice sobre a condição do filtro e/ou fornecer a ordem de classificação necessária.

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

Com esse índice instalado, a concessão de memória desejada para a classificação é de apenas 928 KB :

Indo além, o seguinte índice pode evitar completamente a classificação ( zero concessão de memória):

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

Testado e com bug confirmado nas seguintes compilações do 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)

Isso foi corrigido no SQL Server 2016 Service Pack 1 . As notas de versão incluem o seguinte:

VSTS bug número 8024987 As
varreduras de tabela e as varreduras de índice com predicado push down tendem a superestimar a concessão de memória para o operador pai.

Testado e confirmado corrigido em:

• 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

Ambos os modelos CE.

Do SQL 2012 em diante, você pode procurar uma grande discrepância entre SerialRequiredMemorye SerialDesiredMemory, por exemplo, algo assim:

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

Algumas notas adicionais sobre esses novos atributos aqui . Esta consulta é um pouco grosseira e pronta, mas pegou a consulta de classificação excessiva da minha caixa de desenvolvimento do SQL Server 2014 com uma proporção de 975,47, além de alguns outros planos impressionantes. A proporção 'normal' (pelo menos em meus testes limitados) parece ser ~ 1.

HTH

Obrigado por toda a ajuda. Pensei em enviar uma versão atualizada da consulta acima que consideramos útil.

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