sql server 2005 – table partitioning vinod kumar intel technology india pvt. ltd. mvp – sql...
TRANSCRIPT
SQL Server 2005 – SQL Server 2005 – Table PartitioningTable Partitioning
Vinod KumarVinod KumarIntel Technology India Pvt. Ltd.Intel Technology India Pvt. Ltd.MVP – SQL ServerMVP – SQL Serverwww.ExtremeExperts.comwww.ExtremeExperts.com
AgendaAgenda
Understanding PartitioningUnderstanding Partitioning
DDL commandsDDL commands
Aligning PartitionsAligning Partitions
Partition best practicesPartition best practices
Add and Drop partitionsAdd and Drop partitions
ConclusionConclusion
Types of Partitioning in Types of Partitioning in Database DesignsDatabase Designs
Application-managed partitioningApplication-managed partitioningData is divided among multiple tables or across Data is divided among multiple tables or across serversservers
Application decides where to direct specific Application decides where to direct specific queries at execution timequeries at execution time
Partition ViewsPartition Views‘‘UNION’ Views link tables within or across UNION’ Views link tables within or across databases and servers (DPVs)databases and servers (DPVs)
Partitioned Tables and IndexesPartitioned Tables and IndexesNew in SQL Server 2005New in SQL Server 2005
Applies to objects in a Applies to objects in a singlesingle database and database and instanceinstance
Focus of this talk!Focus of this talk!
Partitioned Tables and Partitioned Tables and IndexesIndexes
Split tables and indexes into multiple Split tables and indexes into multiple storage objects based on the value of a storage objects based on the value of a data columndata column
Based on range of a single column’s valueBased on range of a single column’s value
Still treated as single object by the Still treated as single object by the relational enginerelational engine
Handled as multiple objects by the Handled as multiple objects by the storage enginestorage engine
Up to 1000 partitions per object supportedUp to 1000 partitions per object supported
Partitioning and StoragePartitioning and Storage
Customer ID IndexCustomer ID Index
Order History TableOrder History Table
Nonpartitioned:Nonpartitioned:
Filegroup DATAFilegroup DATA
Filegroup IDXFilegroup IDX
Example: Example: Table ORDER HISTORY with a Table ORDER HISTORY with a Nonclustered Index on CUSTOMER IDNonclustered Index on CUSTOMER ID
OrderOrderHistoryHistory
Customer IDCustomer ID
Order DateOrder Date
AmountAmount
……
Order IDOrder ID
Partitioning and StoragePartitioning and Storage
Customer ID IndexCustomer ID Index Customer ID IndexCustomer ID Index Customer ID IndexCustomer ID Index
Partitioned by ORDER Partitioned by ORDER DATE:DATE:
Order Date <Order Date <‘‘2003-01-01’2003-01-01’
Order Date >=Order Date >=‘‘2003-01-01’ and2003-01-01’ andOrder Date <Order Date <‘‘2004-01-01’2004-01-01’
Order Date >=Order Date >=‘‘2004-01-01’2004-01-01’
Filegroup Filegroup DATA_2002DATA_2002
Filegroup Filegroup DATA_2003DATA_2003
Filegroup Filegroup DATA_2004DATA_2004
Filegroup Filegroup IDX_2002IDX_2002
Filegroup Filegroup IDX_2003IDX_2003
Filegroup Filegroup IDX_2004IDX_2004
OrdersOrders
Customer IDCustomer ID
Order DateOrder Date
AmountAmount
……
Order IDOrder IDOrderOrder
HistoryHistory
Customer IDCustomer ID
Order DateOrder Date
AmountAmount
……
Order IDOrder ID
Order History TableOrder History Table Order History TableOrder History Table Order History TableOrder History Table
Benefits of Partitioned Benefits of Partitioned Tables Tables
ManageabilityManageabilityFast Fast Data Deletion and Data LoadData Deletion and Data Load
Piecemeal backup / restore of historical dataPiecemeal backup / restore of historical data
Partition-wise index managementPartition-wise index management
Minimize index fragmentation for historically-Minimize index fragmentation for historically-partitioned tablespartitioned tables
Support alternative storage for historical dataSupport alternative storage for historical data
Performance querying Large TablesPerformance querying Large TablesJoin efficiencyJoin efficiency
Smaller index tree or table scan when querying a Smaller index tree or table scan when querying a single partitionsingle partition
Simpler query plans compared to Partition ViewsSimpler query plans compared to Partition Views
Improvement Over Partition Improvement Over Partition ViewsViews
Partitioned Table: a Partitioned Table: a single objectsingle object in query plans in query plansSingle set of statisticsSingle set of statisticsSmaller plans, faster compilation than Partition ViewsSmaller plans, faster compilation than Partition Views
Auto-parameterization supportedAuto-parameterization supportedInsert / Bulk Insert / BCP fully supportedInsert / Bulk Insert / BCP fully supportedNumerous fine-grained partitions work wellNumerous fine-grained partitions work wellQueries may access partitions in parallelQueries may access partitions in parallel
Partition is the Partition is the unitunit of parallelism of parallelism
But…But…Cannot span multiple DBs or instancesCannot span multiple DBs or instances
Potentially use PV or DPVs atop Partitioned TablesPotentially use PV or DPVs atop Partitioned Tables
Partition Building BlocksPartition Building Blocks
Objects:Objects:
Partition FunctionPartition Function
Partition SchemePartition Scheme
Operations:Operations:
Split PartitionSplit Partition
Merge PartitionMerge Partition
Switch PartitionSwitch Partition
Partition FunctionPartition Function
Maps ranges of a data type to integer Maps ranges of a data type to integer values values
Defined by specifying boundary pointsDefined by specifying boundary points
N boundary points define N+1 N boundary points define N+1 partitionspartitions
Partition #Partition #
11 22 33 44 55
BoundaryBoundary
11BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
44
Partition Function DDLPartition Function DDL
CREATE PARTITION FUNCTION annual_range CREATE PARTITION FUNCTION annual_range (DATETIME)(DATETIME)
as RANGE RIGHTas RANGE RIGHTfor valuesfor values(( -- Partition 1 -- -- Partition 1 -- 20012001 and earlier and earlier'200'20022-01-01',-01-01', -- Partition 2 -- 200-- Partition 2 -- 20022'200'20033-01-01',-01-01', -- Partition 3 -- 200-- Partition 3 -- 20033'200'20044-01-01',-01-01', -- Partition 4 -- 200-- Partition 4 -- 20044'200'20055-01-0-01-011'' -- Partition 5 -- Partition 5 ---- 200 2005 and later5 and later))
Values < Values < 20020022-01-01-01-01
20020022-01-01-01-01<=Values<<=Values<2003-01-012003-01-01
20020033-01-01-01-01<=Values<<=Values<2004-01-012004-01-01
20020044-01-01-01-01<=Values<<=Values<2005-01-012005-01-01
20020055-01-01-01-01<=Values<=Values
Range Right Datetime Range Right Datetime Partition ExamplePartition Example
Partition #Partition #
11 22 33 44 55
BoundaryBoundary
11BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
44
20020022-01-01-01-01 20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2001 & 2001 & EarlierEarlier
2002 Data2002 Data 2003 Data2003 Data 2004 Data2004 Data 2005 & 2005 & LaterLater
Partition Function NotesPartition Function Notes
Understand the difference between Understand the difference between LEFT and RIGHT LEFT and RIGHT
Use $partition.<Use $partition.<function_namefunction_name> > to query partition function valuesto query partition function values
Best practice:Best practice:Use RANGE RIGHT for ‘continuous’ data Use RANGE RIGHT for ‘continuous’ data values – more intuitivevalues – more intuitive
Boundary becomes starting point for each Boundary becomes starting point for each rangerange
Partition SchemePartition Scheme
Associates a storage location (Filegroup) Associates a storage location (Filegroup) with each partition defined by a partition with each partition defined by a partition functionfunctionNo No requirementrequirement to use different filegroups for to use different filegroups for different partitionsdifferent partitions
Useful for ManageabilityUseful for ManageabilityFilegroup-based backup or storage locationFilegroup-based backup or storage location
Best Practice: Spread Best Practice: Spread allall of your Filegroups of your Filegroups in a Partition Scheme across as many disk in a Partition Scheme across as many disk spindles as possible.spindles as possible.
Rarely want to dedicate separate drives to Rarely want to dedicate separate drives to separate partitionsseparate partitions
Partition Scheme DDLPartition Scheme DDLCREATE PARTITION SCHEME annual_scheme_1CREATE PARTITION SCHEME annual_scheme_1
as PARTITION annual_range toas PARTITION annual_range to
(annual_min,(annual_min, -- filegroup for pre-2002-- filegroup for pre-2002
annual_200annual_20022,, -- filegroup for 2002-- filegroup for 2002
annual_200annual_20033,, -- filegroup for 2003-- filegroup for 2003
annual_200annual_20044,, -- filegroup for 2004-- filegroup for 2004
annual_200annual_2005)5) -- filegroup for 2005 and later-- filegroup for 2005 and later
Create PARTITION SCHEME annual_scheme_2Create PARTITION SCHEME annual_scheme_2
as PARTITION annual_rangeas PARTITION annual_range
ALL to ([PRIMARY])ALL to ([PRIMARY])
Range Right DatetimeRange Right Datetime
Partition #Partition #11 22 33 44 55
BoundaryBoundary
11BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
4420020022-01-01-01-01 20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2001 & Earlier2001 & Earlier2002 Data2002 Data 2003 Data2003 Data 2004 Data2004 Data 2005 & Later2005 & Later
Filegroup Filegroup Annual_MiAnnual_Minn
Filegroup Filegroup Annual_200Annual_20022
Filegroup Filegroup Annual_200Annual_20033
Filegroup Filegroup Annual_200Annual_20044
Filegroup Filegroup Annual_200Annual_20055
Partition Partition FunctionFunction
Partition Partition SchemeScheme
Partitioned Tables & Partitioned Tables & IndexesIndexes
A single column must be selected as the A single column must be selected as the Partitioning KeyPartitioning Key
Partitioned Tables and Indexes are created on Partitioned Tables and Indexes are created on Partition SchemesPartition Schemes instead of instead of FilegroupsFilegroups
All query operations on tables or indexes are All query operations on tables or indexes are transparent to partitioningtransparent to partitioning
Different tables and indexes Different tables and indexes maymay share share common partition functions and schemescommon partition functions and schemes
Table orTable orIndexIndex
PartitionPartitionSchemeScheme
PartitionPartitionFunctionFunction
11 manymany manymany
Table and Index CreationTable and Index Creation
CREATE CREATE TABLE Order_History (TABLE Order_History (Order_ID Order_ID bigint,bigint,Order_DateOrder_Date datetime,datetime,Customer_IDCustomer_ID bigintbigint……
) ON ) ON Annual_Scheme_1(Order_Date)Annual_Scheme_1(Order_Date)
CREATE INDEX Order_Cust_IdxCREATE INDEX Order_Cust_IdxON Order_History(Order_ID)ON Order_History(Order_ID)
ON ON Annual_Scheme_1(Order_Date)Annual_Scheme_1(Order_Date)
Simple DemoSimple Demo
Index PartitioningIndex Partitioning
Partitioning Key of an Index Partitioning Key of an Index need not be part need not be part of of the Index Keythe Index Key
SQL 2005 indexes can include columns outside of SQL 2005 indexes can include columns outside of the btree, at the leaf level onlythe btree, at the leaf level onlyEssential for partitioning, and also great for covering Essential for partitioning, and also great for covering index scenariosindex scenarios
If an index uses a If an index uses a similar partition functionsimilar partition function and same partitioning key as the base table, and same partitioning key as the base table, then the index is “aligned”then the index is “aligned”
One-to-one correspondence between data in One-to-one correspondence between data in table and index partitiontable and index partitionAll index entries in one partition map to data in a All index entries in one partition map to data in a single partition of the base table single partition of the base table
Aligned Index Aligned Index One-to-one partition correspondenceOne-to-one partition correspondence
Table Partitions
Index Partitions
Having all aligned indexes is a best practiceHaving all aligned indexes is a best practice
Index AlignmentIndex Alignment
The GoodThe GoodAligned Indexes are the defaultAligned Indexes are the defaultPartitioning key is Partitioning key is automaticallyautomatically added to the index added to the indexDefaults to same partition scheme as the base tableDefaults to same partition scheme as the base table
The BadThe BadA Non-Aligned Index reduces the manageability benefits of A Non-Aligned Index reduces the manageability benefits of partitioning a large tablepartitioning a large tablePrevent Prevent fast Switchingfast Switching of data into / out of table of data into / out of table
The UglyThe UglyFor a UNIQUE or PK Index to be partitioned, the Partitioning For a UNIQUE or PK Index to be partitioned, the Partitioning Key Key mustmust be part of the Unique Key be part of the Unique KeyOtherwise the index will be non-partitioned – and therefore Otherwise the index will be non-partitioned – and therefore Non-AlignedNon-Aligned
Key vs. Alignment TradeoffKey vs. Alignment Tradeoff
Example: A large table of OrdersExample: A large table of OrdersNatural Partitioning Key = Natural Partitioning Key = Order_DateOrder_Date
Unique Business Key = Unique Business Key = Order_IDOrder_ID
Design Alternatives:Design Alternatives:1.1. Partition table on Partition table on Order_Date,Order_Date,
Non-UniqueNon-Unique, aligned index on Order_ID , aligned index on Order_ID
2.2. Partition table on Partition table on Order_DateOrder_Date a Unique, a Unique, non-alignednon-aligned index on index on Order_IDOrder_ID
3.3. Design the Order ID to always increase in time, Design the Order ID to always increase in time, Partition on Partition on Order_IDOrder_ID, align table and indexes, align table and indexes
Best Solution depends on your Best Solution depends on your workload!workload!
Restrict the Partitioning Key in the WHERE Restrict the Partitioning Key in the WHERE clause to reduce # of partitions touchedclause to reduce # of partitions touched
Critical for performance of both large queries and Critical for performance of both large queries and high-volume small querieshigh-volume small queries
Select * from OrderHistory o where …Select * from OrderHistory o where …o.date_key betweeno.date_key between ‘2002-01-01’ and ‘2002-06-30’ ‘2002-01-01’ and ‘2002-06-30’
Beware: Restrictions applied via a Beware: Restrictions applied via a joinjoin will will notnot eliminate partitionseliminate partitions
Following query will touch Following query will touch all all RegionRegion partitions:partitions:
Select * from Sales s INNER JOIN Region d Select * from Sales s INNER JOIN Region d on d.region_id = s.region_id where …on d.region_id = s.region_id where …d.Name = d.Name = ‘Asia’‘Asia’
Resolve by placing restriction on region_id Resolve by placing restriction on region_id insteadinstead
Querying -- Best PracticesQuerying -- Best Practices
s.region_id = s.region_id = 77
Other Query EnhancementsOther Query Enhancements
Many other operations can be Many other operations can be performed “per-partition”performed “per-partition”
Grouping, filtering, projectionGrouping, filtering, projection
Inserts, updates, deletesInserts, updates, deletes
Create index, bulk insertCreate index, bulk insert
SQL Server tries to find large groups of SQL Server tries to find large groups of operations that can be performed operations that can be performed per-partition to improve per-partition to improve query performancequery performance
DW Partitioning Best DW Partitioning Best PracticesPractices
Typically partition Fact Tables by PeriodTypically partition Fact Tables by PeriodEasily unload history and load new periodsEasily unload history and load new periodsMake typical queries more efficientMake typical queries more efficient
Partition on a meaningful datetime or integer Partition on a meaningful datetime or integer column that is usually column that is usually directly restricteddirectly restricted in in queries, e.g.queries, e.g.
WHERE Date_key between ‘2004-01-01’ and ‘2004-01-31’WHERE Date_key between ‘2004-01-01’ and ‘2004-01-31’WHERE Period_key = 200402 (month = Feb 2004)WHERE Period_key = 200402 (month = Feb 2004)WHERE Year_key = 2004WHERE Year_key = 2004
Don’t Partition based on a Don’t Partition based on a surrogatesurrogate period period keykey
……unless the query application can explicitly restrict the unless the query application can explicitly restrict the keykeyUsers typically don’t know those keys and can’t Users typically don’t know those keys and can’t constrain them directly in ad-hoc queriesconstrain them directly in ad-hoc queries
OLTP Partitioning Best OLTP Partitioning Best PracticesPractices
When partitioning large transaction tables for When partitioning large transaction tables for manageabilitymanageability
Partition directly on a Primary Key that Partition directly on a Primary Key that increases monotonically over timeincreases monotonically over time
Allows fast lookup on the key, simple Allows fast lookup on the key, simple maintenancemaintenanceAvoids non-aligned indexesAvoids non-aligned indexes
Some applications benefit from partitioning Some applications benefit from partitioning on Organization or Geographyon Organization or Geography
Especially if these are naturally part of a Especially if these are naturally part of a Primary KeyPrimary KeyPotential query performance benefitsPotential query performance benefits
Add and Drop OptionsAdd and Drop Options
Add & Drop Partitions?Add & Drop Partitions?
A typical requirement is to insert or A typical requirement is to insert or remove entire partitions of data in bulkremove entire partitions of data in bulk
Achieve this with a sequence of basic Achieve this with a sequence of basic operations on partitions:operations on partitions:
SplitSplit
MergeMerge
SwitchSwitch
SplitSplit
ALTER PARTITION FUNCTION … SPLIT ALTER PARTITION FUNCTION … SPLIT RANGE …RANGE …Adds a boundary point to a Partition Adds a boundary point to a Partition FunctionFunctionAffects all objects using that Partition Affects all objects using that Partition FunctionFunction
One partition is split into twoOne partition is split into twoThe The newnew partition is the one containing the partition is the one containing the newnew boundary point: boundary point:
To the right of boundary if RANGE RIGHTTo the right of boundary if RANGE RIGHTTo the left of boundary if RANGE LEFTTo the left of boundary if RANGE LEFT
Data that falls into the Data that falls into the newnew range is range is movedmoved from the split partition from the split partition
Split Example: Range RightSplit Example: Range Right
Partition #Partition #
11 22 33 44 55
BoundaryBoundary
11BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
44
20020022-01-01-01-01 20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2001 and2001 andEarlierEarlier
2002 Data2002 Data 2003 Data2003 Data 2002004 Data4 Data 2002005 and 5 and LaterLater
BoundaryBoundary
55
66
20020066-01-01-01-01
2006 and 2006 and LaterLater
2006 and later2006 and laterdata moveddata moved
ALTERALTER PARTITION FUNCTION annual_range PARTITION FUNCTION annual_range()()SPLIT RANGE (‘2006-01-01’)SPLIT RANGE (‘2006-01-01’)
2005 Data2005 Data
Split -- ConsiderationsSplit -- Considerations
Instantaneous if partition is Instantaneous if partition is emptyemptyThis is This is IO-intensiveIO-intensive if partition is populated if partition is populated
Any data on Any data on newnew side of the boundary is side of the boundary is physically deleted from old partition and inserted physically deleted from old partition and inserted into the into the newnew partition partitionFully logged operationFully logged operationExclusive table lock held for duration of SplitExclusive table lock held for duration of Split
Schema change, so plans are invalidatedSchema change, so plans are invalidatedAlways assign ‘Next Used’ filegroup in Always assign ‘Next Used’ filegroup in Partition Scheme before using SplitPartition Scheme before using Split
ALTER PARTITION SCHEME NEXT USED … ALTER PARTITION SCHEME NEXT USED … Specifies which filegroup holds new partition’s dataSpecifies which filegroup holds new partition’s data
MergeMerge
ALTER PARTITION FUNCTION … MERGE ALTER PARTITION FUNCTION … MERGE RANGERANGERemoves a boundary point from a Partition Removes a boundary point from a Partition Function Function Affects all objects using that Partition Affects all objects using that Partition FunctionFunctionThe partitions on each side of the boundary The partitions on each side of the boundary are merged into oneare merged into one
The partition that held the boundary value is The partition that held the boundary value is removedremoved
To the right of boundary if RANGE RIGHTTo the right of boundary if RANGE RIGHTTo the left of boundary if RANGE LEFTTo the left of boundary if RANGE LEFT
Data that was in the removed partition is Data that was in the removed partition is moved to the remaining partitionmoved to the remaining partition
BoundaryBoundary
11BoundaryBoundary
11BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
44
Merge Example: Range Merge Example: Range RightRight
11 22 33 44 55
BoundaryBoundary
22BoundaryBoundary
33BoundaryBoundary
44
20020022-01-01-01-01 20020033-01-01-01-0120020044-01-01-01-01 20020055-01-01-01-01
2001 and2001 andEarlierEarlier
2002002 Data2 Data 2002003 Data3 Data 2002004 Data4 Data 2002005 Data5 Data
BoundaryBoundary 55
66
2005 Data2005 Data
20020066-01-01-01-01
2006 and2006 andLaterLater
2002 Data2002 DataMovedMoved ALTERALTER PARTITION FUNCTION annual_range PARTITION FUNCTION annual_range()()
MERGE RANGE (‘2002-01-01’)MERGE RANGE (‘2002-01-01’)
22 33 44 5511
2002 and2002 andEarlierEarlier
Merge -- ConsiderationsMerge -- Considerations
Instantaneous if merged range is Instantaneous if merged range is emptyempty
IO intensiveIO intensive if the partition is already if the partition is already populatedpopulated
Any rows in the removed partition are Any rows in the removed partition are physically moved into the remaining physically moved into the remaining partition.partition.
Fully logged operationFully logged operation
Exclusive table lock held for duration of Exclusive table lock held for duration of SplitSplit
Schema change, so plans are Schema change, so plans are invalidatedinvalidated
SwitchSwitch
Instantly swaps the content of one partition Instantly swaps the content of one partition or table (source) with another table’s empty or table (source) with another table’s empty partition or an empty table (target)partition or an empty table (target)Metadata-only operation, no data movesMetadata-only operation, no data movesRestrictions:Restrictions:
Target table or partition must be Target table or partition must be emptyemptySource and target must be in Source and target must be in same filegroupsame filegroupSource must have all indexes required by the Source must have all indexes required by the target, aligned and in matching filegroupstarget, aligned and in matching filegroupsIf Target is a partition, Source must have check If Target is a partition, Source must have check constraints (if table) or a partition range that fits constraints (if table) or a partition range that fits within the target rangewithin the target range
All associated indexes are automatically All associated indexes are automatically Switched along with the table / partitionSwitched along with the table / partition
Switch – Table to PartitionSwitch – Table to Partition
PartitionPartition##
20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2003 Data2003 Data 2004 Data2004 Data
20020066-01-01-01-01
2005 Data2005 Data 2006 & 2006 & LaterLater
2002 Data2002 Data11 22 33 44 55
20020022-01-01-01-01
2001 & 2001 & EarlierEarlier
66
Alter Table BAlter Table BSWITCH TO ASWITCH TO APARTITION 2PARTITION 2
Table A:Table A:
Table B:Table B:
Filegroup Filegroup DATA_2002DATA_2002
[EMPTY][EMPTY]
CHECK CONSTRAINT:CHECK CONSTRAINT:B.Date_Key >= ‘2002-01-01’ andB.Date_Key >= ‘2002-01-01’ andB.Date_Key < ‘2003-01-01’B.Date_Key < ‘2003-01-01’[EMPTY][EMPTY]
Switch – Partition to TableSwitch – Partition to Table
PartitionPartition##
20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2003 Data2003 Data 2004 Data2004 Data
20020066-01-01-01-01
2005 Data2005 Data 2006 & 2006 & LaterLater
2002 Data2002 Data11 22 33 44 55
20020022-01-01-01-01
2001 & 2001 & EarlierEarlier
66
Alter Table AAlter Table ASWITCH SWITCH PARTITION 2PARTITION 2to Bto B
Table A:Table A:
Table B:Table B:
Filegroup Filegroup DATA_2002DATA_2002
[EMPTY][EMPTY]
[EMPTY][EMPTY]
Switch – Partition to PartitionSwitch – Partition to Partition
PartitionPartition##
20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2003 Data2003 Data 2004 Data2004 Data
20020066-01-01-01-01
2005 Data2005 Data 2006 & 2006 & LaterLater
2002 Data2002 Data11 22 33 44 55
20020022-01-01-01-01
2001 & 2001 & EarlierEarlier
66
Alter Table AAlter Table ASWITCH SWITCH PARTITION 2PARTITION 2to B to B PARTITION 2PARTITION 2
Table A:Table A:
Table B:Table B:
Filegroup Filegroup DATA_2002DATA_2002
[EMPTY][EMPTY]
[EMPTY][EMPTY]
PartitionPartition##
2003 &2003 &LaterLater
2002 Data2002 Data11 22 33
2001 & 2001 & EarlierEarlier
Switch TipsSwitch Tips
If Switching between a nonpartitioned If Switching between a nonpartitioned table and a partition, be sure that the table and a partition, be sure that the nonpartitioned table’s indexes nonpartitioned table’s indexes include include the partitioning keythe partitioning key
May need to explicitly specify the column May need to explicitly specify the column using the INCLUDE option of CREATE using the INCLUDE option of CREATE INDEX (Beta 3)INDEX (Beta 3)
Use the $partition function in scripts Use the $partition function in scripts that automate the Switch command that automate the Switch command
Avoids absolute partition number Avoids absolute partition number referencesreferencesHelpful since partition numbers may Helpful since partition numbers may change over timechange over time
Deep Dive Deep Dive into Partitioninginto Partitioning
ScenariosScenarios
Sliding Window Load and DeleteSliding Window Load and Delete
Efficient Backup and RestoreEfficient Backup and Restore
Partition-wise Index MaintenancePartition-wise Index Maintenance
Snapshot (for availability)Snapshot (for availability)
Sliding Window ScenarioSliding Window ScenarioAssumptionsAssumptions
Large database Large database
Each hour/day/week/month…add new Each hour/day/week/month…add new partition and remove the oldest one.partition and remove the oldest one.
New partition – New partition – May need to batch load, scrub, and May need to batch load, scrub, and transform before incorporating into the transform before incorporating into the whole tablewhole table
OrOr start partition as empty and populate start partition as empty and populate gradually using transactions.gradually using transactions.
Old partition – may need backup, Old partition – may need backup, archive, restore.archive, restore.
Loading Most Recent DataLoading Most Recent Data
Partition #Partition #11 22 33 44 55
20020022-01-01-01-01 20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2001 & 2001 & EarlierEarlier
2002 Data2002 Data 2003 Data2003 Data 2004 Data2004 Data 2005 & 2005 & LaterLater
[EMPTY][EMPTY] [EMPTY][EMPTY]
Create staging table in same Create staging table in same filegroup as target partition (2005)filegroup as target partition (2005)SplitSplit most recent partition, most recent partition,adding boundary pointadding boundary pointfor following periodfor following periodBulk load and index staging tableBulk load and index staging tableSwitchSwitch data into next-to-last partition data into next-to-last partition
[EMPTY][EMPTY]
20020066-01-01-01-01
2005 Data2005 Data 2006 & 2006 & LaterLater
66
2005 Staging 2005 Staging TableTable
Unloading Oldest DataUnloading Oldest Data
Partition #Partition #
20020033-01-01-01-01 20020044-01-01-01-01 20020055-01-01-01-01
2003 Data2003 Data 2004 Data2004 Data
Create unload table in same Create unload table in same filegroup as partition to remove filegroup as partition to remove (2002)(2002)
SwitchSwitch data out of second partition data out of second partition
MergeMerge first partition, removing the first partition, removing the boundary point for the unload boundary point for the unload periodperiod
Archive or Truncate the unload Archive or Truncate the unload tabletable
[EMPTY][EMPTY]
20020066-01-01-01-01
2005 Data2005 Data 2006 & 2006 & LaterLater
2002 Data2002 Data
11 22 33 44 55
20020022-01-01-01-01
2001 & 2001 & EarlierEarlier
[EMPTY][EMPTY]
66
2002 Unload 2002 Unload TableTable
[EMPTY][EMPTY]
11 22 33 44 55
2002 & 2002 & EarlierEarlier
Sliding Window – Best Sliding Window – Best PracticesPractices
Using Range Right Partitioning:Using Range Right Partitioning:Always maintain Always maintain emptyempty partitions for partitions for the earliest and latest date rangesthe earliest and latest date ranges
Ensures that Split and Merge is Ensures that Split and Merge is instantaneousinstantaneous
Adding New Data:Adding New Data:First Split latest date range, then Switch First Split latest date range, then Switch data into the partition to the left of the data into the partition to the left of the boundaryboundary
Removing Old Data:Removing Old Data:First Switch data out of the oldest First Switch data out of the oldest populated partition (Partition #2), then populated partition (Partition #2), then Merge the first date rangeMerge the first date range
ConclusionConclusion
Plan ahead:Plan ahead:Consider Partition Keys and relationship Consider Partition Keys and relationship to any required unique indexesto any required unique indexes
Ensure Partition Keys are restricted in Ensure Partition Keys are restricted in important queries’ WHERE clausesimportant queries’ WHERE clauses
Design for bulk load and bulk delete Design for bulk load and bulk delete strategystrategy
Design for backup and restore strategyDesign for backup and restore strategy
Questions !!!Questions !!!
Your FeedbackYour Feedbackis Important!is Important!
Please Fill Out the Please Fill Out the feedback formfeedback form
© 2005 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
Backup SlidesBackup Slides
General Best PracticesGeneral Best PracticesUse ALIGNED indexesUse ALIGNED indexesSplit and Merge only Split and Merge only emptyempty partitions for best partitions for best maintenance performancemaintenance performanceSpread all data across as many disks as Spread all data across as many disks as possiblepossibleTest all of your partitioning operations & Test all of your partitioning operations & scripts on empty tables and indexes first, scripts on empty tables and indexes first, including including
Piecemeal backup and restorePiecemeal backup and restoreMaintenance plansMaintenance plansSplit / merge / switch operationsSplit / merge / switch operationsNew filegroups and ALTER PARTITION SCHEMENew filegroups and ALTER PARTITION SCHEME
Backup & RestoreBackup & Restore
Partitioning leverage Filegroup Partitioning leverage Filegroup Backup / Restore enhancements in SQL Backup / Restore enhancements in SQL Server 2005Server 2005
Read-Only Filegroups can now be Read-Only Filegroups can now be restored restored without without applying transaction logsapplying transaction logs
Reduces data volume for regular Reduces data volume for regular backups when historical data is not backups when historical data is not changingchanging
Online RestoreOnline Restore
SQL Server 2000SQL Server 2000Database is not available during restoreDatabase is not available during restore
YukonYukonDatabase remains onlineDatabase remains online
Only data being restored is offlineOnly data being restored is offline
OptionsOptionsFile / Filegroup RestoreFile / Filegroup Restore
Damaged Page Tracking and Page-Level Damaged Page Tracking and Page-Level RestoreRestore
Piecemeal RestorePiecemeal RestoreOnline restore of filegroups by priorityOnline restore of filegroups by priority
Database is online if Primary filegroup is onlineDatabase is online if Primary filegroup is online
Online throughout restoreOnline throughout restoreDatabaseDatabase
PrimaryPrimaryFilegroupFilegroup
Filegroup AFilegroup A
Filegroup BFilegroup BFilegroup BFilegroup B
Filegroup AFilegroup A
PrimaryPrimaryBackupsBackups
LogLog
Piecemeal Backup & Piecemeal Backup & RestoreRestore
Mark filegroups for unchanging, Mark filegroups for unchanging, historical partitions as READ ONLY historical partitions as READ ONLY Perform one-time backup of these Perform one-time backup of these READ ONLY filegroupsREAD ONLY filegroupsRegularly backup only the Primary Regularly backup only the Primary filegroup and other filegroups filegroup and other filegroups containing active, changing datacontaining active, changing data
Potentially a small percentage of total dataPotentially a small percentage of total data
To Restore, restore Primary and active To Restore, restore Primary and active filegroups and recover log. Then filegroups and recover log. Then restore the read-only filegroups restore the read-only filegroups separatelyseparately
Partitioned Index Partitioned Index MaintenanceMaintenance
To reindex (REBUILD INDEX) a single To reindex (REBUILD INDEX) a single partition: partition: ALTER INDEX … ALTER INDEX … REBUILD PARTITION = <partition number>REBUILD PARTITION = <partition number>
To defragment (REORGANIZE) a single To defragment (REORGANIZE) a single partition: partition: ALTER INDEX … ALTER INDEX … REORGANIZE PARTITION = <partition REORGANIZE PARTITION = <partition number>number>
SQL Server 2000SQL Server 2000 SQL Server 2005SQL Server 2005
DBCC DBREINDEXDBCC DBREINDEX ALTER INDEX …ALTER INDEX …REBUILDREBUILD
DBCC DBCC INDEXDEFRAGINDEXDEFRAG
ALTER INDEX … ALTER INDEX … REORGANIZEREORGANIZE