I have set up a test db using filegroups before and found the results to be
good improvement in performance. However now my requirements are slightly
different and I think Im going to want to make some slight modifications. I
have got 8 hard drives in my test box. I currently have got the tables in
the db split over 7 of them through the use of each table being in its own
filegroup. (each filegroup covers all 7 drives) The log file is on its own
drive. Now however, Im needing to write a proc that uses some cursors.
According to BOL in regards to TempDB:
Place the tempdb database on a fast I/O subsystem to ensure good
performance. Stripe the tempdb database across multiple disks for better
performance. Use filegroups to place the tempdb database on disks different
from those used by user databases.
Also, according to an article for tuning cursors:
If you have no choice but to use cursors in your application, try to locate
the SQL Server tempdb database on its own physical device for best
performance. This is because cursors use the tempdb for temporary storage of
cursor data. The faster your disk array, the faster your cursor will be.
[6.5, 7.0, 2000]
So as you see there leaves lots of room for how to best configure my db
accross all the disks. There are several options here.
1. Filegroups for the tables in my db accross 6 disks. 1 disk for the log. 1
disk for tempdb.
2. Filegroups for the tables in my db accross x disks. 1 disk for the log. x
disks for tempdb.
3. Filegroups for both the tables in my db and Tempdb. 7 disks for both. 1
disk for log.
Does anyone know based on actuall experience what the best way to configure
this would be? I can read BOL and this article and take my best guess. But
has anyone already experienced this and got any input?
Thanks in advance.