SQL Server 2005 Configuration Blog #2.doc
Prem Mehra and Mike Ruthruff
In an earlier blog, Deploying SQL Server 2005 with SAN #1, we addressed three topics: 1) the core SQL Server requirements with respect to IO subsystem, 2) the complexity introduced by virtualization of the IO subsystem and 3) the type of RAID level to choose.
In this one we want to focus on how best to assign SQL Server objects – logs, data and index objects for persistent and temporary data bases - to LUNs for achieving good performance while keeping design simple. We need to consider four types of objects – logs, persistent data / index objects, TempDB, and flat files for backups and incoming / outgoing files for interfacing with external systems. Recommendations are as follows:
We recommend that you allocate a database log file to a LUN which has dedicated physical disks that are not shared by other LUNs including those which will be used for other log files or data / index objects. The primary reason is that the log I/Os are sequential in nature and any other disk activity can increase the log write latency.
Some of the installations find it very difficult to follow this recommendation because of large number of databases and complexity introduced by having many LUNs. For example, if you have consolidated say 300 data bases, it is quite impractical to have 300 or more spindles dedicated to log files. The SAN administrators are not very receptive to this idea any how because it defeats the value of virtualization which makes SANs more cost effective.
In such cases, we have observed a deployment practice where a set of dedicated spindles are used to create LUNs that are assigned to logs. These spindles are not used for creation of other LUNs which are to be used to allocate files for assignment of data and index objects.
We have observed that even these installations maintain a separate LUN (and in turn disks) for their mission critical databases when they use a pool for their less critical consolidated database logs.
The objective is to aim for 2 to 3 msec response time for log writes. Keep in mind that large amounts of cache on some disk arrays help in achieving this goal; however, these response times should be achievable under realistic working conditions. Initial SAN base-line testing (to be discussed in another blog) could be used to test this ahead of SQL server deployment.
Next, let us consider TempDB.
If the work load has significant TempDB write activity (creation of temporary objects, row versioning, on-line index, etc.), it is preferred to have RAID 10 for TempDB. This would be helpful in getting optimum performance. However, due to cost considerations some installations may decide not to use RAID 10. In that case, RAID 0 is an option, but keep in mind that if you lose a disk in this scenario, you have impacted system availability. If the work load is not write-intensive for TempDB, RAID 5 should suffice.
The next interesting question is about allocation and placement of persistent objects. First, should persistent data and index objects share physical disks with each other; and, second, should TempDB be placed on these disks or on separate physical disks?
Our recommendations are geared for installations whose work load is variable over time, difficult to analyze because SQL statements are generated by tools, issued by third party packages over which they have little or no control, or the staff does not have the resources and time to analyze the work load in detail.
The recommendations depend on the TempDB and persistent data bases RAID types - first let us consider when the RAID types are the same.
We suggest that you take the spindles - not dedicated to log files and other objects - and assign them for the various data and index objects including TempDB (more on creating LUNs and striping across them in a future blog). This recommendation is simple and straight forward. It avoids several pitfalls including one when carefully managed allocation optimized for one work load becomes wasteful for another set of workload. This also offers the most flexibility and should ensure the best performance in situations where there is a good deal of random IO requests.
A variation to the above scheme would be to assign TempDB to a set of specific LUNs (on dedicated physical disks) if one wants to dedicate additional or fewer resources to TempDB or if TempDB has different RAID grouping than permanent objects.
Keep in mind, if you dedicate some disk resources to an object XYZ, those resources are not available to the general pool for others to use when XYZ is not fully utilizing it, thus those resource are simply wasted. By the same token, if object XYZ can benefit from additional disk resources in the pool, that benefit is not available as well. So, if one starts to fine tune (micro manage - as some might say) the allocation of disk resources to specific objects, one should do testing under various load conditions to see that the various competing goals are achieved.
The above easy to implement simple recommendation to spread database objects across all spindles (except those dedicated to Logs) works reasonably well both for OLTP and OLAP.
For OLTP work load – where the I/O is predominantly random – the availability of more spindles provides good response time. For Data Warehouse, ad-hoc relational queries against VLDB typically using pre-fetch I/Os against several objects benefit from large number of spindles. Also, a hash join - that scans a subset of a fact table and involves some random access to dimensions - but lots of I/Os to the TempDB, also benefit from increased number of spindles made available because of spreading all objects across all spindles.
However, we believe that there are a few installations which can do better by not following this recommendation. These are the installations that fully understand the behavior of their work load over days and weeks and have the time and expertise for evaluating and testing various allocation and placement strategies for their database objects. These installations – given sufficient time and resources are very likely to come up with allocation strategies that are superior to the one recommended here. They should obviously follow those strategies that are best suited for their environment.
In one of the future blogs, we will focus on establishing base line SAN performance prior to deploying SQL Server.