Database Reference
In-Depth Information
Database File Layout
For easy reference, the following are the best practices you should consider when laying out database files:
•
Place the data and transaction log files of a user database on different disks. This allows the
transaction log disk head to progress sequentially without being moved randomly by the
nonsequential I/Os commonly used for the data files.
•
Placing the transaction log on a dedicated disk also enhances data protection. If a database
disk fails, you will be able to save the completed transactions until the point of failure by
performing a backup of the transaction log. By using this last transaction log backup during
the recovery process, you will be able to recover the database up to the point of failure. This is
known as
point-in-time recovery.
•
Avoid RAID 5 for transaction logs because, for every write request, RAID 5 disk arrays incur
twice the number of disk I/Os compared to RAID 1 or 10.
•
You may choose RAID 5 for data files, since even in a heavy OLTP system, the number of read
requests is usually seven to eight times the number of write requests. Also, for read requests
the performance of RAID 5 is similar to that of RAID 1 and RAID 10 with an equal number of
total disks.
•
Look into moving to a more modern disk subsystem like SSD or FusionIO.
For a detailed understanding of database file layout and RAID subsystems, please refer to the “Disk Bottleneck
Resolutions” section of Chapter 3.
Database Compression
SQL Server has supplied data compression since 2008 with the Enterprise and Developer editions of the product. This
can provide a great benefit in space used and in performance as more data gets stored on a page. These benefits come
at the cost of added overhead in the CPU and memory of the system; however, the benefits usually far outweigh the
costs. Take this into account as you implement compression.
Database Administration
For your reference, here is a short list of the performance-related database administrative activities that you should
perform on a regular basis as part of the process of managing your database server:
•
Keep the statistics up-to-date.
•
Maintain a minimum amount of index defragmentation.
AUTOCL0SE
or
AUTOSHRINK
.
•
Avoid automatic database functions such as
•
Minimize the overhead of SQL tracing.
In the following sections, I cover the preceding activities in more detail.
■
For a detailed explanation of SQL Server 2014 administration needs and methods, please refer to the microsoft
Note
