Início / dba / Perguntas / 172434
Accepted
Joe Obbish
Asked: 2017-05-02 07:24:43 +0800 CST

Por que a criação de CCI em uma tabela vazia solicita uma enorme concessão de memória?

  • 772

Em um servidor de controle de qualidade com memória muito baixa, tive o erro 8545 ao criar índices columnstore clusterizados em novas tabelas no SQL Server 2016:

Erro 8645: Ocorreu um tempo limite ao aguardar recursos de memória para executar a consulta. Execute novamente a consulta.

Posso reproduzir facilmente uma grande concessão de memória solicitada em minha máquina local. Para o seguinte código:

DROP TABLE IF EXISTS dbo.MY_FIRST_FACT_TABLE;

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX MY_FIRST_CCI ON dbo.MY_FIRST_FACT_TABLE;

Recebo uma solicitação de memória de cerca de 512 MB de memória. Há um aviso para a concessão de memória excessiva no plano de execução real:

concessão de memória

Mesmo que a consulta use 0 KB de memória, ela ainda pode expirar dependendo de outra atividade no servidor. Por que o SQL Server solicita tanta memória? O que posso fazer sobre isso?

sql-server sql-server-2016

1 respostas

  1. Best Answer

    Há uma dica sobre isso na documentação do desempenho da consulta CCI :

    Planeje memória suficiente para criar índices columnstore em paralelo

    A criação de um índice columnstore é, por padrão, uma operação paralela, a menos que a memória seja restrita. Criar o índice em paralelo requer mais memória do que criar o índice em série. Quando há memória suficiente, a criação de um índice columnstore leva 1,5 vezes mais do que a criação de uma árvore B nas mesmas colunas.

    A memória necessária para criar um índice columnstore depende do número de colunas, do número de colunas de string, do grau de paralelismo (DOP) e das características dos dados. Por exemplo, se sua tabela tiver menos de um milhão de linhas, o SQL Server usará apenas um thread para criar o índice columnstore.

    Essa citação sugere que o número de linhas na tabela é importante para o DOP, mas não para a concessão de memória. A memória necessária também depende do número de colunas, então vamos testar com uma tabela com menos colunas para ver mais facilmente a diferença esperada na concessão de memória. Estou usando esta tabela e definição de índice:

    DROP TABLE IF EXISTS dbo.MY_SECOND_FACT_TABLE;

CREATE TABLE dbo.MY_SECOND_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL
);

INSERT INTO dbo.MY_SECOND_FACT_TABLE WITH (TABLOCK) (ID, COL1)
SELECT TOP (0) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE CLUSTERED COLUMNSTORE INDEX MY_SECOND_CCI ON dbo.MY_SECOND_FACT_TABLE WITH (MAXDOP = 1);

    Abaixo estão os resultados de alguns testes:

    ╔══════════════════╦════════════════╦═════════════════╗
║ REQUESTED_MAXDOP ║ NUMBER_OF_ROWS ║ MEMORY_GRANT_KB ║
╠══════════════════╬════════════════╬═════════════════╣
║                1 ║              0 ║           89928 ║
║                2 ║              0 ║           89928 ║
║                1 ║        2000000 ║           89928 ║
║                2 ║        2000000 ║          179896 ║
║                1 ║        5000000 ║           89928 ║
║                2 ║        5000000 ║          179896 ║
╚══════════════════╩════════════════╩═════════════════╝

    Com 0 linhas, o DOP é rebaixado para 1, mas, caso contrário, o número de linhas não parece importar para a concessão de memória. Isso é, sem dúvida, uma simplificação grosseira, mas talvez o SQL Server construa o CCI um rowgroup por vez por DOP e mantenha os dicionários de string à medida que avança. Isso soa bem na prática e significa que a concessão de memória não será dimensionada com o número de linhas na tabela, mas sem uma verificação de um número muito baixo de linhas, o SQL Server poderá solicitar uma concessão de memória excessiva. A concessão de memória excessiva não importa na maioria dos casos porque CREATE INDEXé muito rápida, mas pode ser importante se o SQL Server aguardar a concessão de memória.

    Então agora eu tenho uma idéia de por que a concessão de memória é tão grande, mas o que pode ser feito sobre isso? O SQL Server 2014 introduziu a sintaxe para definir uma definição de índice como parte da definição de tabela. É possível escrever uma única instrução que cria a tabela e o índice :

    INDEX index_name CLUSTERED COLUMNSTORE

    Aplica-se a: SQL Server 2014 a SQL Server 2016 e Banco de Dados SQL do Azure.

    Especifica para armazenar a tabela inteira em formato colunar com um índice columnstore clusterizado. Isso sempre inclui todas as colunas na tabela. Os dados não são classificados em ordem alfabética ou numérica, pois as linhas são organizadas para obter benefícios de compactação de armazenamento de colunas.

    Para a tabela original, a sintaxe ficaria assim:

    CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL,
    INDEX MY_FIRST_CCI CLUSTERED COLUMNSTORE
);

    Uma CREATE TABLEinstrução não gera um plano real, então não posso dizer imediatamente se isso reduziu a concessão de memória. Tentei criar a tabela 1000 vezes enquanto martelava o sys.dm_exec_query_memory_grantsDMV, mas nunca obtive nenhum resultado. Isso sugere que a memória não foi concedida, mas não é prova. Eu também tentei eventos estendidos com query_memory_grant_usage e não obtive nenhum resultado para a CREATE TABLEinstrução única.

    Obviamente, a melhor maneira de testar é criar o CCI nas condições de fluxo de trabalho originais com memória disponível limitada. Eu fiz isso e não experimentei nenhum tempo limite de memória.

    Suponho que se possa dizer que definir o CCI na CREATE TABLEdeclaração para uma nova tabela é uma prática recomendada. No entanto, se sua carga de trabalho causar esse erro, alterar o CREATE TABLEscript provavelmente não será suficiente para resolver outros problemas que você encontrará, como problemas de memória ao iniciar o carregamento de dados nos CCIs.

    • 3

relate perguntas