Filegroups and log files

Question

Filegroups and log files

huscyp

SSC Enthusiast

Points: 154
More actions
January 23, 2009 at 2:04 pm

#383798

Hi,
Well i want to clear my doubts for the below two things:
1.Why do we use the filegroups.and what are its benefits.
2.If we are installing a new SQL server with e.g. 2 TB of Database , how should the files be placed.If we have lets say 5 disks.where to place the transaction log and where to place the filegroup and why?
H.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

We use filegroups to - ideally - split the database I/O among several independent disks (spindles) for better performance. Disk I/O is the largest bottleneck in terms of database performance.

Access of the data file(s) is random, whereas access of the transaction logs is sequential, so having both on the same disk subsystem causes a lot of contention, as the disk head tries to respond to the I/O requests.

This is why you want to place your data and logs on separate spindles.

Also to put on separate disks - if you have them:

- tempdb data file(s)

- non-clustered indexes (create separate filegroup for this, say IDX, and move all your non-cl idxs there)

- backups (txn-log, full backups)

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

huscyp SSC Enthusiast Points: 154 More actions · Answer 2

thanks.It was clear.

SO lets take an example , where our database will be 2TB very soon and there are some tables which will be very big.how we should divide or allocate the filegroups and indexes etc.

We have 10 seperate disks.

1 disk - tempdb

1 disk - non cluster index

1 disk - database FG

1 disk - large table

1 disk - Tlog file

can we divide more in detail ?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

huscyp (1/29/2009)
thanks.It was clear.
SO lets take an example , where our database will be 2TB very soon and there are some tables which will be very big.how we should divide or allocate the filegroups and indexes etc.
We have 10 seperate disks.
1 disk - tempdb
1 disk - non cluster index
1 disk - database FG
1 disk - large table
1 disk - Tlog file
can we divide more in detail ?

Do you have any tables that store data changes for auditing through DML triggers? If yes, you could use a separate filegroup for those tables and put on a separate disk.

Create several tempdb data files of the exact same size - as many files as there are cpu cores - and place each file in a separate disk (if you have any disks to spare).

Create other filegroups according to mode of access in your application: for example, put read-only tables or tables updated only, say, once a day, in a separate filegroup/disk.

The goal is to spread I/O load across as many "spindles" as possible.

The possibilities are endless.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

huscyp SSC Enthusiast Points: 154 More actions · Answer 4

thanks a million.

Now its more clear.

I think i need to study more about filegroups / disks , in order to understand better sql database design.

thanks

H.