Database Reference
In-Depth Information
In some cases, you can use this behavior to optimize queries against partitioned tables. In our case, you can have
the following algorithm:
1.
Read the top 100 modified rows from every partition using the
$PARTITION
function,
limiting execution to the single partition scope.
2.
Sort rows read in the previous step, and select the top 100 rows across all partitions.
3.
Select data from the clustered index for the 100 rows returned by the previous step.
The first step of the algorithm requires you to know the number of partitions in the table. You can use
sys.partition_range_values DMV
to find the number of boundary values in the partition function, which is one
less than the number of partitions in the table.
The code in Listing 15-34 shows an optimized version of the query.
Partitions
CTE returns the numbers
that correspond to the partition numbers in the table, which are used as filters in the
CROSS APPLY
operator of
Steps1and2
CTE. The
CROSS APPLY
operator implements the first step of the algorithm. Select in
CROSS APPLY
executed once per partition.
Listing 15-34.
Potential issues with data partitioning: Optimized query
declare
@LastDateModified datetime = '2014-05-25'
,@BoundaryValuesCount int
-- Getting number of boundary values in partition function
select @BoundaryValuesCount = max(boundary_id)
from sys.partition_functions pf join
sys.partition_range_values prf on
pf.function_id = prf.function_id
where pf.name = 'pfData'
;with Partitions(PartitionNum)
as
(
select 1
union all
select PartitionNum + 1
from Partitions
where PartitionNum <= @BoundaryValuesCount
)
,Steps1and2(Id, DateCreated, DateModified)
as
(
select top 100 PartData.ID, PartData.DateCreated, PartData.DateModified
from Partitions p
cross apply
( -- Step 1 - runs once per partition
select top 100 Id, DateCreated, DateModified
from dbo.Data
where
DateModified > @LastDateModified and
$Partition.pfData(DateCreated) =
p.PartitionNum
