How many logical volumes for large database?

We are about to migrate a large database server (600+ GB data) onto new
hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box with
SAN attached storage. My question concerns how many logical volumes we
should have to support the various database components.
Currently, we have separate logical drives for the Data files, Indexes,
Logs, and TempDB. This was done for two reasons:
First, we had four available SCSI channels, so by creating four arrays each
on a different channel we increased disk I/O.
Second, we did it to reduce fragmentation. The database is used mainly for
running reports and analyses off of data from our main business application,
which is run on a VMS cluster. That system can't handle the performance hit
for all of the reporting, so once a week data is dumped from the mainframe
into the SQL Server. The rest of the week the SQL db is essentially
read-only. So the data volume is fairly static, growing on a weekly basis
but almost never shrinking. For performance reasons, the indexes are
dropped and recreated weekly, the logs are truncated and then grow, and of
course TempDB grows and shrinks as needed. By separating each component
onto a different logical drive, we should have less fragmentation.
Now that we are moving to a new architecture, the question has been raised
as to whether this is still the best setup, or if it just creates
unnecessary administrative overhead. Obviously we no longer get a
performance gain from multiple I/O channels, because all of the storage will
be accessed via the same fiber channel to the SAN.
As for the fragmentation issue, is it really that much of a problem? Would
it cause issues if we were to, say, combine data and indexes on the same
volume, or logs and TempDB?
The goal is to reduce administrative overhead. But since we have data
files, indexes, logs, and tempDB in any case, does it make a difference from
a management standpoint whether they are all on one volume or on four
separate ones? Would it be an issue either way from a backup/restore
standpoint?
Thanks,
Gary
Gary,
You can still benefit from multiple volumes on a SAN, specifically for data,
log, and tempdb. (Since it is essentially a read only db,
you apparently don't need a volume for backups.) Even with one HBA, having
independent sets of spindles for different types of IO (data, log, and
tempdb) usually provides some performance gain.
If the queries are active and make any use of temp tables, then you can
benefit from having tempdb on its own volume. The database log files will be
active when loading, so keeping them on their own volume should help with
load time.
I've seen many installations with that much or more data that don't use
separate volumes for indexes. If you never update the tables, and recreate
all the indexes weekly with each new load, you should not have problems with
defragmentation. But check anyway by using DBCC SHOWCONTIG.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Gary Dom" <domgATsutterhealthDOTorg@.no.spam> wrote in message
news:%230VstQAnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> We are about to migrate a large database server (600+ GB data) onto new
> hardware. It is currently on a 450 MHz quad CPU, 4 GB RAM box with local
> SCSI storage, and will be moving to a new 3 GHz Quad CPU, 6 GB RAM box
with
> SAN attached storage. My question concerns how many logical volumes we
> should have to support the various database components.
> Currently, we have separate logical drives for the Data files, Indexes,
> Logs, and TempDB. This was done for two reasons:
> First, we had four available SCSI channels, so by creating four arrays
each
> on a different channel we increased disk I/O.
> Second, we did it to reduce fragmentation. The database is used mainly
for
> running reports and analyses off of data from our main business
application,
> which is run on a VMS cluster. That system can't handle the performance
hit
> for all of the reporting, so once a week data is dumped from the mainframe
> into the SQL Server. The rest of the week the SQL db is essentially
> read-only. So the data volume is fairly static, growing on a weekly basis
> but almost never shrinking. For performance reasons, the indexes are
> dropped and recreated weekly, the logs are truncated and then grow, and of
> course TempDB grows and shrinks as needed. By separating each component
> onto a different logical drive, we should have less fragmentation.
> Now that we are moving to a new architecture, the question has been raised
> as to whether this is still the best setup, or if it just creates
> unnecessary administrative overhead. Obviously we no longer get a
> performance gain from multiple I/O channels, because all of the storage
will
> be accessed via the same fiber channel to the SAN.
> As for the fragmentation issue, is it really that much of a problem?
Would
> it cause issues if we were to, say, combine data and indexes on the same
> volume, or logs and TempDB?
> The goal is to reduce administrative overhead. But since we have data
> files, indexes, logs, and tempDB in any case, does it make a difference
from
> a management standpoint whether they are all on one volume or on four
> separate ones? Would it be an issue either way from a backup/restore
> standpoint?
> Thanks,
> Gary
>
>