Database Reference
In-Depth Information
4.
To verify the files belonging to a database, execute the following commands:
USE AdventureWorks2012;
GO
SELECT * FROM sys.database_files;
GO
Moving the Log Files to a Separate Physical Disk
SQL Server transaction log files should always, when possible, be located on a separate hard disk drive from all other
SQL Server database files. Transaction log activity primarily consists of sequential write I/O, unlike the nonsequential
(or random) I/O required for the data files. Separating transaction log activity from other nonsequential disk I/O activity
can result in I/O performance improvements because it allows the hard disk drives containing log files to concentrate on
sequential I/O. But, remember, there are random transaction log reads and the data reads and writes can be sequential
as much as the transaction log. There is just a strong tendency of transaction log writes to be sequential.
The major portion of time required to access data from a hard disk is spent on the physical movement of the disk
spindle head to locate the data. Once the data is located, the data is read electronically, which is much faster than the
physical movement of the head. With only sequential I/O operations on the log disk, the spindle head of the log disk
can write to the log disk with a minimum of physical movement. If the same disk is used for data files, however, the
spindle head has to move to the correct location before writing to the log file. This increases the time required to write
to the log file and thereby hurts performance.
Even with an SSD disk, isolating the data from the transaction log means the work will be distributed to multiple
locations, improving the performance.
Furthermore, for SQL Server with multiple OLTP databases, the transaction log files should be physically
separated from each other on different physical drives to improve performance. An exception to this requirement is
a read-only database or a database with few database changes. Since no online changes are made to the read-only
database, no write operations are performed on the log file. Therefore, having the log file on a separate disk is not
required for read-only databases.
As a general rule of thumb, you should try, where possible, to isolate files with the highest I/O from other files
with high I/O. This will reduce contention on the disks and possibly improve performance. To identify those files
using the most I/O, reference sys.dm_io_virtual_file_stats.
Using Partitioned Tables
In addition to simply adding files to filegroups and letting SQL Server distribute the data between them, it's possible
to define a horizontal segmentation of data called a
partition
so that data is divided between multiple files by the
partition. A filtered set of data is a segment; for example, if the partition is by month, the segment of data is any
given month. Creating a partition moves the segment of data to a particular filegroup and only that filegroup. While
partitioning is primarily a tool for making data management easier, you can see an increase in speed in some
situations because when querying against well-defined partitions, only the files with the partitions of data you're
interested in will be accessed during a given query through a process called
partition elimination
. If you assume for
a moment that data is partitioned by month, then each month's data file can be set to read-only as each month ends.
That read-only status means you'll recover the system faster and you can compress the storage resulting in some
performance improvements. Just remember that partitions are primarily a manageability feature. While you can see
some performance benefits from them in certain situations, it shouldn't be counted on as part of partitioning the data.
SQL Server 2014 supports up to 15,000 partitions.
