Very Computer

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.

I would guess that putting tempdb on its own drive is a good place to start
(#1).

You state that this is a test box. Does its configuration reflect a
production system? The reason I ask is that I would never place data files
across disks that weren't in a RAID set.

I've found that putting the pagefile on one RAID1 volume,  tempdb (and its
log) on its own RAID1 volume, all transaction logs on a separate RAID1
volume, and then spreading the data across drive sets (multiple RAID1s or a
RAID 10) has seemed to work out the best for most configurations.

Ron
--
Ron Talmage
SQL Server MVP

Quote:> > 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.