Início / dba / Perguntas / 117016
Accepted
Henrik Staun Poulsen
Asked: 2015-10-06 03:43:37 +0800 CST

Procure e você deve digitalizar... em tabelas particionadas

  • 772

Eu li estes artigos no PCMag por Itzik Ben-Gan :

Procure e você fará a varredura Parte I: Quando o otimizador não otimiza
Procure e você fará a varredura Parte II: Chaves Ascendentes

No momento, estou tendo um problema de "Grouped Max" com todas as nossas tabelas particionadas. Usamos o truque fornecido por Itzik Ben-Gan para obter um max(ID), mas às vezes ele simplesmente não funciona:

DECLARE @MaxIDPartitionTable BIGINT
SELECT  @MaxIDPartitionTable = ISNULL(MAX(IDPartitionedTable), 0)
FROM    ( SELECT    *
          FROM      ( SELECT    partition_number PartitionNumber
                      FROM      sys.partitions
                      WHERE     object_id = OBJECT_ID('fct.MyTable')
                                AND index_id = 1
                    ) T1
                    CROSS APPLY ( SELECT    ISNULL(MAX(UpdatedID), 0) AS IDPartitionedTable
                                  FROM      fct.MyTable s
                                  WHERE     $PARTITION.PF_MyTable(s.PCTimeStamp) = PartitionNumber
                                            AND UpdatedID <= @IDColumnThresholdValue
                                ) AS o
        ) AS T2;
SELECT @MaxIDPartitionTable

eu pego esse plano

insira a descrição da imagem aqui

Mas depois de 45 minutos, olhe para as leituras

reads          writes   physical_reads
12,949,127        2       12,992,610

de onde eu saio sp_whoisactive.

Normalmente é executado muito rapidamente, mas não hoje.

Editar: estrutura da tabela com partições:

CREATE PARTITION FUNCTION [MonthlySmallDateTime](SmallDateTime) AS RANGE RIGHT FOR VALUES (N'2000-01-01T00:00:00.000', N'2000-02-01T00:00:00.000' /* and many more */)
go
CREATE PARTITION SCHEME PS_FctContractualAvailability AS PARTITION [MonthlySmallDateTime] TO ([Standard], [Standard])
GO
CREATE TABLE fct.MyTable(
    MyTableID BIGINT IDENTITY(1,1),
    [DT1TurbineID] INT NOT NULL,
    [PCTimeStamp] SMALLDATETIME NOT NULL,
    Filler CHAR(100) NOT NULL DEFAULT 'N/A',
    UpdatedID BIGINT NULL,
    UpdatedDate DATETIME NULL
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [DT1TurbineID] ASC,
    [PCTimeStamp] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
) ON [PS_FctContractualAvailability]([PCTimeStamp])

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_UpdatedID_PCTimeStamp] ON [fct].MyTable
(
    [UpdatedID] ASC,
    [PCTimeStamp] ASC
)
INCLUDE (   [UpdatedDate]) 
WHERE ([UpdatedID] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [PS_FctContractualAvailability]([PCTimeStamp])
GO
sql-server performance

1 respostas

  1. Best Answer

    A questão básica é que o Index Seek não é seguido por um operador Top. Esta é uma otimização que geralmente é introduzida quando a busca retorna linhas na ordem correta para uma MIN\MAXagregação.

    Essa otimização explora o fato de que a linha min/max é a primeira na ordem crescente ou decrescente. Também pode ser que o otimizador não possa aplicar essa otimização a tabelas particionadas; Eu esqueço.

    De qualquer forma, o ponto é que, sem essa transformação, o plano de execução acaba processando todas as linhas que se qualificam S.UpdatedID <= @IDColumnThresholdValuepor partição, em vez da desejada linha por partição.

    Você não forneceu definições de tabela, índice ou particionamento na pergunta, então não posso ser muito mais específico. Você deve verificar se seu índice suportaria tal transformação. De forma mais ou menos equivalente, você também pode expressar o MAXcomo um TOP (1) ... ORDER BY UpdatedID DESC.

    Se isso resultar em uma classificação (incluindo uma classificação TopN ), você sabe que seu índice não é útil. Por exemplo:

    SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.IDPartitionedTable), 0)
FROM    
( 
    SELECT
        O.IDPartitionedTable
    FROM      
    ( 
        SELECT
            P.partition_number AS PartitionNumber
        FROM sys.partitions AS P
        WHERE 
            P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
            AND P.index_id = 1
    ) AS T1
    CROSS APPLY 
    (    
        SELECT TOP (1) 
            S.UpdatedID AS IDPartitionedTable
        FROM fct.MyTable AS S
        WHERE
            $PARTITION.PF_MyTable(S.PCTimeStamp) = T1.PartitionNumber
            AND S.UpdatedID <= @IDColumnThresholdValue
        ORDER BY
            S.UpdatedID DESC
    ) AS O
) AS T2;

    A forma do plano que isso deve produzir é:

    Forma de plano desejada

    Observe o Top abaixo do Index Seek. Isso limita o processamento a uma linha por partição.

    Ou, usando uma tabela temporária para conter números de partição:

    CREATE TABLE #Partitions
(
    partition_number integer PRIMARY KEY CLUSTERED
);

INSERT #Partitions
    (partition_number)
SELECT
    P.partition_number AS PartitionNumber
FROM sys.partitions AS P
WHERE 
    P.[object_id] = OBJECT_ID(N'fct.MyTable', N'U')
    AND P.index_id = 1;

SELECT
    @MaxIDPartitionTable = ISNULL(MAX(T2.UpdatedID), 0)
FROM #Partitions AS P
CROSS APPLY 
(
    SELECT TOP (1) 
        S.UpdatedID
    FROM fct.MyTable AS S
    WHERE
        $PARTITION.PF_MyTable(S.PCTimeStamp) = P.partition_number
        AND S.UpdatedID <= @IDColumnThresholdValue
    ORDER BY
        S.UpdatedID DESC
) AS T2;

DROP TABLE #Partitions;

    Observação: acessar uma tabela do sistema em sua consulta evita o paralelismo. Se isso for importante, considere materializar os números de partição em uma tabela temporária e APPLYa partir dela. O paralelismo normalmente não é útil neste padrão (com indexação correta), mas seria negligente da minha parte não mencioná-lo.

    Observação lateral 2: há um item Connect ativo solicitando suporte integrado para MIN\MAXagregados e Top em objetos particionados.

    • 28

relate perguntas