September 10, 2001 at 5:23 am
So is it better at database creation to make the database size as big as it'll get ? will this have any impact ? and if not, which is better to use for database growth, percent increase or fixed size increase ?
September 10, 2001 at 10:27 am
One thing I have read, but not tested, is that mutliple filegroups improve IO as a separate thread is created for each file. This can have some benefits for splitting a heavily used table to a separate group.
Steve Jones
September 10, 2001 at 11:25 am
Steve,
I hate to pick your brain too much but, do you remember where you read this. I am looking into this as a possibility for performance improvement and if others have already done the study legwork it would be nice. Let me know. Thanks.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 10, 2001 at 12:41 pm
I've implemented this myself based on a Technet article. As long as the files are placed on separate drive letters, added parallel IO is created for SQL Server. It is no replacement for RAID, but it makes a nice addition. We did see a minor performance boost by using separate files of diff drives..
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
September 11, 2001 at 9:16 am
I have a few refs:
BOL:Placing Tables on Filegroups
BOL:Using Files and Filegroups
Inside SQL Server - Doesn't really discuss, but recommends not too unless advanced DBA, knowedg of DB, etc. Does note the Internal MS SAP system uses 12 files. Doesn't say why.
http://www.microsoft.com/technet/treeview/default.asp?url=/TechNet/prodtechnol/sql/maintain/rdbmspft.asp - In the parallel data retrieval section, it mentions a separate thread is used for each file (for parallel access). The benefits of this will be limited unless the files are on separate disks.
Steve Jones
September 11, 2001 at 10:04 am
Thanks for the reply and the link. I had already found it a downloadable format at http://www.microsoft.com/sql/techinfo/administration/2000/RDBMSPerfTuning.doc and was already reading the information. (Sorry, should have posted earlier - Bad David)
I will keep you up to date, if interested, on what kind of gains I see in performance. Roll-out for me will probably be in the next month to 6 weeks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply