Início / dba / Perguntas / 208603
Accepted
Steffen Mangold
Asked: 2018-06-03 16:10:46 +0800 CST

Eliminação de partição sob encomenda por

  • 772

Eu tenho uma tabela particionada e problemas com eliminação de partição.

Configurar

Minha tabela é bastante simples e se parece com isso:

CREATE TABLE [data].[OperationData](
    [DataLoggerID] [bigint] NOT NULL,
    [End] [datetime2](7) NOT NULL
CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED 
(
    [DataLoggerID] ASC,
    [End] ASC
))

A tabela é particionada na [End]coluna por data.

Problema

Eu executo uma consulta simples para encontrar a linha mais recente por [End]coluna.

SELECT TOP(1)
    [OperationData].[DataLoggerID],
    [OperationData].[End]
FROM [data].[OperationData]
WHERE [OperationData].[DataLoggerID] = 30217  
ORDER BY [OperationData].[End] DESC

O problema é que o plano de execução mostra que todas as partições foram pesquisadas e nenhuma eliminação de partição ocorreu.

Estatísticas

Pergunta

A eliminação de partição é possível nesta consulta? E quando sim porque não é usado?

Um pouco mais...

Não consigo colar o plano em "Colar o plano" (aparece um erro), então copio-o aqui.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.520" Build="15.0.400.390" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="26.2112" StatementText="SELECT TOP(1)&#xD;&#xA;    [OperationData].[DataLoggerID],&#xD;&#xA;   [OperationData].[End]&#xD;&#xA;FROM [data].[OperationData] AS [OperationData]  &#xD;&#xA;WHERE [OperationData].[DataLoggerID] = 30217  &#xD;&#xA;ORDER BY [OperationData].[End] DESC" StatementType="SELECT" QueryHash="0xCA9C86DFB3F9CA37" QueryPlanHash="0xB953A3686656A27D" RetrievedFromCache="true" StatementSqlHandle="0x090060DDDE8D2D2A050D6AF14FDF6902EDD10000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="7" ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="false">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1024" CachedPlanSize="280" CompileTime="6783" CompileCPU="52" CompileMemory="7848">
            <MissingIndexes>
              <MissingIndexGroup Impact="95.7193">
                <MissingIndex Database="[powerdoo]" Schema="[data]" Table="[OperationData]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[DataLoggerID]" ColumnId="1" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <Warnings>
              <MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="16" DesiredMemory="24" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="3104936" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="5872010" EstimatedPagesCached="1101001" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="9384056" />
            <WaitStats>
              <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="805" WaitCount="19" />
              <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="15" WaitCount="8" />
            </WaitStats>
            <QueryTimeStats CpuTime="76" ElapsedTime="846" />
            <RelOp AvgRowSize="23" EstimateCPU="1.00318" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="26.2112">
              <OutputList>
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
              </OutputList>
              <MemoryFractions Input="1" Output="1" />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="845" ActualCPUms="76" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="1024" UsedMemoryGrant="16" />
              </RunTimeInformation>
              <TopSort Distinct="false" Rows="1">
                <OrderBy>
                  <OrderByColumn Ascending="false">
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OrderByColumn>
                </OrderBy>
                <RelOp AvgRowSize="23" EstimateCPU="1.22175" EstimateIO="23.975" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15680.7" EstimatedRowsRead="15680.7" LogicalOp="Clustered Index Seek" NodeId="1" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="25.1968" TableCardinality="81308300">
                  <OutputList>
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                    <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="49050" ActualRowsRead="49050" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="730" ActualCPUms="64" ActualScans="7672" ActualLogicalReads="2533" ActualPhysicalReads="0" ActualReadAheads="65" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <RunTimePartitionSummary>
                    <PartitionsAccessed PartitionCount="7672">
                      <PartitionRange Start="1" End="7672" />
                    </PartitionsAccessed>
                  </RunTimePartitionSummary>
                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="End" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Index="[PK_OperationData]" Alias="[OperationData]" IndexKind="Clustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(1)">
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                          <EndRange ScanType="LE">
                            <RangeColumns>
                              <ColumnReference Column="PtnId1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(7672)">
                                <Const ConstValue="(7672)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </EndRange>
                        </SeekKeys>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Database="[powerdoo]" Schema="[data]" Table="[OperationData]" Alias="[OperationData]" Column="DataLoggerID" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(30217)">
                                <Const ConstValue="(30217)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </TopSort>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
sql-server partitioning

2 respostas

  1. Best Answer

    O otimizador pode produzir um plano ideal para esta consulta:

    Esquema de amostra

    CREATE PARTITION FUNCTION PF (datetime2(7)) AS 
