April 19, 2017 at 7:31 am
GilaMonster - Wednesday, April 19, 2017 7:27 AMIn object explorer, expand out the database -> storage - > partition functions. right click the function -> script. Do the same with the partition schemeThat query should have returned results. Check that you ran it in the correct database and specified the table name correctly (with schema if necessary)
Maybe change to:
SELECT partition_number,
row_count
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('<name of partitioned table>') AND index_id in (0, 1)
lord, it's early here. I will run it again and put the table in. This is what happens when I work 12 hours a day and at the baseball park with the kid till late at night...
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 7:34 am
CREATE PARTITION SCHEME [psch_Inventory] AS PARTITION [pfn_Inventory] TO ([INVENTORY_201501], [INVENTORY_201502], [INVENTORY_201503], [INVENTORY_201504], [INVENTORY_201505], [INVENTORY_201506], [INVENTORY_201507], [INVENTORY_201508], [INVENTORY_201509], [INVENTORY_201510], [INVENTORY_201511], [INVENTORY_201512], [INVENTORY_201601], [INVENTORY_201602], [INVENTORY_201603], [INVENTORY_201604], [INVENTORY_DEFAULT])
GO
CREATE
PARTITION FUNCTION [pfn_Inventory](date) AS RANGE LEFT FOR VALUES (N'2015-01-31T00:00:00.000', N'2015-02-28T00:00:00.000', N'2015-03-31T00:00:00.000', N'2015-04-30T00:00:00.000', N'2015-05-31T00:00:00.000', N'2015-06-30T00:00:00.000', N'2015-07-31T00:00:00.000', N'2015-08-31T00:00:00.000', N'2015-09-30T00:00:00.000', N'2015-10-31T00:00:00.000', N'2015-11-30T00:00:00.000', N'2015-12-31T00:00:00.000', N'2016-01-31T00:00:00.000', N'2016-02-29T00:00:00.000', N'2016-03-31T00:00:00.000', N'2016-04-30T00:00:00.000')
GO
partition_number row_count
1 46463225
2 45534244
3 51125676
4 53558465
5 55806914
6 67851260
7 90703858
8 99700277
9 98323521
10 113011553
11 109755088
12 120139585
13 115392628
14 109597150
15 117488416
16 183054091
17 2471627929
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 7:38 am
I haven't added the filegroup or ndf file to the database yet, that's why it's not showing in the listing. I restored it from backup late last night for a developer.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 7:56 am
??? You posted the alter function and alter schema commands in the first post. They're correct, but the split will run for ages, because it's moving a whole pile of data. And by the looks of things, you've got *multiple* splits that you need to do, since the initial partitions are monthly, but all data since 2016-04-30T00:00:00.000 is sitting in the most recent partition.
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
April 19, 2017 at 8:03 am
yes, I realized my stmt after I put it out here. My apologies. then steps are correct. Add new file and filegroup, run alter schema and alter function with date range and let it run but it will take a few days because it has 2+ trillion rows.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 8:18 am
No.
I mean, you can do that, but you're looking at a couple of days for each split, and to get that table back into the correct partitions, you need to do that 12 times!
Most efficient would probably to create the filegroups, create tables as I suggested above, move the data into the correct tables for the time frame (including data that should be in the 2016-04-30 partition, then empty that partition, split the function 12 times and then switch in the tables created earlier
And it's 2.4 billion rows in the most recent partition, not a couple trillion.
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
April 19, 2017 at 8:45 am
ok, thanks.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 9:13 am
GilaMonster - Tuesday, April 18, 2017 1:45 PMIf the split takes time then there's data that needs to be moved to the new partition. It's generally recommended to split so that the new partition is empty. If you can't do that, then the split is going to take time, it's essentially deleting and inserting the data that needs to be moved to the new partition.What does this return?
SELECT COUNT(*) FROM <partitioned table name> WHERE <partition column> > '2016-05-31 00:00:00.000'If there are lots of rows that have to move to the new partition, perhaps create a new table in the destination filegroup, identical in structure to your partitioned table, insert into that new table the rows you want in the new partition, delete them from the monster and then split the partition (instant this time since there's no data to move) and switch in the table that you created. But that's going to take time.
Got it Gail. I created new files and filgroups, created tables for each month from 05-12 2016, switched out 201604 file group to new table, running inserts into tables with said filegroups, deleted from crazy large table, switch partitions back to original table. Do I leave a blank partition for 012017 so I don't have to go through this again and run it properly?
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 9:22 am
GilaMonster - Wednesday, April 19, 2017 8:18 AMNo.I mean, you can do that, but you're looking at a couple of days for each split, and to get that table back into the correct partitions, you need to do that 12 times!
Most efficient would probably to create the filegroups, create tables as I suggested above, move the data into the correct tables for the time frame (including data that should be in the 2016-04-30 partition, then empty that partition, split the function 12 times and then switch in the tables created earlierAnd it's 2.4 billion rows in the most recent partition, not a couple trillion.
wow, I really need some sleep and coffee. Thanks for correcting me on the count.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 12:24 pm
lkennedy76 - Wednesday, April 19, 2017 9:13 AMGot it Gail. I created new files and filgroups, created tables for each month from 05-12 2016, switched out 201604 file group to new table, running inserts into tables with said filegroups, deleted from crazy large table, switch partitions back to original table. Do I leave a blank partition for 012017 so I don't have to go through this again and run it properly?
It's late here. Let me revisit this in the morning and get a proper list of steps.
You won't need to delete from crazy large table. By switching out that huge partition, once the rows have been put where they should be, you can drop the table that you switched the data to.
What's the current max value in the table? April 2017?
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
April 19, 2017 at 12:43 pm
yes April 2017.
Here's what I have done so far;
Create new ndf files and filegroups for 05 2016 to 12 2016.
Created tables for 05 2016 to 12 2016, each table is on its own filegroup for whatever month it belongs. for example I created Inventory201605 on inv_201605, Inventory201606 on inv_201606, ect...
turned Compression on table and switched out 201604 into it's own table. Inventory201604 on inv_201604
inserting data in inventory201605 for date range 05/01/2016 and 05/31/2016.
thank you for all your help.
MCSE SQL Server 2012\2014\2016
April 19, 2017 at 12:46 pm
Cool. Will reply in detail over coffee tomorrow morning (~12 hours from now).
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
April 21, 2017 at 9:56 am
Apologies, the day got away from me.
Ok, so everything less than 2016-03-31 is in the correct partition.
Some of this I know you've already done, it's here for completeness
Switch the most recent partition out to an empty table. Now there is no data > 2016-03-31 in the table.
Create one filegroup for each month after 2016-04 (that one's already there, and its the one with the monster table in it)
Thus, you should at the end have the following new filegroups:
INVENTORY_201604
INVENTORY_201605
INVENTORY_201606
INVENTORY_201607
INVENTORY_201608
INVENTORY_201609
INVENTORY_201610
INVENTORY_201611
INVENTORY_201612
INVENTORY_201701
INVENTORY_201702
INVENTORY_201703
INVENTORY_201704
On each of those partitions, create a table that matches the huge table *exactly* in definition (same indexes, same columns, same data types. On each, put a constraint that restricts the rows to the range that will be allowed by the partition.
Since you're using RANGE LEFT, for the table on the INVENTORY_201604 partition that would be > 2016-03-31 AND <= 2016-04-30.
Make sure you get these constraints correct, or you'll be unable to switch the tables into the partitioned table.
Now insert into each of these tables the rows that belong in them (that's why the constraint is put there before the insert). The insert is from the table that you switched out of the partitioned table in step 1.
If all has gone correctly until this point, you have one huge table with all the rows that were initially in the huge partition, and 13 tables on 13 filegroups each with the data that belongs in that partition.
Now, run the ALTER statements to specify the next used filegroup and to spit the partition function. These should run instantly, as there's now no data that needs to move.
Once the splits have finished, you should be able to switch each of those smaller tables into the huge partitioned table, and then drop the table you created (which will now be empty)
Once you're sure everything's correct, drop the table that you switched out in step 1 (the one with 2.4 billion rows in it)
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
April 24, 2017 at 8:43 am
Gail,
I am starting this process today, I will post my step and results when I am done. I really hope this will help someone else in my shoes one day...
MCSE SQL Server 2012\2014\2016
April 25, 2017 at 8:28 am
Once you're sure everything's correct, drop the table that you switched out in step 1 (the one with 2.4 billion rows in it)
Step one is the inventory file for 04/01/2016 - 04/30/2016, the file that has the 2.4 billion is the inventory_default file group, do I switch this file group out as well while I am inserting\sliding information in and out to it's own table and then drop it once I am finished? Will I switch the inventory_deafault file group back to the inventory table?
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply