better disk config for staging & tempdb files...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jéjé - 29 Jun 2005 02:41 GMT

Hi,

I want to have your feedback on how to configure my drives to insure a good
performance during loading process & transformation against a staging
database.

Image you have 4 drives available for the staging database & tempdb
database...
loosing these disk is not important, so no redundancy required.
I read from an external database into the staging, I do some updates and
copy into the staging himself, and finally I read the staging to load the
datawarehouse.
I execute more then 70 DTS packages, the sequence of these packages is
optimized. So I could write a fact table in the staging while I read another
to fill the datawarehouse at the same time.
I have 4 "big" fact tables (from 1 millions of rows to 20 millions)
There is "only" 4Gb to 5Gb used by the staging database during the loading
process.

how to configure these disks?
Does it better to setup all disks in raid 0? (the system do everything)
Does it better to keep the 4 disks separatly, and create multiple files &
file groups and dedicating 1 disk by fact table?
Creating 1 filegroup and 4 files in this file group (1 on each disk)? (SQL
Server manage the sharing usage of the disks)
Where to put log files?

thanks for your feedback.

Jerome.

Reply to this Message

Danny - 30 Jun 2005 13:15 GMT

Jerome,

What's the cost to the business if your DW is down for several days? Having
nonredundant disk for tempdb is generally unacceptable. Having nonredundant
disk for staging is a bit unusual. Push back to get more disk.

If I were placed in this situation and could not get more disk, I'd mirror
and strip with the controller and say that's all the performance she's got.

If management and the business owners are ready to signoff on the potential
of several days down time. The amount of down time depends on outside
hardware availability and support staff availability. Put your logs for
tempdb and staging on one disk. Use the disk controller to strip the other
three and place one file per processor (create all the same size) within the
file group. Normally I would break it out more but four disks isn't enough
to do much with.

> Hi,
>
[quoted text clipped - 26 lines]
>
> Jerome.

Reply to this Message

Jéjé - 30 Jun 2005 23:27 GMT

the cost is 0 if we can't load data.
a downtime of 4hour to change a disk if we don't have one in the office is
not an issue.

I don't understand why having the tempdb database on a non-fault taulerant
disk is not a good idea from your point of view!!!
tempdb is the only database recreated when er restart SQL server, so the
downtime in case of a crash is ... 5 minutes?

Thanks for the guide about 1 disk by processor.
I'll plan this config.

> Jerome,
>
[quoted text clipped - 45 lines]
>>
>> Jerome.

Reply to this Message