October 8, 2008 at 4:39 pm
Can anyone help me in understanding a little bit about best practice filegroup management in sql server. Basically i want to know will i gain any performance with having the following setup:
PRIMARY FileGroup -> all system objects only
Data FileGroup -> all user objects (tables, stored procs, functions, etc...)
Index FileGroup -> all non-clustered indexes
And having all filegroups have one physical file each and all located on the same disk. Or would i only gain performance by having each filegroup on a seperate physical disk? Any insight into this would greatly be appreciated.
October 8, 2008 at 6:20 pm
When designing, the thumb-rule is no. of spindles is directly proportional to IO throughput.
I would spread my high-transaction tables such that they get many spindles.
i.e. Create clustered index for these tables on Filegroup A and map this filegroup to files on different disks.
There are lots of tips on improving IO performance. Here is something which talks about Filegroups
http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
October 9, 2008 at 6:46 am
While breaking up the files and filegroups as you've outlined will help, don't forget about the database log. It needs to be on a pretty fast disk (RAID 5 would be bad) and seperated from your data storage.
In general, we create three file groups on a database. One for clustered indexes and/or heaps (although we have exceedingly few heaps). One for non-clustered indexes. One for BLOB (varchar(max) and the like). These are stored on three seperate drives, but in some systems, more than three.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply