Lesson Learned #96: Updating a non-clustered index in a partitioned table.

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a service request that our customer has implemented a process to rebuild the indexes and after it they update the statistics. In this rebuild process I found that they have some partitioned table but they are rebuild the entire table instead of the latest partition.

Based on this URL our customer is able using the parameter PARTITION=n, reducing considerably the time spent in this rebuild process.

Also, they asked a good question about, what happen with the other indexes that we have in this partitioned table, is it posible to use PARTITION=n for them, and the answer is yes, let me explain with an example:

CREATE PARTITION FUNCTION PF_HASH_BY_VALUE (BIGINT) AS RANGE LEFT 
FOR VALUES (100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000)

CREATE PARTITION SCHEME PS_HASH_BY_VALUE 
AS PARTITION PF_HASH_BY_VALUE
ALL TO ([PRIMARY]);
GO

CREATE TABLE [TBL_PARTITION] 
( [MY_VALUE] [bigint] NOT NULL,
  [AGE] [int],
  CONSTRAINT [PK_TBL_PARTITION] PRIMARY KEY CLUSTERED ([MY_VALUE] ASC)
) ON PS_HASH_BY_VALUE ([MY_VALUE])

insert into [TBL_PARTITION] (my_value) values(100001)
insert into [TBL_PARTITION] (my_value) values(200001) 
insert into [TBL_PARTITION] (my_value) values(300001) 
insert into [TBL_PARTITION] (my_value) values(400001) 
insert into [TBL_PARTITION] (my_value) values(500001) 
insert into [TBL_PARTITION] (my_value) values(600001) 
insert into [TBL_PARTITION] (my_value) values(700001) 
insert into [TBL_PARTITION] (my_value) values(800001) 
insert into [TBL_PARTITION] (my_value) values(900001)

insert into [TBL_PARTITION] (my_value) values(100002)
insert into [TBL_PARTITION] (my_value) values(200002) 
insert into [TBL_PARTITION] (my_value) values(300002) 
insert into [TBL_PARTITION] (my_value) values(400002) 
insert into [TBL_PARTITION] (my_value) values(500002) 
insert into [TBL_PARTITION] (my_value) values(600002) 
insert into [TBL_PARTITION] (my_value) values(700002) 
insert into [TBL_PARTITION] (my_value) values(800002) 
insert into [TBL_PARTITION] (my_value) values(900002)

I created an index CREATE INDEX TBL_PARTITION_IX1 ON TBL_PARTITION (AGE) and after it, executing this command SELECT object_name(object_id),* FROM sys.dm_db_partition_stats where object_name(object_id)='TBL_PARTITION' you could see that the new index create will have the same partition definition, so, basically, I need to execute this command to rebuilt just only this index

ALTER INDEX [PK_TBL_PARTITION] ON TBL_PARTITION REBUILD PARTITION = 10;

Enjoy!

Leave a Reply Cancel reply