RANGE RIGHT FOR VALUES 
(
    '20180101','20180102','20180103','20180104','20180105','20180106','20180107','20180108','20180109','20180110',
    '20180111','20180112','20180113','20180114','20180115','20180116','20180117','20180118','20180119','20180120',
    '20180121','20180122','20180123','20180124','20180125','20180126','20180127','20180128','20180129','20180130',
    '20180131','20180201','20180202','20180203','20180204','20180205','20180206','20180207','20180208','20180209',
    '20180210','20180211','20180212','20180213','20180214','20180215','20180216','20180217','20180218','20180219',
    '20180220','20180221','20180222','20180223','20180224','20180225','20180226','20180227','20180228','20180301',
    '20180302','20180303','20180304','20180305','20180306','20180307','20180308','20180309','20180310','20180311',
    '20180312','20180313','20180314','20180315','20180316','20180317','20180318','20180319','20180320','20180321',
    '20180322','20180323','20180324','20180325','20180326','20180327','20180328','20180329','20180330','20180331',
    '20180401','20180402','20180403','20180404','20180405','20180406','20180407','20180408','20180409','20180410',
    '20180411','20180412','20180413','20180414','20180415','20180416','20180417','20180418','20180419','20180420',
    '20180421','20180422','20180423','20180424','20180425','20180426','20180427','20180428','20180429','20180430',
    '20180501','20180502','20180503','20180504','20180505','20180506','20180507','20180508','20180509','20180510',
    '20180511','20180512','20180513','20180514','20180515','20180516','20180517','20180518','20180519','20180520',
    '20180521','20180522','20180523','20180524','20180525','20180526','20180527','20180528','20180529','20180530',
    '20180531','20180601','20180602','20180603','20180604','20180605','20180606','20180607','20180608','20180609',
    '20180610','20180611','20180612','20180613','20180614','20180615','20180616','20180617','20180618','20180619',
    '20180620','20180621','20180622','20180623','20180624','20180625','20180626','20180627','20180628','20180629',
    '20180630','20180701'
);

CREATE PARTITION SCHEME PS 
AS PARTITION PF 
ALL TO ([PRIMARY]);

    Dados de amostra

    CREATE TABLE [dbo].[OperationData]
(
    [DataLoggerID] [bigint] NOT NULL,
    [End] [datetime2](7) NOT NULL
    CONSTRAINT [PK_OperationData] PRIMARY KEY CLUSTERED 
    (
        [DataLoggerID] ASC,
        [End] ASC
    ) ON PS ([End])
);

-- Add one million rows
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.OperationData
    WITH (TABLOCKX)
    (DataLoggerID, [End])
SELECT
    Nums.n % 50000,
    DATEADD(DAY, Nums.n % 366, '20180101')
FROM Nums
WHERE
    Nums.n <= 1000 * 1000;

    Consulta original

    SELECT TOP (1)
    [OperationData].[DataLoggerID],
    [OperationData].[End]
FROM [dbo].[OperationData]
WHERE [OperationData].[DataLoggerID] = 30217  
ORDER BY [OperationData].[End] DESC;

    O plano de execução mostra uma busca ordenada para trás por partição, com a primeira linha de qualificação encontrada retornada:

    Plano de varredura para trás

    Procurar imóveis

    Isso é exatamente como você esperaria: processe cada partição do número mais alto para baixo, procure imediatamente encontrar a Enddata e hora mais altas para o dado DataLoggerIDe pare depois de encontrar a primeira linha.

    Este plano foi encontrado no Microsoft SQL Azure:

    Version="1.520" Build="15.0.400.390"

    Também verifiquei que o otimizador produz o mesmo plano ideal para todas as versões do SQL Server 2008 R2 ao SQL Server 2017. Isso inclui testes com 15.000 partições (quando disponíveis).

    Gambiarra

    Caso contrário, você pode escrever a lógica de consulta de forma mais explícita:

    CREATE TABLE #Partitions (partition_number integer PRIMARY KEY);

INSERT #Partitions 
    (partition_number)
SELECT
    P.partition_number
FROM sys.partitions AS P
WHERE
    P.[object_id] = OBJECT_ID(N'dbo.OperationData', 'U')

SELECT TOP (1) 
    LocalMinimum.DataLoggerID,
    LocalMinimum.[End]
FROM #Partitions AS P
CROSS APPLY
(
    SELECT TOP (1)
        OD.DataLoggerID,
        OD.[End]
    FROM  dbo.OperationData AS OD
    WHERE
        $PARTITION.PF(OD.[End]) = P.partition_number
        AND OD.DataLoggerID = 30217
    ORDER BY
        OD.[End] DESC
) AS LocalMinimum
WHERE
    LocalMinimum.DataLoggerID = 30217
ORDER BY
    P.partition_number DESC,
    LocalMinimum.[End] DESC;

    Isso fornece o seguinte plano para carregar os números de partição na tabela temporária:

    Carregar partições

    E este plano para a consulta por partição:

    Consulta explícita

    Mais uma vez, isso processa as partições na ordem inversa até que a primeira linha seja encontrada.

    • 11

  2. A eliminação de partição não é possível aqui porque os metadados da partição não possuem as informações sobre se há alguma linha para esse DataLoggerID na partição principal.

    • 1

relate perguntas