Un-partition and decompress tables

Question

Un-partition and decompress tables

Roust_m

SSCoach

Points: 17405
More actions
January 20, 2016 at 9:43 pm

#307268

Hi,
I am planning to un-partition and decompress some large tables to be able to use standard edition of SQL Server.
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt, should I do this with clustered indexes first and not clustered last or vise versa? I need to achieve the best performance and minimize the time.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

Roust_m (1/20/2016)
Hi,
I am planning to un-partition and decompress some large tables to be able to use standard edition of SQL Server.
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt, should I do this with clustered indexes first and not clustered last or vise versa? I need to achieve the best performance and minimize the time.
Thanks.

Quick thought, create new uncompressed tables, insert the data from the originals then drop and rename.

😎

Roust_m SSCoach Points: 17405 More actions · Answer 2

Eirikur Eiriksson (1/20/2016)
Roust_m (1/20/2016)
Hi,
I am planning to un-partition and decompress some large tables to be able to use standard edition of SQL Server.
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt, should I do this with clustered indexes first and not clustered last or vise versa? I need to achieve the best performance and minimize the time.
Thanks.
Quick thought, create new uncompressed tables, insert the data from the originals then drop and rename.
😎

I am planning to do this via TSQL script due to large number of databases and partitioned tables. This approach is difficult to implement using TSQL - too complex.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Roust_m (1/20/2016)
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt...

They do not, unless you're changing the keys of the clustered index. If you are, drop the non-clustered indexes first, drop and receate the cluster with the different key columns, recreate the non-clustered indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Roust_m (1/20/2016)
Hi,
I am planning to un-partition and decompress some large tables to be able to use standard edition of SQL Server.
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt, should I do this with clustered indexes first and not clustered last or vise versa? I need to achieve the best performance and minimize the time.
Thanks.

