Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

#Files and FILEGROUPS Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 3:49 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1427883
Posted Thursday, March 7, 2013 9:36 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 6, 2014 11:07 AM
Points: 11, Visits: 47
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
Post #1428065
Posted Thursday, March 7, 2013 9:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 42,454, Visits: 35,508
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 2008, MVP
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

Post #1428077
Posted Thursday, March 7, 2013 9:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1428091
Posted Thursday, March 7, 2013 10:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 42,454, Visits: 35,508
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 2008, MVP
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

Post #1428116
Posted Thursday, March 7, 2013 10:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1428128
Posted Thursday, March 7, 2013 11:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 42,454, Visits: 35,508
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 2008, MVP
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

Post #1428154
Posted Thursday, March 7, 2013 3:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1428287
Posted Friday, March 8, 2013 2:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 42,454, Visits: 35,508
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 2008, MVP
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

Post #1428465
Posted Friday, March 8, 2013 3:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
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...
Post #1428486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse