FileGroup/File Configuration 1

[ZenRaven]

SQL Server 2008 R2
I have a 90 GB database running on RAID 10 with 8 spindles. I have 398 tables out of which 3 comprise 87% of all data. Here is the breakdown of percentage of total size:

Table1 Clustered 24%
Table1 NonClustered 22%
Table2 Clustered 17%
Table2 NonClustered 12%
Table3 Clustered 7%
Table3 NonClustered 5%
Other395 13%

Does anyone have any recommendations as to how to split these up into filegroups/files if at all?

I wonder, since the single file is already spread across 8 spindles on the RAID 10, if splitting them out will actually make a difference.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[mrdenny]

Probably not, but it all depends on how the data is used. You'll have to monitor and test each config to see which gives you the better performance.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[ZenRaven]

We're a small shop and this production box is the only one with an array large enough to handle these configurations so it's really not anything I can play around with. Is your "probably not" based on the fact that the file(s) are all on 1 logical drive or other factors?


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[mrdenny]

It is based on the fact that the physical file is currently laid out across all the drives in the array.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog