optimizar - seleccionar los primeros 10 registros sql

¿Por qué seleccionar arriba... ordenar por columna indexada todavía ordenar?

Question

Creé el siguiente índice para cubrir la declaración superior seleccionada.

-- Column A, B have type of int
create unique index ix_ on T (A, B) with (data_compression = page)
-- tried to create non-unique index too and the execution plan is the same
select top 20 A, B from T order by A, B -- 19 seconds
select top 20 A, B from T -- return result instantly

Sin embargo, todavía toma un tiempo (19 segundos en mi mesa que tiene 50 millones de filas) y el plan de ejecución muestra que todavía hay un "Ordenar".

El plan de ejecución muestra

Seleccionar (Costo: 0%) ← Superior (Costo: 0%) ← Paralelismo (Reunir flujos) (Costo: 0%) ← Ordenar (Clasificación superior N) Costo: 93% ← Escaneo de índice (no agrupado) [T.ix_] Costo : 7%

Plan de ejecución

<?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.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="552.009" StatementText="select top 20 A A, B B --, checksum(*) cs&#xA;from T with (index(ix_))&#xA;order by A, B" StatementType="SELECT" QueryHash="0x1531573504856080" QueryPlanHash="0x5D4FED760C34AF43" RetrievedFromCache="true">
<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="8" MemoryGrant="1024" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="256">
<ThreadStat Branches="1" UsedThreads="8">
<ThreadReservation NodeId="0" ReservedThreads="8" />
</ThreadStat>
<MemoryGrantInfo SerialRequiredMemory="16" SerialDesiredMemory="24" RequiredMemory="896" DesiredMemory="960" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="896" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="768000" EstimatedPagesCached="768000" EstimatedAvailableDegreeOfParallelism="8" />
<RelOp AvgRowSize="15" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="552.009">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(20)">
<Const ConstValue="(20)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="0.0286101" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="552.009">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="20" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="212.739" EstimateIO="303.269" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="551.98">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="20" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="12" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<TopSort Distinct="false" Rows="20">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="15" EstimateCPU="5.81245" EstimateIO="30.16" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="42226500" LogicalOp="Index Scan" NodeId="3" Parallel="true" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="35.9724" TableCardinality="42226500">
<OutputList>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="8" ActualRows="3993270" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="7" ActualRows="2713924" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="6" ActualRows="8866373" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="5" ActualRows="10625143" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="4" ActualRows="4254726" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="3" ActualRows="3195887" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="2" ActualRows="3626671" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="1" ActualRows="4950538" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="41">
<PartitionRange Start="1" End="41" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="B" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DB]" Schema="[dbo]" Table="[T]" Alias="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[DB]" Schema="[dbo]" Table="[T]" Index="[ix_]" Alias="[T]" IndexKind="NonClustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</TopSort>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Answer 1

Su tabla está dividida por B.

El índice hereda este esquema de particionamiento a menos que especifique lo contrario. Por ejemplo con

create unique index ix_ on T (A, B) with (data_compression = page) on [primary] 

(En cuyo caso se vuelve no alineado y evita algunas operaciones, como el cambio de metadatos solamente)

El valor "A" más bajo podría estar en cualquier partición.

Esto no está muy bien optimizado. Puede mantener el índice alineado y usar esta reescritura según el código aquí

SELECT TOP 20 A, B
FROM sys.partitions AS P
CROSS APPLY ( SELECT TOP 20 A, B
FROM dbo.T
WHERE $PARTITION.YourPartitionFunction(T.B) = P.partition_number
ORDER BY A,B
) AS A
WHERE P.object_id = OBJECT_ID(''dbo.T'')
AND P.index_id = INDEXPROPERTY( OBJECT_ID(''dbo.T''), ''ix_'', ''IndexID'' )
ORDER BY A,B

Obtendrá las 20 filas superiores de cada una de las 41 particiones (sin ordenarlas) y luego clasificará las 820 filas que resultan de eso para obtener el top 20 final (en lugar de los 42 millones enteros).