In I/O terms SQL Server should be running on a platform that allows it to achieve a single figure latency (ms - millisecond) from the underlying storage.
Host Considerations
Out of scope for this post are hardware considerations, but I shall list those items as things to check or questions to pose for your IT team
What are the speeds and throughput (IOPs) of the drives in question?
If physical drives, how are they configured? (remembering the write performance overhead of a RAID 5 configuration).
If a SAN is involved, what tier storage are we on? Is it guaranteed?
Are any processes in place that move volumes across storage?
On a virtual environment, are the drives Thick or Thin provisioned?
Windows Considerations
Ensure the following 3 items are configured for Windows
- Configure the Service Account you will run SQL from with the "Perform Volume Maintenance Tasks" Group Policy. (See SQL Service Account Settings Summary)
- Ensure drives dedicated for SQL Data, Log and Backup files are formatted to a cluster size of 64K.
- If using an OS earlier than Windows 2008, ensure drives are aligned correctly. (See Disk Alignment for SQL Server)
Basically, before you even install SQL Server measure the capability of the host you've been provided with. The SQLIO tool can help you measure this.
File Placement
The access pattern for reading and writing Data is somewhat random in OLTP systems. Logs are written to sequentially however. Separating their files can be beneficial for performance. Separating the backups of data and logs from the data and logs themselves is common sense to prevent data loss.
Therefore, ensure separate drives for
- SQL Data
- SQL Logs
- SQL Backups (Data & Transaction Log backup folders)
Ensure system databases are installed in those locations too (not just the default c: drive location)
Tempdb
In large environents it may be wise to situate tempdb on it's own drive.
To further optimize tempdb access, splitting it into equal files (start with 1 per core) is ideal to allieviate pressure on file access.
File Configuration
Managing growth is a DBA task and balancing between presizing databases and using auto growth settings is necessary.
Presizing datafiles
Manually growing data files so data grows into them is good practice.
It prevents file system fragmentation and I/O delays when autogrowth occurs for a data file.
It may confuse windows systems administrators however who will see a drive as being full and percieve that a system is low on space.
Autogrowth Settings
If using Autogrowth Settings (even as a backup) then a balance needs to be achieved.
A fixed size growth (e.g. the default of 1MB) will generate file system fragmentation. If several databases configured in this way are also expanding, a drive could become very inefficient.
Using percentage growth settings is initially fine, but a 150GB database that suddenly has to expand by 10% will stop everything as it tries to allocate the next 15GB.
Autoshrinking
Just say NO. If you find a database with this enabled, turn it OFF!
SQL server will end up repeatedly growing the database by the autogrowth settings when new data arrives, followed by shrinking it again.
VLDBs - Very Large Databases
If you are lucky enough to be the developer and DBA, then you can take advantage of filegroups. Filegroups enable objects within a database to be stored separately. Different filegroups could be placed on separate drives. This could be for capacity or performance reasons.
Filegroups can be used -
- to separate heavily used tables
- to separate data from indexes
- to enable filegroup backups and implement a custom backup schedule on different types of data
- for data Partitioning should you be using a capable SQL edition *
* Current data could be on the more expensive faster storage, whereas archive queries which aren't run as frequently could come from another source.
