File and Filegroup fill pattern -- SQL server uses proportional fill algorithm to keep the free space within a filegroup evenly distributed across all the files in the filegroup which are located at different drives.
It uses the round-robin mechanism to for filling in files, for example first it fills file1 and file2 and so on then it come back to file1 again, Along with that it uses proportional fill algorithm which means if file1 has occupied with 1 GB space and file2 is filled with 500 Mb space while filling the files it allocates 1 extent to file1 and 2 extents to file2.
If a database has two files in F: drive which is almost full, due to various limitations we may not increase the storage for F:\drive
The solution for this is we will add a new data file in G: drive, but due to proportionate fill algorithm the newly added ndf file will get more allocations when compared to other two files hence this file becomes IO hot spot.
We can rebuild the index to prevent a write hot spot on new file after adding a new file in the file group. This index step will proportional fill all files in the filegroup, if the file is added to new file group then we need to move the table to that file group, use create index with drop_existing=on by specifying filegroup name.
Note : This wont work if the table has LOB data.
No comments: