March 15, 2018 at 8:27 am
Hi,
I've been given an environment that's performing badly. On inspection, I can see one the tables that are partitioned, appears to have stopped being partitioned at the end of 2016. This means the last partition has around 13 times more data than the other partitions (which is significant, relatively speaking, we're talking 100's of millions of rows).
I want to split this top partition into the correct ranges and in doing so, speeding up queries that are trying to access the data contained within.
I'm trying to work the best way of splitting the data into the correct partition ranges? The data is partitioned on a DATE column,and is currently partitioned by month.
March 15, 2018 at 8:36 am
wak_no1 - Thursday, March 15, 2018 8:27 AMHi,I've been given an environment that's performing badly. On inspection, I can see one the tables that are partitioned, appears to have stopped being partitioned at the end of 2016. This means the last partition has around 13 times more data than the other partitions (which is significant, relatively speaking, we're talking 100's of millions of rows).
I want to split this top partition into the correct ranges and in doing so, speeding up queries that are trying to access the data contained within.
I'm trying to work the best way of splitting the data into the correct partition ranges? The data is partitioned on a DATE column,and is currently partitioned by month.
So is the partition function not active for the table? I was under the impression that a table is either partitioned or it isn't. I'm not familiar with how you could stop any further partitioning, but have the remainder of the data still in various partitions. Maybe I just answered my own question... maybe the partition function just gets made inactive for the table, and then everything just continues to go into the "last current" partition? Anyway, I do remember that existing partitions can be manipulated to go elsewhere, and I recall seeing good posts from Jeff Moden on the details. You may want to search for posts by him on partitioning.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 15, 2018 at 8:46 am
sgmunson - Thursday, March 15, 2018 8:36 AMwak_no1 - Thursday, March 15, 2018 8:27 AMHi,I've been given an environment that's performing badly. On inspection, I can see one the tables that are partitioned, appears to have stopped being partitioned at the end of 2016. This means the last partition has around 13 times more data than the other partitions (which is significant, relatively speaking, we're talking 100's of millions of rows).
I want to split this top partition into the correct ranges and in doing so, speeding up queries that are trying to access the data contained within.
I'm trying to work the best way of splitting the data into the correct partition ranges? The data is partitioned on a DATE column,and is currently partitioned by month.
So is the partition function not active for the table? I was under the impression that a table is either partitioned or it isn't. I'm not familiar with how you could stop any further partitioning, but have the remainder of the data still in various partitions. Maybe I just answered my own question... maybe the partition function just gets made inactive for the table, and then everything just continues to go into the "last current" partition? Anyway, I do remember that existing partitions can be manipulated to go elsewhere, and I recall seeing good posts from Jeff Moden on the details. You may want to search for posts by him on partitioning.
As far as I can see the function appears to be valid. In SSMS, I've expanded the 'Storage' folder, and then expanded the 'Partition Functions' folder.
I'll be honest, I've only ever worked with partitions in Oracle, where splitting of existing partitions is straight forward. I'm relatively new to partitioning in SQL Server.
March 15, 2018 at 8:53 am
Sounds like your partition components are stale
Using Object Explorer,
[Your database]
> [Storage]
> [Partition Schemes]
> [Partition Functions]
Script out the partition scheme and function and compare with the data in your table.
If these components are out of date, then you have a fair amount of work to do:
Update the partition scheme and function.
You will probably have to create matching filegroups.
You will have to shuffle your data around to match the partition components
- at a guess, drop (or disable) ordinary indexes, rebuild clustered index, recreate ordinary indexes.
Check that the indexes are partition-aligned.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2018 at 9:01 am
ChrisM@Work - Thursday, March 15, 2018 8:53 AMSounds like your partition components are stale
Using Object Explorer,
[Your database]
> [Storage]
> [Partition Schemes]
> [Partition Functions]
Script out the partition scheme and function and compare with the data in your table.
If these components are out of date, then you have a fair amount of work to do:
Update the partition scheme and function.
You will probably have to create matching filegroups.
You will have to shuffle your data around to match the partition components
- at a guess, drop (or disable) ordinary indexes, rebuild clustered index, recreate ordinary indexes.
Check that the indexes are partition-aligned.
I can see in the function that the 'left for values' last specified value is 20161299 - there's nothing beyond this. Is this always a manual process, so you always need to specify the max range? Can this be automated?
There are no indexes on this table of nearly 2 billions rows, effectively making it a HEAP!
March 15, 2018 at 9:10 am
wak_no1 - Thursday, March 15, 2018 9:01 AMChrisM@Work - Thursday, March 15, 2018 8:53 AMSounds like your partition components are stale
Using Object Explorer,
[Your database]
> [Storage]
> [Partition Schemes]
> [Partition Functions]
Script out the partition scheme and function and compare with the data in your table.
If these components are out of date, then you have a fair amount of work to do:
Update the partition scheme and function.
You will probably have to create matching filegroups.
You will have to shuffle your data around to match the partition components
- at a guess, drop (or disable) ordinary indexes, rebuild clustered index, recreate ordinary indexes.
Check that the indexes are partition-aligned.
I can see in the function that the 'left for values' last specified value is 20161299 - there's nothing beyond this. Is this always a manual process, so you always need to specify the max range? Can this be automated?
There are no indexes on this table of nearly 2 billions rows, effectively making it a HEAP!
Here's the best beginners' tutorial I've been able to find for my colleagues, it's an awesome piece of work:
https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
What you're looking for is within it. I don't recommend you opt for a quick internet-sourced fix for this, but that's your choice.
Our biggest partitioned table is over 12,000,000,000 rows and we can retrieve thousands of rows from it in moments. Using partition switching we've reduced the weekly load time from 9 hours to less than an hour.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 15, 2018 at 11:10 am
Thank you, that link does help.
I was after someone who had been in a similar situation & therefore wanted to draw upon their experience, a forum seemed the best place to a start. I have searched/read stuff online too.
March 16, 2018 at 1:38 am
Ok, I understand how I need to split the top partition:
ALTER PARTITION SCHEME <PartitionScheme>
NEXT USED [PRIMARY];
GO
ALTER PARTITION FUNCTION <PartitionFunction>
SPLIT RANGE (20170199);
GO
etc.. for each month
What I need to understand is, will SQL server start moving the data around in the top partition automatically, as soon as the next partition is created?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply