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 12»»

Tables on seperate filegroups. Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 1:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 AM
Points: 9, Visits: 168
Hi All
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own.. I would like to know if this is sensible and the pros and cons..

Regards
Senthil
Post #1597603
Posted Wednesday, July 30, 2014 7:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:03 AM
Points: 457, Visits: 1,463
It will make sense if the files belonging to these FGs are placed on separate physical drives and you have a lot of queries between fact tables. In this case you will alleviate large I/O readings by employing parallelism.

However, if most of your queries select data within same fact tables, the better solution would be to partition them, again spreading partition files across different physical drives.
Post #1597729
Posted Wednesday, July 30, 2014 8:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 17,807, Visits: 15,728
I am in favor of separating the large fact tables into their own filegroups - based on better data and analysis.

I don't necessarily think that the fact tables should each have their own filegroup. But if you can split out the fact tables into some configuration of new filegroups - it would prove beneficial.

Where is the benefit of doing that?

With data this size, it provides a more robust opportunity for recovery. Sure you can see some performance gain (whether on same disks or not). But the big benefit is being able to take a filegroup backup and restore a filegroup backup (when/if necessary).
Maybe somebody wants/needs a 2TB group of tables restored to a dev or qa environment. With FG backups, you can achieve that without having to concoct a process to move the tables via bcp or ssis, or worse via backup/restore of the entire 50TB database.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1597747
Posted Wednesday, July 30, 2014 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
SQLRNNR (7/30/2014)
I am in favor of separating the large fact tables into their own filegroups - based on better data and analysis.

I don't necessarily think that the fact tables should each have their own filegroup. But if you can split out the fact tables into some configuration of new filegroups - it would prove beneficial.

Where is the benefit of doing that?

With data this size, it provides a more robust opportunity for recovery. Sure you can see some performance gain (whether on same disks or not). But the big benefit is being able to take a filegroup backup and restore a filegroup backup (when/if necessary).
Maybe somebody wants/needs a 2TB group of tables restored to a dev or qa environment. With FG backups, you can achieve that without having to concoct a process to move the tables via bcp or ssis, or worse via backup/restore of the entire 50TB database.


+1000

For anyone interested in such a process, it's called a "Piecemeal" restore in Books Online and such restores can frequently be done online with little or no interruption in service.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1597770
Posted Friday, August 8, 2014 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 AM
Points: 9, Visits: 168
Thanks Jason.
Your suggestion is one of the main benefit that we want to achieve. The ability to leave tables that we do not do development in the next few months and that can be excluded in this way on the backups to the development environment. But the downside is that the DBA informed me that we are using a SIMPLE recovery database and it is not possible to have filegroup based backup when this setting is made.
Post #1601085
Posted Friday, August 8, 2014 3:29 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 @ 4:45 AM
Points: 40,173, Visits: 36,567
senthil kumar d (8/8/2014)
But the downside is that the DBA informed me that we are using a SIMPLE recovery database and it is not possible to have filegroup based backup when this setting is made.


He's correct. Well, it's possible to take the backups afaik, but they're useless for restoring the DB.



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 #1601088
Posted Friday, August 8, 2014 4:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 16, 2014 1:09 PM
Points: 411, Visits: 1,309
senthil kumar d (7/30/2014)
Hi All
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own.. I would like to know if this is sensible and the pros and cons..

Regards
Senthil


Adding to what has been said and giving the case of how common SANs are now...

if all your mdf files reside on the same LUN (regardless of how many drive letters you may see in the Os), you won't get any I/O improvement anyway, even if you use FGs. This is because in most cases, the whole LUN that contains all the logical drives is already a single tier with same RAID.

By the way, why are you using SIMPLE recovery model? Is your company aware of possible data loss in case of a system crash/restore if you use SIMPLE?
Post #1601352
Posted Wednesday, August 13, 2014 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 AM
Points: 9, Visits: 168
Sorry for the late response. I had asked for a discussion with my DBA on why they are doing SIMPLE recovery instead of Full recovery..

But we have split our filegroups so that they are spread across 16 different drives.. So it should help distribute the IOs..
Post #1602625
Posted Friday, August 15, 2014 6:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
senthil kumar d (8/13/2014)
Sorry for the late response. I had asked for a discussion with my DBA on why they are doing SIMPLE recovery instead of Full recovery..

But we have split our filegroups so that they are spread across 16 different drives.. So it should help distribute the IOs..


If it's on a SAN, you have to ask the question... "Are they really 16 separate physical drives"?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1603942
Posted Monday, August 18, 2014 1:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2014 4:23 AM
Points: 9, Visits: 168
Hi Jeff
Yes. There are 16 seperate physical drives on which the files are distributed. And the reason for simple recovery model is that the transaction logs are really huge and they do not want to have a back up of all these huge logs and hence did not want to have Full recovery model..
Post #1604285
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse