October 5, 2004 at 4:45 am
Hi,
Can anyone advise the best practice for using filegroups and multiple files. I have a server with
Raid 1 (2 discs) drive for holding the OS and SQLServer binaries
Raid 0 (2 discs) drive for holding TempDB
Raid 1+0 (4 discs) drive for holding Transaction Logs and OS page file
Raid 1+0 (10 discs) for holding the Database
I have a datawarehouse which supports 65 million rows. I was thinking of using seperate file groups for holding the tables and indexes.
Each of the filegroups will support multiple files. (How do you determine the optimum number?)
Although all files are going to be storted on the one raid 10 disc subsystem I have read SQLServer will issue multiple threads (one for each file) when querying the database.
Can anyone advise
Thanks
Dal
October 5, 2004 at 7:33 am
I believe that SQL Server will use separate threads for each file, but in most warehouses, a few files get most of the activity, so it's not likely that you'll see a great benefit and the same spindles will be turning for the different files. In a file server, it's no big deal, but with the read aheads and if you've sized well, this can cause minor contention for the heads.
Multiple file groups are a pain. And they complicated backup and recovery. If you have a large warehouse that you CAN backup in stages, some read only or static data, then they can help there. I might take a slightly different tact with your setup and use perhaps 6 and 4 disks from the 10 and do a couple of files on each. That way you don't have heads working against each other. Also I believe NetIQ did a study and found 7-8 disks to tbe the optimum for Raid 5. If you need space, then this might be an option there.
Lastly, I always get nervous for RAID 0. If one of those disks fails, your DB stops. I mean your DW DB. I'm not sure that the extra speed of RAID 0 is worth it. Maybe take another disk and RAID 5 or add 2 more for a RAID 10 scenario.
October 5, 2004 at 8:28 am
Thanks for the information Steve.
Unfortunately the disc controller on the server only offers 4 channels and physically I only have 2 channels available in the server over 4 drives and 2 channels available over the 14 discs in the external enclosure.
I might take your advice and mirror the pair of drives supporting the TempDB rather than striping the data over two.
Backup is not an issue as we have a large enough window over night to backup using TSM. I was only interested in Filegroups and using multiple files only from a performance perspective.
On another note would you know whether switching either the filegroup or the database to read only would give a performance benefit?
Thanks
Dal
October 5, 2004 at 9:32 am
... switching either the filegroup or the database to read only would give a performance benefit?
For Sure! No locking will be involved and less resources will be utilized for those Files/Databases. Be careful to TEST that very well though.
* Noel
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply