Data partitioning and filegroups

Question

Data partitioning and filegroups

PiMané

SSChampion

Points: 13965
More actions
September 7, 2012 at 8:36 am

#267370

Hi,
It's a good practice to split the data and indexes into separate filegroups so the hardware can do multiple reads at the same time. Also partitioning a table with historical data can be a good practice...
But if the hardware is weak, say a "regular" PC with a RAID1 with SATA disks, can't this a bad practice since the disks aren't very fast and don't support multiple reads?
Are SSD disks good for log files? They are very fast and every operation in the database writes to the log file...
Thanks,
Pedro

If you need to work better, try working less...

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

PiMané (9/7/2012)
It's a good practice to split the data and indexes into separate filegroups so the hardware can do multiple reads at the same time. Also partitioning a table with historical data can be a good practice...

Maybe, but it's seldom as simple as that. Depends on the hardware, the access patterns, the current bottleneck the DB is experiencing, etc.

Are SSD disks good for log files? They are very fast and every operation in the database writes to the log file...

Wouldn't be my first choice for SSDs. They help random reads more than writes, writes are often absorbed by cache.

See which data files have the highest latency and consider moving them to SSD.

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

sql-lover SSCoach Points: 18530 More actions · Answer 2

PiMané (9/7/2012)
Hi,
It's a good practice to split the data and indexes into separate filegroups so the hardware can do multiple reads at the same time. Also partitioning a table with historical data can be a good practice...
But if the hardware is weak, say a "regular" PC with a RAID1 with SATA disks, can't this a bad practice since the disks aren't very fast and don't support multiple reads?
Are SSD disks good for log files? They are very fast and every operation in the database writes to the log file...
Thanks,
Pedro

My 2 cents.

A few years ago, a Microsoft support engineer explained to me (previous job) that even though the file groups are on same disk, we may get a little bit of improvement, do not remember the reason, if we create mutiple file-groups and use several data files. Of course, good RAID and hardware is important, but that's only when no other choice is available.

But if all logic disks or mount points are mapped to same controller or old RAID, splitting the files on separate file groups won't give you too much gain, because everything is still running on top of same physical RAID.

I would focus, like Gila Monster explained, on the database with high latency. On my personal experience, if you are able to tune up long run queries on that database, or reduce fragmentation on that same database, it will benefit other databases too, as everything is on "same basket".