August 21, 2014 at 8:13 am
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: 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
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!
August 26, 2014 at 8:48 am
anyone?
August 27, 2014 at 12:25 pm
I think the focus should be database centric because filegroups are database centric. Rather than arbitrary unique names across database, have a set of standard names to use in all databases. There should be a purpose to each and the focus is not always separation of IO. For example, FG_TABLES, FG_INDEX, FG_BUSY_TABLES, FG_READ_ONLY, FG_PRE_PROCESSING_BACKUP, etc. (http://sqlmag.com/database-performance-tuning/focus-files-and-filegroups)
As to how to separate IO, the old basic is to isolate on IO types first. I like to separate OS, data, log, tempdb, and backup first. Filegroups are not needed for that. I would create partitions for each even if a single array is used. After that, it's time to start getting a handle of the IO loads of each application and the capabilities of the arrays. If the app IOP and bandwidth loads are light compared to the capabilities, then it does not make sense to work very hard on preparing for a heavy load. You could run IO tests (e.g., SQLIO) to check.
I don't have a real answer for you because I deal mostly with OLTP. However, you might find this link interesting. See page 20, for example.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply