January 8, 2015 at 3:21 am
I recently ran a db shrink on a large database to recover a lot of unused space within the db and release back to the SAN. I shrank each file separately, one at a time, from 200GB to 100GB each. In the end, we have some irregular file sizes (see below). Why do these files end up with different sizes? Is it necessary to size them all the same? (I think yes, so that the proportional fill will distribute the data evenly)
DBFILE1 100000
DBFILE2 100000
DBFILE3 100000
DBFILE4 102731
DBFILE5 103316
DBFILE6 102391
DBFILE7 103182
DBFILE8 103555
Secondly, after the shrink, we seem to have more free space in the database than originally planned. Using the free space estimates from the SQL disk usage report, shrinking down to 100GB should have resulted in about 10% freespace. Running the report again after the shrink, I have about 26% free. (My guess is that the report does not account for unused space in the pages and that the shrink operation "packed" the pages full. But we ran a full reindex afterwards, so would it not "unpack" the pages and redistribute the data evenly?)
thanks for any info!
January 8, 2015 at 4:18 am
JarJar (1/8/2015)
(My guess is that the report does not account for unused space in the pages and that the shrink operation "packed" the pages full.
Shrink does not do that. It shuffles pages around, it does not change the contents of the pages.
But we ran a full reindex afterwards, so would it not "unpack" the pages and redistribute the data evenly?)
No, exactly the opposite. Reindex fills the pages as full as it can, depending on the setting of the fill factor for that index.
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
January 8, 2015 at 6:30 am
thanks! that explains the freespace discrepancy.
any ideas about the irregular dbfile sizes?
January 9, 2015 at 4:14 am
JarJar (1/8/2015)
I recently ran a db shrink on a large database to recover a lot of unused space within the db and release back to the SAN. I shrank each file separately, one at a time, from 200GB to 100GB each. In the end, we have some irregular file sizes (see below). Why do these files end up with different sizes? Is it necessary to size them all the same? (I think yes, so that the proportional fill will distribute the data evenly)DBFILE1 100000
DBFILE2 100000
DBFILE3 100000
DBFILE4 102731
DBFILE5 103316
DBFILE6 102391
DBFILE7 103182
DBFILE8 103555
Secondly, after the shrink, we seem to have more free space in the database than originally planned. Using the free space estimates from the SQL disk usage report, shrinking down to 100GB should have resulted in about 10% freespace. Running the report again after the shrink, I have about 26% free. (My guess is that the report does not account for unused space in the pages and that the shrink operation "packed" the pages full. But we ran a full reindex afterwards, so would it not "unpack" the pages and redistribute the data evenly?)
thanks for any info!
Are these files actually part of the same filegroup?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 14, 2015 at 3:14 am
sorry for the late reply. it was my turn to have the flu. :sick:
yes, all these files are part of the PRIMARY filegroup.
January 14, 2015 at 3:17 am
It's TempDB where the files MUST be the same size. For user databases isn't not particuarly critical.
It is a bit odd to see 8 files in the primary filegroup though. With user databases, it's far more common to see multiple files in multiple filegroups, because that gives far more control over what goes into what file and allows for partial database restore.
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
January 14, 2015 at 3:45 am
normally yes, i personally wouldn't do it like this. but this is SAP and they have their own set of best practices that are obliged to follow. each datafile is on it's own dedicated LUN. it's not a big deal like it is, but i am just trying to understand what might have happened to have such irregular sizes following a shrink that specifically targeted 100GB.
it's my understanding that data is striped across the files of a filegroup using a proportional fill algorithm, so if one file is larger than the others it gets proportionally more data. if they are not sized the same, then the larger file(s) will continue to grow larger relative to the others. in other words, the data striping is no longer even across all files.
January 14, 2015 at 4:11 am
JarJar (1/14/2015)
it's my understanding that data is striped across the files of a filegroup using a proportional fill algorithm, so if one file is larger than the others it gets proportionally more data.
No. Proportional fill is based on the % of free space in the file, not the size of the file. If a file has a greater % of free space, it gets more data. The idea is that all files fill at the same time.
Shrink seldom hits the exact target, it's a goal size, not the exact size to end at.
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
January 14, 2015 at 5:48 am
GilaMonster (1/14/2015)
JarJar (1/14/2015)
it's my understanding that data is striped across the files of a filegroup using a proportional fill algorithm, so if one file is larger than the others it gets proportionally more data.No. Proportional fill is based on the % of free space in the file, not the size of the file. If a file has a greater % of free space, it gets more data. The idea is that all files fill at the same time.
Shrink seldom hits the exact target, it's a goal size, not the exact size to end at.
thanks for the clarification. so all is normal behaviour it seems. funny that when we ran this in QA, all the filesizes shrank to exactly what was specified. in fact, i don't think i've ever seen a shrink not hit it's target. but, of course, this occurs on the largest PROD database we have. :crazy:
January 14, 2015 at 10:40 am
GilaMonster (1/14/2015)
For user databases isn't not particuarly critical.
I would say it's just as critical, you still have a proportional fill rate and files of differing size\freespace will just cause the algorithm to shift page allocations to account for one file having more freespace than another
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 14, 2015 at 1:07 pm
It's critical in TempDB to spread out the allocation of tables across the files and hence reduce the contention on the first set of allocation pages in the file. If the TempDB files are of different sizes then it's likely that one file will see more allocations than the others, defeating the point of the multiple files in the first place.
User databases aren't likely to see the kind of allocations that TempDB does. It doesn't make that much of a difference with a user database if, when 6 extents worth of data is inserted, 4 files each get one extent and one file gets two.
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
January 15, 2015 at 1:25 am
I get that totally thats why i said page allocations shift to account for more space in one file than another.
In a previous database i came across with 52 files in the primary filegroup (all wildly different sizes), we had very uneven io spikes across the LUNs with one being hit harder than the rest. Not the most efficient use of the database and storage engine resources 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 15, 2015 at 6:06 pm
I think I read it right - You did a shrink database and then a re-index.
I found doing in doing that, the shrink would compact the database pages putting them in any order and the indexes were badly fragmented. The reindex would put them in order, but it needs continuous pages to do that.
Guess what - you got rid of them all with the shrink, so they go at the end of the file leaving lots of "holes" or free space in the database and you are back to square one..........
To remove space and keep the database running well, I found it better to use a reorganise / reindex routine based on fragmentation and forget about the shrink.
January 16, 2015 at 12:54 am
phil.doensen (1/15/2015)
I found doing in doing that, the shrink would compress pages putting them in any order and the indexes were badly fragmented. The reindex would put them in order, but it needs continuous pages to do that.
Guess what - you got rid of them all with the shrink, so they go at the end of the file leaving lots of "holes" or free space in the database and you are back to square one..........
To remove space and keep the database running well, I found it better to use a reorganise / reindex routine based on fragmentation and forget about the shrink.
[/quote
i'm not sure i understand this. we needed to shrink to reclaim free space after enabling page compression. too much empty space that could be used for other things. not a regular operation. i would never shrink a db otherwise.
January 16, 2015 at 8:17 pm
All I am saying is that if you shrink a database and reindex you may end up with just as much free space as you started with. That was my experience.
After the reindex, the freespace is not wasted. It is available for ANY table insert that happens in the future.
All that will happen is that the freespace will decrease and your database files will not expand for a while.
I saw free space in my databases something like your 26%, and as I work a lot with SQL Express and have a 10GB limit this was a concern to me. It meant I had an effective limit of about 7GB not 10GB.
I then changed my nightly indexing from a shrink database / full reindex (sorry I inherited this one!) to a reorganize/reindex based on fragmentation and saw the 26% go down to something more like 5%.
It did take time though - as more data was added to the database.
I am not sure if it can be done instantaneously.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply