SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Radim Bača

Asked: 2017-03-31 23:17:48 +0800 CST2017-03-31 23:17:48 +0800 CST 2017-03-31 23:17:48 +0800 CST

Dica de cardinalidade do SQL Server

772

Existe uma maneira de 'injetar' uma estimativa de cardinalidade em um otimizador do SQL Server (qualquer versão)?

ou seja, algo semelhante à dica de cardinalidade do Oracle.

Minha motivação é impulsionada pelo artigo Quão bons são os otimizadores de consulta, realmente? ^[1] , onde testam a influência do estimador de cardinalidade na seleção de um plano ruim. Portanto, seria suficiente se eu pudesse forçar o SQL Server a 'estimar' as cardinalidades precisamente para consultas complexas.

^[1] Leis, Victor, et al. "Quão bons são os otimizadores de consulta, realmente?"
Anais do Fundo VLDB 9.3 (2015): 204-215.

3 respostas

Voted

Joe Obbish · Answer 1 · 2017-04-01T06:00:51+08:00

Você pode obter algo semelhante à CARDINALITYdica da Oracle usando estrategicamente TOPe uma função definida pelo usuário chamada MANY() desenvolvida por Adam Machanic . Vamos trabalhar com alguns exemplos. Estou usando o banco de dados AdventureWorks disponível gratuitamente. Suponha que eu realmente precise controlar o número de linhas retornadas pela thtabela derivada na seguinte consulta:

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
) th ON p.ProductID = th.ProductID;

Como está, recebo uma estimativa de 113443 linhas:

Se eu precisar diminuir a estimativa th, posso usar TOPjunto com a OPTIMIZE FORdica de consulta para definir uma meta de linha. Aqui está uma maneira de fazer isso:

DECLARE @row_goal BIGINT = 9223372036854775807;
SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1));

Podemos ver que a estimativa é apenas 1 linha:

Eu defini @row_goalpara o maior BIGINTvalor possível para evitar alterar os resultados. A OPTIMIZE FORdica de consulta instrui o otimizador a otimizar a consulta como se @row_goalfosse igual a 1. Obterei os mesmos resultados, mas a consulta será otimizada de maneira diferente.

Aumentar uma estimativa de cardinalidade é mais complicado. Não podemos simplesmente aumentar o valor de TOPporque o otimizador perceberá que não retornará linhas suficientes. No entanto, podemos usar a MANY()função para adicionar linhas à estimativa. Observe que a MANY()função sempre retornará 0 linhas, mas a estimativa de linha dela muda com o parâmetro de entrada. Suponha que você precise aumentar a estimativa de linha da tabela derivada em 10 vezes. Uma maneira de conseguir isso:

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (9223372036854775807) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
    LEFT OUTER JOIN dbo.Many(10) AS m ON 1=1
) th ON p.ProductID = th.ProductID;

Podemos ver que a estimativa é 10X a tabela base:

O supérfluo TOPfoi adicionado para evitar que o otimizador mova as tabelas. Sem ela, a MANY()função pode ser aplicada no lugar errado do plano.

É possível combinar as duas técnicas se você quiser uma superestimativa precisa em vez de apenas multiplicar o número de linhas por um fator. Por exemplo, vamos supor que você realmente precise que a estimativa da tabela derivada seja exatamente 1.000.000 linhas. Uma maneira de conseguir isso:

DECLARE @row_goal BIGINT = 9223372036854775807;

SELECT 
    p.Name
    , th.ProductId
    , th.Quantity
    , th.ActualCost
FROM Production.Product p
INNER JOIN (
    SELECT TOP (@row_goal) ProductId, Quantity, ActualCost
    FROM Production.TransactionHistory 
    LEFT OUTER JOIN dbo.Many(10) AS m ON
        1=1
) th ON p.ProductID = th.ProductID
OPTION (OPTIMIZE FOR (@row_goal = 1000000));

Podemos ver que a estimativa é de 1.000.000 linhas:

Preciso avisá-lo de que essas são técnicas avançadas que geralmente não são necessárias para a otimização de consultas. Se você quiser saber mais eu recomendo assistir Clash of the Row Goals apresentado por Adam Machanic.

função dbo.Many

-- By Adam Machanic, reproduced with permission
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Many' AND OBJECT_SCHEMA_NAME(object_id) = 'dbo')
    DROP FUNCTION dbo.Many
