OK, so I have:

  • 500 GB DW
  • 5 GB in smaller DBs
  • 220 GB TempDB
  • 350 GB in Log files.

My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.

I have several IO locations:

  • 540 GB in-server HDD 15k RAID10
  • 1 TB HDD 15k RAID10 on SAN (separete controller)
  • 2 TB HDD 15k RAID10 on SAN (same controlller as below)
  • 800GB SSD RAID10 on SAN (same controller as above)

Data warehouse has 2 fact tables that are absolutely crucial and quite large.

Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:

  • FILEGROUP1: for 1st crucial Fact Table
  • FILEGROUP2: for 2nd crucial Fact Table
  • FILEGROUP3: for tempDB
  • FILEGROUP4: for dimensions data
  • FILEGROUP5: for the rest of facts data
  • FILEGROUP6: for dimensions indexes
  • FILEGROUP7: for the rest of facts indexes
  • FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
  • FILEGROUP9: for the rest of log files
  • FILEGROUP10: for source tables
  • FILEGROUP11: others

How should I organize them across available drives? I was thinking about sth like:

  • 800 GB SSD: FILEGROUPS 1-3
  • 2 TB RAID10: FILEGROUPS 5+7+8
  • 1 TB RAID10: FILEGROUPS 4+6+10+11
  • 540 GB in-server: FILEGROUP 9

I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.

Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where im going for 4.

Any thoughts? Thx in advance!

| improve this question | | | | |
  • Multiple filegroups per drive are not pointless. By splitting out tables and indexes out to filegroups you can control fragmentation. Consider having one filegroup per meaningfully big index. That way sequentially inserted indexes never fragment. – usr Aug 26 '14 at 14:58
  • What is the structure of your large fact tables? Consider using partitioning and splitting tables across multiple filegroups based on usage of certain partitions. – Stoleg Aug 26 '14 at 16:19
  • Please add I/O tests of each LUN you have. Do a test of both sequential and random I/O on the drive... Until you know the speed and topology of each LUN, there is no point in doing the file layout design – Thomas Kejser Aug 27 '14 at 2:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy