Creating 15000 partitions

Question

Creating 15000 partitions

shamshad.ali

Hall of Fame

Points: 3850
More actions
September 3, 2015 at 5:35 am

#320283

We have to move daily transactions on archive (daily bases we move day end data on archive DB) and I see that table partitioning can do this in better way. Managing (backup, indexing) and querying on archived data would be easily available. I need some help on this how can I create Partition(s) for daily bases, i saw we can create 15K partitions if we are on SQL Server 2012 or higher. Please let me know what would be the script for Create Partition for daily bases?
Sample on MSDN for creating partition of 15K is:
--Create integer partition function for 15,000 partitions.
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
SET @i += 1;
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO
Shamshad Ali

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

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

shamshad.ali (9/3/2015)
We have to move daily transactions on archive (daily bases we move day end data on archive DB) and I see that table partitioning can do this in better way. Managing (backup, indexing) and querying on archived data would be easily available. I need some help on this how can I create Partition(s) for daily bases, i saw we can create 15K partitions if we are on SQL Server 2012 or higher. Please let me know what would be the script for Create Partition for daily bases?
Sample on MSDN for creating partition of 15K is:
--Create integer partition function for 15,000 partitions.
DECLARE @IntegerPartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION IntegerPartitionFunction (int) AS RANGE RIGHT FOR VALUES (';
DECLARE @i int = 1;
WHILE @i < 14999
BEGIN
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N', ';
SET @i += 1;
END
SET @IntegerPartitionFunction += CAST(@i as nvarchar(10)) + N');';
EXEC sp_executesql @IntegerPartitionFunction;
GO
Shamshad Ali

You're looking for a script and I have to tell you that's the wrong thing to do when it comes to partitioning especially for 15,000 partitions.

If you're going to partition tables and have to support it for when things do go wrong (or even when they go right), you need to have a deep understanding of what partitioning is all about. There are many caveats especially when it comes to things like unique indexes/FKs/PKs, aligned indexes, and the nature of SPLIT itself.

And if you're partitioning for performance, forget about it. Partitioning is not a panacea for performance and will, many times, actually provide less performance because of the nature of the underlying B-Trees of the table (clustered index) and related non-clustered indexes.

Please do yourself a favor. If you don't already have it, get a copy of Books Online and read every single word on all the links in every article in Books Online and then sit down and create a detailed plan for partitioning so that you and your company don't end up shooting yourselves in the mouth. Make sure that you consider how big the table will actually get, how to "compress" the free space out of static files/filegroups, whether or not to make them Read-Only for savings on backups, what you need to do for index maintenance, how it will affect DRs and restores, and much, much more.

The bottom line is that if you setup partitioning and thought is was easy, then you've probably done something seriously wrong.

--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)

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 2

I googled for efficient way of archiving and found partitioning with several advantages. And Please let me know how do I resolve this if partitioning is much complex and not a way for archiving? Please let me know if we have a log table in which we write each and every hit from application and it is banking system data from whole country with thousands of branches. Should we clean it every day end and move on another database or we achieve it by some other way. Please provide solution if you need further detail.

Shamshad Ali

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 3

shamshad.ali (9/3/2015)
I googled for efficient way of archiving and found partitioning with several advantages. And Please let me know how do I resolve this if partitioning is much complex and not a way for archiving? Please let me know if we have a log table in which we write each and every hit from application and it is banking system data from whole country with thousands of branches. Should we clean it every day end and move on another database or we achieve it by some other way. Please provide solution if you need further detail.
Shamshad Ali

It's a great way to archive. But you have to do some serious study first so that you know what's going on. I can certainly post some code that will demonstrate it all but you have to do the study with Books Online that I recommended or it will burn you.

--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)

shamshad.ali Hall of Fame Points: 3850 More actions · Answer 4

I have an assignment on this and prepare a presentation for this task in few days. Please do not forget your post on this. I need to resolve it ASAP.

Shamshad Ali.

shamshadali74@gmail.com

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

shamshad.ali (9/3/2015)
I have an assignment on this and prepare a presentation for this task in few days. Please do not forget your post on this. I need to resolve it ASAP.
Shamshad Ali.
shamshadali74@gmail.com

Heh... it is YOUR assignment, not mine, and you still need to study to pull this whole thing off so you don't burn yourself or the company data. 😉 Do you even know what a Partition Function is?

Is all that you're interested in is the ability to move a day out of the table each day? If so, my code will do you no good. You need to post the DDL for the table you want to partition including all the indexes, identify how may rows per day the table will take on, how many rows the table currently has in it, what the current size of the table is in bytes including the indexes, how much free space you have, whether or not any of the columns are targets of FKs (those will likely go away if present), and the column you want to partition on. I can't even modify the original code you posted because I don't even know the name of the column you want to partition on.

I'll also warn you that partitioning can actually degrade performance for many of your queries.

Of course, if you actually read what I suggested you read, you'd already know that. 😉

--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)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

What's up? Did you quit or did you get the answer that you think you want from somewhere else?

--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)