GO
CREATE FUNCTION dbo.Many(@n INT)
RETURNS TABLE AS
RETURN
(
    WITH
    a(x) AS
    (
        SELECT
            *
        FROM
        (
            VALUES
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
                (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
        ) AS x0(x)
    )
    SELECT TOP(@n)
        1 AS x
    FROM
        a AS a1,
        a AS a2
    WHERE
        a1.x % 2 = 0
)
GO

Tom V · Answer 2 · 2017-04-01T03:16:02+08:00

Não há como injetar uma estimativa de cardinalidade diretamente no otimizador, mas dependendo do que você deseja alcançar, existem algumas opções.

Você pode usar uma OPTION (FAST N)dica de consulta para introduzir metas de linha e possivelmente reescrever sua consulta usando CTEs ou subconsultas para injetar TOP...ORDER BYmetas de linha baseadas em diferentes partes do seu plano de execução, mas não tenho certeza de quão eficiente será sua consulta resultante quando você iniciar brincando com as construções mais complexas.

Consulte Por Dentro do Otimizador: Objetivos de Linha em Profundidade para obter uma explicação mais completa.

Se você quiser influenciar os operadores escolhidos pelo otimizador, não precisará tentar injetar estimativas de cardinalidade, mas poderá usar coisas como OPTION (MERGE JOIN)ou OPTION (HASH JOIN), por exemplo, para forçar operadores de junção física.

Este artigo entra em mais detalhes sobre como influenciar um plano usando dicas: Controlando planos de execução com dicas

Se você deseja corrigir um plano, também pode usar um guia de plano.

Novamente, não está claro qual é o seu caso de uso real, e sua milhagem pode variar usando essas técnicas. Em muitos casos, é melhor deixar o otimizador decidir e certificar-se de que você tenha estatísticas atualizadas para que o otimizador possa tomar uma decisão informada.

Sugestão relevante do Microsoft Connect: permitir especificar a dica de seletividade de filtro em consultas por xor88. A Microsoft respondeu:

Obrigado pelo feedback. Eu posso ver o benefício potencial disso. Em geral, nos esforçamos para tornar nosso comportamento automático o melhor possível e evitar a necessidade desse tipo de dica, mas é claro que temos muitas outras dicas. Consideraremos isso para uma versão futura, mas seria além da versão Denali (11.0).

Atenciosamente,
Eric Hanson
Program Manager
SQL Server Query Processing

Dan Guzman · Answer 3 · 2017-04-01T03:36:35+08:00

Você pode usar a dica de consulta do SQL Server OPTIMIZE FORpara forçar a estimativa de cardinalidade com base nos valores sugeridos em vez de usar o valor real (parâmetros) ou o valor desconhecido (variáveis) durante a compilação. Consulte o tópico de dicas de consulta na documentação do SQL Server para obter detalhes completos.

Por exemplo, a consulta abaixo estimará as contagens de linhas com base no histograma de estatísticas dos valores sugeridos, em vez da cardinalidade média geral, como faria com variáveis locais.

DECLARE 
      @StartDate datetime = '20150101'
    , @EndDate datetime = '20150102';
SELECT *
FROM dbo.Example
WHERE
    DateColumn BETWEEN  @StartDate AND @EndDate
OPTION(OPTIMIZE FOR(@StartDate = '20100101', @EndDate='20100101'));

Da mesma forma, a dica pode ser usada para parâmetros para que as estimativas sejam baseadas no histograma de estatísticas dos valores sugeridos em vez dos valores reais dos parâmetros durante a compilação.

DECLARE 
      @StartDate datetime = '20150101'
    , @EndDate datetime = '20150102';
EXECUTE sp_executesql N'SELECT *
        FROM dbo.Example
        WHERE
            DateColumn BETWEEN  @StartDate AND @EndDate
        OPTION(OPTIMIZE FOR(@StartDate = ''20100101'', @EndDate=''20100101''));'
    , N'@StartDate datetime, @EndDate datetime'
    , @StartDate = @StartDate
    , @EndDate = @EndDate;

A UNKNOWNpalavra-chave pode ser especificada em vez de um literal na dica para usar a cardinalidade média geral em vez de estimar com base no valor real do parâmetro e no histograma de estatísticas.

Dica de cardinalidade do SQL Server

função dbo.Many

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Dica de cardinalidade do SQL Server

3 respostas

função dbo.Many

relate perguntas