< Back
Page 8 of 8
This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
Best Practices
This chapter focused on optimizing storage and I/O. Best practices directly related to storage and I/O are the following:
- The default configuration of filegroups and files is adequate only for small and/or lightly used databases. High-performance databases will require a lot of tuning and modification from the defaults. Be sure to document your changes.
- When creating filegroups and files for a database, preconfigure their size for what they need to be a year or more from today. Set the autogrow parameter to a specific, absolute value of sufficient size so that autogrowth will not happen frequently. Better yet, avoid autogrowth altogether by enlarging data files and transaction log files during preventative maintenance hours.
- Ensure that database files and the transaction log file for all databases reside in RAID volumes. RAID1+0 is the best, but more costly. RAID5 is good for read-heavy applications. If cost is an issue, RAID1 is an inexpensive option for write-heavy databases, such as tempdb.
- SSD is a game-changer. It is exceedingly fast compared to hard disks, especially for applications that are heavy on randomized I/O. However, SSD has its own set of special management issues.
- Monitor and tune storage and I/O for tempdb just as you would for a business-critical production database. On SQL Server instances with many active databases, tempdb is frequently the busiest database on the entire instance.
- Do not create multiple files and filegroups for the transaction log file.
- Database files, the transaction log file, and operating system files should be located on physically separate volumes for performance and availability. By “physically separate,” understand that this means completely separate from other I/O intensive processes. It will do no good, for example, to put all transaction logs together onto the same RAID. In business-critical applications, each database file should be separate from other database files, while the transaction log file should be completely isolated.
- Create additional database files on physically separate volumes to speed I/O. Make sure that all database files are of identical size and organize them through the use of filegroups.
- If the following steps have not sped up I/O enough to meet the needs of the application, consider creating partitions on the most heavily used table(s) and/or index(es) to further segregate I/O onto physically separate volumes.
- SANs are often black boxes to DBAs. Make sure that I/O performance is at least comparable to direct-attached storage (DASD) and that SAN administrators are held accountable not only for storage volume but also for I/O throughput.
In addition, the following list is a summary of some other of the best practices from this chapter:
- Leverage data compression to reduce disk space consumed by data files and to speed I/O by reducing the overall amount of I/O occurring.
- Do not set the database to automatically shrink because this leads to performance degradation and excessive fragmentation over time.
- Configure the recovery model for each database accordingly and implement a backup and restore strategy. This should also include the system databases and should be tested regularly. Having a backup doesn’t mean you can recover a database, and only a test recovery will reveal that.
- Review the other break-out chapters in the book for more information on items such as hardening a SQL Server, encryption, Policy-Based Management, Resource Governor, and SQL Agent jobs like backups, DBCC corruption checks, and maintenance activities.
< Back
Page 8 of 8