That's actually a mistake, IMHO. There's probably ([font="Arial Black"]see Note 2 below[/font]) no reason why you could not continue partitioning as a Partitioned View. Build one partitioning table at a time and switch out/drop the partition for the original on partition at a time and you won't even need the space to do the whole table all at once. You'll just need the space for the largest partition (after it's uncompressed, of course ;-)) and a little headroom.

And, I have to tell you, Partitioned Views are incredibly effective. Because of some of their traits, I usually prefer them over partitioned tables. For example, older partitions can be indexed for query performance while the "current" partition (if temporally based) can be indexed to allow for very rapid inserts.

One thing that you do have remember is the number "253" (IIRC, [font="Arial Black"]see note 1 below[/font]) because I believe that's the maximum number of SELECTs you can UNION ALL in a single statement. Seems like a limit but, if partitioned by month, that's more than 21 years of data and you still get the benefits of things like storing one partition per file group so that you can still make older static partitions READ_ONLY so that you don't have to backup those that have not suffered any changes. You also get the continue benefit of greatly reduced index maintenance and it's actually very easy to "switch" in and out the partition tables that support the view simply by rebuilding the view with only the tables that you want and can do so on "switch outs" without having to exactly duplicate a table to switch out to. You simply stop including it in the Partitioned View and you're done.

[font="Arial Black"]Note 1: [/font] BOL (https://msdn.microsoft.com/en-us/library/ms191141.aspx) suggests that, and I quote...

Any number of UNION operators can appear in a Transact-SQL statement.

First, for a Partitioned View, they should be UNION ALL and not UNION. It's been a while since I've had to make a "monster" partitioned view so some things may have changed but, back then, there seemed to be a hard limit at ~253 SELECTs... I strongly recommend a test if you plan to exceed that number quickly (partition by day, for example).

Another limit is that the code must be the lesser of maximum batch size or 250MB. Please see the following for that and other max capacity information. https://msdn.microsoft.com/en-us/library/ms143432.aspx. This limit may be what caused it to look like 253 was the max number of UNION ALLed SELECTs . It may simply have been the number of bytes in the definition of the view.

[font="Arial Black"]Note 2: [/font]Older versions of SQL Server came with the threat that Partitioned Views were deprecated and would be removed in a future version. True or not, that threat has been removed. Please see the following article on CREATE VIEW and do a search for the word "Partition". I've also checked the deprecation lists and the word "Partitioned" doesn't even appear in them. The recommendation in BOL is that Partitioned Tables are the "recommended method" but that requires the Enterprise Edition and you can't forget the marketing ploys build into BOL to get you to buy the Enterprise Edition. With a little forethought and planning, Partitioned Views can be at least as effective as Partitioned Tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roust_m SSCoach Points: 17405 More actions · Answer 5

Jeff Moden (1/21/2016)
Roust_m (1/20/2016)
Hi,
I am planning to un-partition and decompress some large tables to be able to use standard edition of SQL Server.
Given that not clustered indexes get rebuilt when a clustered index gets rebuilt, should I do this with clustered indexes first and not clustered last or vise versa? I need to achieve the best performance and minimize the time.
Thanks.
That's actually a mistake, IMHO. There's probably ([font="Arial Black"]see Note 2 below[/font]) no reason why you could not continue partitioning as a Partitioned View. Build one partitioning table at a time and switch out/drop the partition for the original on partition at a time and you won't even need the space to do the whole table all at once. You'll just need the space for the largest partition (after it's uncompressed, of course ;-)) and a little headroom.
And, I have to tell you, Partitioned Views are incredibly effective. Because of some of their traits, I usually prefer them over partitioned tables. For example, older partitions can be indexed for query performance while the "current" partition (if temporally based) can be indexed to allow for very rapid inserts.
One thing that you do have remember is the number "253" (IIRC, [font="Arial Black"]see note 1 below[/font]) because I believe that's the maximum number of SELECTs you can UNION ALL in a single statement. Seems like a limit but, if partitioned by month, that's more than 21 years of data and you still get the benefits of things like storing one partition per file group so that you can still make older static partitions READ_ONLY so that you don't have to backup those that have not suffered any changes. You also get the continue benefit of greatly reduced index maintenance and it's actually very easy to "switch" in and out the partition tables that support the view simply by rebuilding the view with only the tables that you want and can do so on "switch outs" without having to exactly duplicate a table to switch out to. You simply stop including it in the Partitioned View and you're done.
[font="Arial Black"]Note 1: [/font] BOL (https://msdn.microsoft.com/en-us/library/ms191141.aspx) suggests that, and I quote...
Any number of UNION operators can appear in a Transact-SQL statement.
First, for a Partitioned View, they should be UNION ALL and not UNION. It's been a while since I've had to make a "monster" partitioned view so some things may have changed but, back then, there seemed to be a hard limit at ~253 SELECTs... I strongly recommend a test if you plan to exceed that number quickly (partition by day, for example).
Another limit is that the code must be the lesser of maximum batch size or 250MB. Please see the following for that and other max capacity information. https://msdn.microsoft.com/en-us/library/ms143432.aspx. This limit may be what caused it to look like 253 was the max number of UNION ALLed SELECTs . It may simply have been the number of bytes in the definition of the view.
[font="Arial Black"]Note 2: [/font]Older versions of SQL Server came with the threat that Partitioned Views were deprecated and would be removed in a future version. True or not, that threat has been removed. Please see the following article on CREATE VIEW and do a search for the word "Partition". I've also checked the deprecation lists and the word "Partitioned" doesn't even appear in them. The recommendation in BOL is that Partitioned Tables are the "recommended method" but that requires the Enterprise Edition and you can't forget the marketing ploys build into BOL to get you to buy the Enterprise Edition. With a little forethought and planning, Partitioned Views can be at least as effective as Partitioned Tables.

Hi Jeff,

Thanks for your detailed explanation. I will research partitioned views, but given the number of databases and the amount of money/time the company is willing to spend on this, I will have to go with removing partitioning I am afraid. This server is not performance critical, so they will not invest into a large migration project.