| |
 |
|
SQL Server Tips by Burleson |
Proper Database and Log File
Placement
Lastly, with respect to database I/O, the DBA should make sure the
database and log files are on separate drives and the drives are
formatted properly for the files’ demands. For static databases,
this might not be too much of an issue, but for dynamic databases,
it is critical.
Separating database and log files help lessen the impact of physical
I/O contention at the server level as long as separate controllers
exist for the drives in question. Drive selection and formatting are
important, too. Log activity is obviously very write intensive, so
RAID format selection makes a big difference in performance. RAID5
is not the natural choice for log or TEMPDB files, but RAID0, RAID1,
RAID0+1, or RAID1+0 are good choices. RAID5 can be okay for database
files, especially for read intensive databases.
Chapter 5 contains a procedure named up_bn_storage_overview that
provides a good storage overview of SQL Server and specifically
presents a listing of database and log placements on the server
drives. This procedure instantly reveals if database and log files
were accidentally placed on the same drive or other drives whose
RAID levels are not right for the files in question.
After performing server and database workload analysis, the DBA can
then begin to dive into session and SQL analysis, focusing on
databases exhibiting the highest overall server workloads.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
