SQLServer 重建重组表某个分区索引

来源：互联网发布：mysql truncate速度编辑：程序博客网时间：2024/05/31 15:19

测试：

--DROP TABLE [DemoDB].[dbo].[Address]SELECT * INTO [DemoDB].[dbo].[Address] FROM [AdventureWorks2014].[Person].[Address]GOUSE [DemoDB]GO--创建分区函数CREATE PARTITION FUNCTION [PF_ID](INT) AS RANGE LEFT FOR VALUES (10000,15000)GO--创建分区方案CREATE PARTITION SCHEME [PS_ID] AS PARTITION [PF_ID] TO ([PRIMARY], [FG], [FG1] )GO--表分区ALTER TABLE [dbo].[Address]ADD CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([AddressID] ASC)ON [PS_ID]([AddressID])GO--删除1/4的数据,使其产生碎片DELETE FROM [dbo].[Address] WHERE [AddressID]%4=0--查看表的分区SELECT * FROM sys.partitions WHERE [object_id]=OBJECT_ID('Address')SELECT index_id,partition_number,avg_fragment_size_in_pages,page_count,alloc_unit_type_descFROM sys.Dm_db_index_physical_stats(Db_id(),Object_id('Address'),NULL,NULL,NULL)

--现在重建表第三个分区的索引，再看看，碎片减少了。ALTER INDEX [PK_Address] ON [dbo].[Address] REBUILD PARTITION = 3GO

--重建表所有分区的索引，可以使用不同的数据压缩。分区3 none是没有压缩的。ALTER INDEX [PK_Address] ON [dbo].[Address] REBUILD PARTITION = ALLWITH (DATA_COMPRESSION = NONE ON PARTITIONS (3), DATA_COMPRESSION = ROW ON PARTITIONS (2), DATA_COMPRESSION = PAGE ON PARTITIONS (1))GO

--以行方式压缩分区三（行压缩较久）ALTER INDEX [PK_Address] ON [dbo].[Address] REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = ROW ON PARTITIONS (3))GO

--同样，重组也适合ALTER INDEX [PK_Address] ON [dbo].[Address] REORGANIZE PARTITION = 3 GOALTER INDEX [PK_Address] ON [dbo].[Address] REORGANIZE PARTITION = ALLGO

0 0