November 15, 2021 at 11:59 am
Hello, I have a DB with partitions
create partition function PF_IdEmpresas (smallint) AS range left for values (1,2,3,4,5,6,7,8,9,10,11,12,13)
create partition scheme PS_BASE AS PARTITION PF_IdEmpresas to
(BASE01,BASE02,BASE03,BASE04,BASE05,BASE06,BASE07,
BASE08,BASE09,BASE10,BASE11,BASE12,BASE13,BASE14)
In the filegroup BASE14 there are data with the values 14, 15 and 16 and I want to add a new filegroup, BASE17, for the data with value 17.
I execute
ALTER DATABASE [DMS96283] ADD FILEGROUP [BASE17]
Work fine!
ALTER DATABASE [DMS96283] ADD FILE ( NAME = N'BASE17', FILENAME = N'C:\spiga\volume7\MSSQL13.SPIGAPLUS\MSSQL\DATA\BASE17.ndf' , SIZE = 8192MB , FILEGROWTH = 1024MB ) TO FILEGROUP [BASE17]
Work fine!
ALTER PARTITION SCHEME PS_BASE NEXT USED BASE17
Work fine!
But when I execute
alter partition function PF_IdEmpresas() split range (16)
The time elapsed is very great, I don't understand it, the DB there aren't data with value 17 although the DB has 300GB
Is it normal?
Thanks for all.
November 16, 2021 at 4:08 am
When you split a partition with data in it, the engine must scan the partition's rows to determine which rows will go into the new partition. Even in cases such as yours, where no rows exist in the current partition that must be moved to the new partition, that entire partition will still be scanned. If multiple tables use the same partition function, then this will be performed in each of those tables.
In the future, split only empty partitions. Create at least one additional partition than the one you just created (SPLIT RANGE(17)) before you load any rows that would go into the partition for (16). Then split the empty (17) partition before you begin using it.
Eddie Wuerch
MCM: SQL
November 22, 2021 at 1:06 pm
Thank you, I didn't know it but I thought it, the partition with values 14, 15 and 16 is the bigger.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply