July 17, 2018 at 8:21 am
We have a multi-terabyte DB that is part of an HA Group, which, due to a lot of archiving now has a large amount of free space scattered throughout.
As we pay for this storage, it would be nice to recover it, by reducing the size of tables (which are partitioned) and ultimately the database.
However, this is a 24/7 online system, so the downtime caused by index rebuilds and suchlike needs to be avoided as much as possible.
I've been playing in my sandpit, trying to use Paul Randall's suggestion of moving data by using 'drop_existing' but it isn't working as I'd wished. It's impressive for non-clustered indexes but I can't get it to work as I expect for the clustered index/primary key.
My brief description of the issue I'm having and his answer are here.
If Paul says it is possible then I'm willing to believe I'm doing something wrong but I can't fathom it.
I've attached the scripts I've used to create my tests and I would welcome any (sensible) suggestions about how to reduce the fragmentation and recover the space with the minimal disruption to users.
July 17, 2018 at 8:56 am
Just wondering how much space we are talking about... Also, what ratio is that amount of space when compared to the growth rate of the data over the next year ? Shrinking a database file to recover space can be fraught with problems in terms of creating undesirable fragmentation and potential performance issues attendant to that fragmentation, which can be insidious because you start out with none, but then suddenly develop a ton of it, just from new activity and growth.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 17, 2018 at 9:05 am
sgmunson - Tuesday, July 17, 2018 8:56 AMJust wondering how much space we are talking about... Also, what ratio is that amount of space when compared to the growth rate of the data over the next year ? Shrinking a database file to recover space can be fraught with problems in terms of creating undesirable fragmentation and potential performance issues attendant to that fragmentation, which can be insidious because you start out with none, but then suddenly develop a ton of it, just from new activity and growth.
Hi Steve,
The amount of space that can be recovered is substantial and amounts to a bucketful of money each month, as the db is hosted elsewhere. If we left it as is, it would be a couple of years before we re-used it. In addition, the backup sizes should be reduced, which has a cost also.
Aggressively shrinking isn't something we'd consider (except for TRUNCATE ONLY) but Paul's suggestion effectively reorganizes the data while moving it to a new filegroup. Something like that would be ideal but I can't get that option to work.
Thanks for your time.
July 17, 2018 at 9:38 am
BrainDonor - Tuesday, July 17, 2018 9:05 AMsgmunson - Tuesday, July 17, 2018 8:56 AMJust wondering how much space we are talking about... Also, what ratio is that amount of space when compared to the growth rate of the data over the next year ? Shrinking a database file to recover space can be fraught with problems in terms of creating undesirable fragmentation and potential performance issues attendant to that fragmentation, which can be insidious because you start out with none, but then suddenly develop a ton of it, just from new activity and growth.Hi Steve,
The amount of space that can be recovered is substantial and amounts to a bucketful of money each month, as the db is hosted elsewhere. If we left it as is, it would be a couple of years before we re-used it. In addition, the backup sizes should be reduced, which has a cost also.
Aggressively shrinking isn't something we'd consider (except for TRUNCATE ONLY) but Paul's suggestion effectively reorganizes the data while moving it to a new filegroup. Something like that would be ideal but I can't get that option to work.
Thanks for your time.
Can I ask exactly what you mean when you say "I can't get that option to work" ? I'm asking because you're initial post says "it isn't working as I'd wished." Please be more specific as to what is or is not working...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 18, 2018 at 1:10 am
sgmunson - Tuesday, July 17, 2018 9:38 AMBrainDonor - Tuesday, July 17, 2018 9:05 AMsgmunson - Tuesday, July 17, 2018 8:56 AMJust wondering how much space we are talking about... Also, what ratio is that amount of space when compared to the growth rate of the data over the next year ? Shrinking a database file to recover space can be fraught with problems in terms of creating undesirable fragmentation and potential performance issues attendant to that fragmentation, which can be insidious because you start out with none, but then suddenly develop a ton of it, just from new activity and growth.Hi Steve,
The amount of space that can be recovered is substantial and amounts to a bucketful of money each month, as the db is hosted elsewhere. If we left it as is, it would be a couple of years before we re-used it. In addition, the backup sizes should be reduced, which has a cost also.
Aggressively shrinking isn't something we'd consider (except for TRUNCATE ONLY) but Paul's suggestion effectively reorganizes the data while moving it to a new filegroup. Something like that would be ideal but I can't get that option to work.
Thanks for your time.Can I ask exactly what you mean when you say "I can't get that option to work" ? I'm asking because you're initial post says "it isn't working as I'd wished." Please be more specific as to what is or is not working...
The behaviour I'm getting is no different from what I posted in the initial question to Paul - “The new index definition does not match the constraint being enforced by the existing indexâ€, if I try CREATE INDEX with DROP_EXISTING. The only way it appears to work are if I drop the clustered index first and recreate it - not what I would prefer because I then appear to lose the primary key constraint.
The two methods I tried are in script 5 of the attached scripts, where the commented-out code is the one I would like to use but the DROP CONSTRAINT and CREATE INDEX are the only option that appears to get close to what I want.
July 19, 2018 at 2:46 pm
Wondering whether the following solves anything:ALTER TABLE [dbo].[PetDetails]
DROP CONSTRAINT [PK_Petdetails]
WITH (MOVE TO [ps_PetTypes2]([PetType]));
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 20, 2018 at 4:02 am
sgmunson - Thursday, July 19, 2018 2:46 PMWondering whether the following solves anything:ALTER TABLE [dbo].[PetDetails]
DROP CONSTRAINT [PK_Petdetails]
WITH (MOVE TO [ps_PetTypes2]([PetType]));
GO
Well that was interesting. It still removes the primary key but it was fast. I'll have a further play with that and see if it has any advantages over any other method I'm attempting.
Currently at the back end of a data-center move, so it may be a day or two.
Thanks for your time.
July 20, 2018 at 7:43 am
BrainDonor - Friday, July 20, 2018 4:02 AMsgmunson - Thursday, July 19, 2018 2:46 PMWondering whether the following solves anything:ALTER TABLE [dbo].[PetDetails]
DROP CONSTRAINT [PK_Petdetails]
WITH (MOVE TO [ps_PetTypes2]([PetType]));
GOWell that was interesting. It still removes the primary key but it was fast. I'll have a further play with that and see if it has any advantages over any other method I'm attempting.
Currently at the back end of a data-center move, so it may be a day or two.
Thanks for your time.
Thanks for the feedback. I suspected that's what would happen, but re-creating the PK might not be too bad... There you could:ALTER TABLE dbo.PetDetails
ADD CONSTRAINT PK_Petdetails PRIMARY KEY NONCLUSTERED
(
[PetDetailID] ASC,
[PetType] ASC
);
I'll look forward to seeing how you things go...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2018 at 10:53 am
sgmunson - Friday, July 20, 2018 7:43 AMThanks for the feedback. I suspected that's what would happen, but re-creating the PK might not be too bad... There you could:ALTER TABLE dbo.PetDetails
ADD CONSTRAINT PK_Petdetails PRIMARY KEY NONCLUSTERED
(
[PetDetailID] ASC,
[PetType] ASC
);I'll look forward to seeing how you things go...
Finally managed to spend a day comparing different ways of reorganising the table and it appears that rebuild with page compression and offline is the fastest, which makes sense although not what I was after.
With 2.1 million rows of data, which I fragment as best as I can:
Drop constraint with move, followed by adding the constraint - 1:30,
Rebuild with no compression and offline - 0:12
Rebuild with page compression and offline - 0:04
Rebuild with page compression and online - 0:23
The original data doesn't have page compression, so if I take that into account I recover almost 23,000 pages - considerably more than just the empty space to be clawed back - about 15,000 more pages.
Thanks for your time with this and the drop constraint with move is a new one that I wasn't aware of but it looks like it will have to be a rebuild in the small hours of the morning. The live tables have in excess of 150 million rows, so I'll have to do a bit more testing.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply