Confirm: new index on specific filegroup spreads over data files

Question

Confirm: new index on specific filegroup spreads over data files

Indianrock

SSC-Insane

Points: 20333
More actions
August 22, 2012 at 6:26 am

#393001

I'm going to test this but just wanted to confirm that if you have a file group, say FG_index with several data files on separate disk luns associated with this filegroup, a new index will "automatically" be spread over the various data files, rather than just all going to one indiscriminately. Or if one lun is low on space, it might not even receive any of this new index data?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It will be spread across the files according to the proportional fill algorithm. Not the space free on the drive that affects that, the space free in the data file.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 2

Thanks for the response. So let's say you had two disk luns of 10GB each. If an index is created which requires 1GB of disk space, the proportional fill might try to put it in FileA which has 5GB free inside the file as opposed to FileB which only has .1GB free in the file. Since that disk only has .1GB free it couldn't autogrow. I guess I'm asking if "proportional fill" takes into account disk free space and/or auto grow settings.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

As I said, it's not the space on the drive that affects proportional fill, it is the space free in the data file.

Autogrow's irrelevant in your case, there's 5GB free in the data file, the index requires 1GB, there's more than enough space the file doesn't have to grow.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

Thanks. I think my example was poor, but was trying to create a scenario where sql would attempt to put too much data in the file that had more internal free space, but less free on disk. I think the real bottom line is 1) keep an eye on your disk space, 2) in general don't be shrinking files and 3) explain to management that having a generous amount of free space is a cost of doing business. If they don't want to spend for more disk space then don't accept any new business from your customers.

🙂

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

If they don't want to spend for more disk space then don't accept any new business from your customers.
🙂

Not harsh like this, but space management involves backup space on a separate disk for your reference and tape space.

Regards
Durai Nagarajan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Indianrock (8/22/2012)
Thanks. I think my example was poor, but was trying to create a scenario where sql would attempt to put too much data in the file that had more internal free space, but less free on disk.

It may well do that. The storage engine doesn't care how much space is on disk when it's spreading out the data, just free space in the file. The point of proportional fill is that all files in the filegroup reach full at about the same time.

In your example, if the index took 6GB, both files would fill, one would fail to autogrow, one would succeed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass