Share via

Storage Consideration for SQL Server 2005 DW environment

  • Article

Storage design is a key component of data warehouse/BI environment. Optimum storage design provides significant performance. SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQL Server uses tempdb for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

Following key points should be considered while designing storage system for consolidated SQL Server environment.

  • RAID 1/0 (or RAID 10) gives excellent random read/write performance. RAID 10 is ideal for OLTP environment with lots of small random read/writes. This RAID type also provides good fault tolerance since it can survive the failure of up to half of the disks, provided one disk in each mirror image pair survives. Use RAID 10 if more than 30 percent of the IO is small random writes and if budget permits. RAID 10 is recommended for OLTP Data, Transaction Log, and Tempdb data volumes.
  • RAID 5 gives excellent read performance, especially large sequential I/O. But it provides lower random write performance. It also delivers flower fault tolerance than RAID 10 since it can tolerate only one drive failure per RAID 5 LUN. Also, in the event of a drive failure, the time for the storage system to rebuild the content of the failed drive is longer than RAID 10. RAID 5 costs less for the same storage capacity compared to RAID 10. RAID 5 is ideal for Backup volumes.
  • SQL Server log files are accessed sequentially and are write intensive. Since RAID 5 requires 4 I/O per write (write data, validate data, write parity, and validate parity), placing the log files on RAID 5 array greatly increases the possibility for an I/O bottleneck. Placing the data file and log files on the same drive spindles creates contention for the drive heads between the sequential movement required for the log writes versus the random drive head movement required to read and write data. Because of this, the log file should not be placed on the same set of spindles as the data files. The log files should be placed on a RAID 1+0 arrays.
  • SQL Server 2005 makes greater use of tempdb than SQL Server 2000. For high performance applications, tempdb should be placed on a dedicated RAID 1+0 array and sized large enough so it does not auto grow. TempDB has a large number of objects being created all the time. For an object to be created, space must be allocated to it. Space allocation is determined by looking at some of the internal system pages (the GAM, and SGAM). In the end, it is these pages that start to have significant contention (with just one file) in a VERY active TempDB. To minimize that contention you should create multiple files. The guideline is to create as many numbers of files as the number of processor cores on the system. So, for example if you have dual core 8 way processor, you should have 16 files for your tempdb database.
  • Backups should be made to drives other than the ones used for data and log not only to separate out I/Os, but physically separate drives means that even if the data or log disks fail, the backup drive will most likely still exist. Writing the backup file to the same set of disks means that the data file is being read from and the backup file is written to all using the same I/O pool. It also puts available space at risk since backups can fill up drives quickly without a proper retention policy to manage them.
  • All versions of SQL Server write in 8k pages and the read ahead is 64k. The recommendation is to format any disks which may be used for SQL Server data using a 64k block size. Using defaults for NTFS (which is not 64K) will hamper SQL Server performance. Even if there is the potential that a disk may be used for SQL Server data, format it with a 64k block size.
  • SQL Server on non-optimized and shared SAN where many different applications and I/O profiles access the same spindles can result in performance issues. Sections of the storage should be reserved for high performance SQL Server requirements and carved up at the time of deployment of the particular SQL Server solution. These storage allocations should not come from the general storage pool.
  • Sector alignment. Sector alignment can increase the I/O performance of SQL Server by up to 20%. If sector alignment is required, this will be done via the command line utility DISKPART in Windows Server 2003 prior to formatting the disks with NTFS.

Comments