#Files and FILEGROUPS

Question

#Files and FILEGROUPS

PiMané

SSChampion

Points: 13965
More actions
March 7, 2013 at 3:49 am

#394328

Hi,
In an 8 CORE machine with 3 RAID10 (data files, log files and tempdb) what's the appropriate number of files or filegroups for a database with lots of users and IO? I saw a SQLPass presentation where it said 1 file for 4 CORES..
If a database has 2 files and since the files are on the same RAID set is it worth creating also 2 FILEGROUPS (one for each data file) or the porpoise of FILEGROUPS is to put "sensible" data on one and not so sensible data on another so the 1st one has backups more frequently?
Thanks,
Pedro

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

Viewing 10 posts - 1 through 9 (of 9 total)

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

andrecesarr SSC Veteran Points: 207 More actions · Answer 1

Hi!

In your case, to the users databases i'll try to start with just one data file and try to start and maintain just one file for the transaction log, for the system database tempdb, you could use 4 database files and one file for the transaction log 🙂

Bye!

André CR

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

The one file/ x cores is for TempDB, not for user databases.

Unless you have reason to split a user database for recoverability or IO issues, you probably don't want to split it.

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

PiMané SSChampion Points: 13965 More actions · Answer 3

GilaMonster (3/7/2013)
The one file/ x cores is for TempDB, not for user databases.
Unless you have reason to split a user database for recoverability or IO issues, you probably don't want to split it.

Thanks,

If there are IO issues should the new ndf file be on the same RAID or a different, probably new one (since the IO is on the RAID adding the new file to the existing RAID won't make a difference.. or will it?!)?

And are my thoughts on FILEGROUPS correct?

They are useful to "split" data for backup/restore issues?

I can create an INDEX FILEGROUP to store the index and not make a backup on the index data and if I need to restore the database I'll just recreate the indexes since I have the DDL.

Thanks,

Pedro

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

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

PiMané (3/7/2013)
If there are IO issues should the new ndf file be on the same RAID or a different, probably new one (since the IO is on the RAID adding the new file to the existing RAID won't make a difference.. or will it?!)?

If a drive is under IO load then splitting a database file into two and leaving the second on the same drive results in exactly the same IO load to the disk.

I can create an INDEX FILEGROUP to store the index and not make a backup on the index data and if I need to restore the database I'll just recreate the indexes since I have the DDL.

Really? Have you tried that?

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

PiMané SSChampion Points: 13965 More actions · Answer 5

GilaMonster (3/7/2013)
Really? Have you tried that?

Not with indexes... just some more "sensible" data (accounts, orders, ...) and "fixed" data (like countries, regions, currencies, ...).

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

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

PiMané (3/7/2013)
GilaMonster (3/7/2013)
Really? Have you tried that?
Not with indexes... just some more "sensible" data (accounts, orders, ...) and "fixed" data (like countries, regions, currencies, ...).

You might want to try that 'don't back up the indexes' idea before you implement that in production. Don't want an unpleasant surprise...

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

PiMané SSChampion Points: 13965 More actions · Answer 7

So having a FILEGROUP to store the indexes and another for the data isn't an advantage?

It just useful for "organizing" the database?

But FILEGROUP for different types of data is useful as long as the indexes are in the same FILEGROUP then?

If the number of files for tempdb is related with the number of CORES why doesn't the rule apply for database files?

Is it because tempdb has "load" coming from "all directions" (all databases, DMVs, ...), hence having more IO?

Thanks,

Pedro

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

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

PiMané (3/7/2013)
If the number of files for tempdb is related with the number of CORES why doesn't the rule apply for database files?

Because you're not continually (as in many times a minute) creating and dropping tables in a user database. At least I hope you're not.

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

PiMané SSChampion Points: 13965 More actions · Answer 9

GilaMonster (3/8/2013)
Because you're not continually (as in many times a minute) creating and dropping tables in a user database. At least I hope you're not.

🙂 LOL..

right..

Thanks,

Pedro

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