April 26, 2008 at 7:57 am
Due to SQL's proportional fill algorithm I'd like to have the amount of data in my data files as close to equal as possible.
File sizes before:
dat 482,895 MB 0% free
2dat 436,927 MB 3% free
I made the max size of each file 600,000 MB and added a third file 3dat also 600,000 MB. I rebuilt all the clustered indexes (and nonclustered for good measure) and unfortunately the re-balancing wasn't quite right.
File sizes after:
dat 464,761 MB 77% free
2dat 443,234 MB 73% free
3dat 309,568 MB 51% free
I only have a handful of heap tables that take up <100MB total so they're not the issue. I did do an ONLINE index rebuild. I'm not sure if an offline rebuild would have been better. I will not be able to try and offline for a few weeks though as it's time consuming and I have other tasks I need to run on this test server now.
I did a FULLSCAN rebuild on any column statistics not updated by the index rebuild but that didn't help either.
April 28, 2008 at 1:45 am
It depend on the objects and the indexes on the file too. SQL Server can't split table on the filegroup.
Let SQL Server handle the case. Or else as per the size of the table do it explisitely.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 28, 2008 at 1:56 am
This is because of the fact that your heap is small relative to the indexes and that SQL Server tries to keep the index pools closely knit, and in most of the cases, it favors a stack. Unless you use partitions for tables, as rightly said by others, SS tries to keep the whole table together.
Also, did you try de-fragmenting. It might also result in similar conditions.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply