Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Rebuild partition

By Federico Iori,

The idea for this article comes from

http://www.sqlservercentral.com/scripts/Truncate/69506/

and also the basic structure of code is copied from there .

Thus a big thanks goes to mr Pandekar .

Application:

In data warehouse sometime is necessary to rebuild not only indexes, but also heaps, because typical analytical query retrieves goes with full table scan .

Moreover , SQL server 2008 does not allow to rebuild online a partitioned index .

It is normal in DWH system that admin needs to rebuild a partition, without putting the table offline.

Ten years ago I was working with Oracle , and I would have accomplished this task with something like :

Duplicate table target_table to target_table_new, rebuild indexes on target_table_new identical like

target_table, then rename target_table to target_table_drop , then rename target_table_new to target_table, or usign synonyms

-How rebuild partition works: Uses 2 temporary tables
Copies all partition rows to a temporary table b, build indexes on this tables
then switch target partition into temp tab a
then switch data from temp tab b into target partition
then drop temporaty tables a and b

- Command for execution- exec REBUILD_PARTITION 'SchemaName','TableName',PartitionNumber

- Limitations :
1) It should not be working with compressed tables or indexes ,
2) Will not work with table containing foreign keys or check constraints
3) Will not work with clustered primary key defined as desc
4) Works only with partitions range datetime

5) Operation is not ATOMIC : it means that if switch command fails, target partition will stay empty.

Anyway data are not lost, need to be inserted manually : see lines 463-473

- Successful Test results for-
1) Nonclustered PK + indexes
2) Clustered PK + indexes

- Possible improvements :

1) support for compression

2) insert into stage table b select * from target partition is slow, should be replace with bulk insert from openrowset in some way

3) Support for check and foreign key constraints : in general they are not used in data warehouse , because there are very few processes with modifies data of tables , so contraints to enforce data integrity are not necessary.

 

I encourage everybody to use my procedure and test it . If many people will be happy with it, I could consider putting this procedure into an automatic jobs to defragment database. Today the procedure works, but I think is wise to launch under human supervision.

 

Federico Iori, Bratislava ( Slovakia ) , fediori@gmail.com

 

 

 

 

 

 

 

Total article views: 1700 | Views in the last 30 days: 14
 
Related Articles
FORUM

Rebuild Partitioned Indexes

Rebuilding Indexes

FORUM

Partition index

Partition index rebuild for all db's

FORUM

Rebuild Index

Rebuild Index

FORUM

Rebuilding indexes

Rebuilding indexes

ARTICLE

Targeted Index Performance Improvements

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving ...

Tags
data warehousing    
defragmentation    
online    
partition    
rebuild    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones