June 26, 2003 at 10:40 am
Will creating filegroups in the same drive improve performance? I want to create data and index groups in my D drive, which has been allocated to me. Can I leave my primary group alone? Any ideas will be greatly appreciated.
June 26, 2003 at 12:27 pm
Definitely NOT. I would recommend to create more files per each group and place on the different disks (2 to 3 file on each disk), this will definitely improve the performance because when reading the table a separate thread will be created for each file to access the table data. If your table is heavily accessed table like orderdetail or orderhistory I would recommend to create table in one group and tables index in another file group and place on different disks. As you know log file is more write intensive, place it on a separate disk
Shas3
June 27, 2003 at 9:34 pm
Hi
I wouldnt say no straight out. Sure, its not ideal, but using fg's to seperate out objects (ie. data vs index or audit objects) isnt a bad thing and give you the added flxibility of moving the fg's around when more disks come in. You also need to carefully measure the real IO going on (aka disk queue lengths), dont go spending up big when its a 200Mb db with bugger all IO for example. Also remember, servers are multi-user beasts, disks are spinning all over the shop, and you have users accessing all sorts of dbms objects. Disk speed is important, but more so is maximising the use of the procedure and the buffer cache's to reduce physical IO and ensure optimial utilisation of the buffers.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply