Sql-Server

按順序消除分區

  • June 4, 2018

我有一個分區表和分區消除問題。

設置

我的表很簡單，看起來像這樣：

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

[End]該表按日期按列分區。

問題

我執行一個簡單的查詢以逐[End]列查找最新的行。

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

問題是執行計劃顯示搜尋了所有分區並且沒有發生分區消除。

統計數據

問題

此查詢是否可以消除分區？如果是，為什麼不使用它？

多一點…

我無法將計劃粘貼到“粘貼計劃”（它顯示錯誤），所以我將其複製到這裡。

<?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)
    [OperationData].[DataLoggerID],
   [OperationData].[End]
FROM [data].[OperationData] AS [OperationData]  
WHERE [OperationData].[DataLoggerID] = 30217  
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>

優化器可以為此查詢​​生成一個最佳計劃：

範例架構

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]);

樣本數據

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;

原始查詢

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

執行計劃顯示每個分區進行一次向後排序的查找，並返回找到的第一個符合條件的行：

後向掃描計劃

尋找房產

這正是您所希望的：從最高數字向下處理每個分區，立即尋找給End定的最高日期和時間DataLoggerID，並在找到第一行後停止。

這個計劃是在 Microsoft SQL Azure 上找到的：

Version="1.520" Build="15.0.400.390"

我還驗證了優化器為從 SQL Server 2008 R2 到 SQL Server 2017 的所有版本生成了相同的優化計劃。這包括使用 15,000 個分區（如果可用）進行測試。

解決方法

否則，您可以更明確地編寫查詢邏輯：

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;

這給出了將分區號載入到臨時表中的以下計劃：

載入分區

這個針對每個分區查詢的計劃：

顯式查詢

再一次，這會以相反的順序處理分區，直到找到第一行。

引用自：https://dba.stackexchange.com/questions/208603

相關問答

Sql-Server

Azure SQL - 超大規模服務層 - 是否支持表分區

  • March 11, 2022
檢視問答
Sql-Server

分區和現有索引

  • September 14, 2021
檢視問答
Sql-Server

分區切換與模式傳輸

  • March 1, 2021
檢視問答
Sql-Server

檢查分區函式是否存在值列表 ，如果不遍歷列表並將新的分區值添加到分區函式？

  • February 16, 2020
檢視問答
Sql-Server

在 SQL-Server 中對多租戶數據進行分區的建議

  • September 3, 2017
檢視問答
Sql-Server

對所有表授予 SELECT、UPDATE、DELETE、ALTER 的權限

  • September 13, 2022
檢視問答