SQL Server Performance Forum – Threads Archive

Partitioning a SAN for Data, Log, TempDb

I have no experience using a SAN and would like to know if the Microsoft recommendations for separating OS, data, log, tempdb and backups still applies. If so, please explain. I’ve been told it should not matter because the SAN determines how to allocate spindles. I must admit I am skeptical. Thanks, Dave

san vendors have claimed and applied the blind allocation of storage to databases thinking that their powerful san will handle it.
then i get called ($10K) to see the worst disk performance characteristics possible for a database. i have seen $800K with less performance than a notebook disk drive. data and logs each must have dedicated and separate physical disk drives, may also be good to separate temp, depending on your app.
OS should be a no load, and presumable stays on the DAS? some vendors may also give bad advice on the cache setting as well.
i recommend getting performance guarantees on the purchase config (not the max config) and then run database load tests to verfiy claims

I agree with Joe on this. They tried to get us to make huge RAID 5 arrays and just throw everything on there. They had no idea what they were talking about. When the engineers got here, it quickly became obvious. We settled on seperate disks for the data (RAID 5) and log (RAID 10) with the tempdb on mirrored drives on the hosts. The OS is still on the hosts also. If you go with a "virtualized" environment, you would want to look at putting everthing on a SAN/NAS possibly; however, we haven’t gone this route. We do have other disks configured as they suggested. All of our miscellaneous environments are on shared 5 disk RAID 5 arrays. We carve out multiple LUNs from those to server the needs of QA, UAT, etc. We also have our secondary environments on them such as accounting, HR etc. While it serves fine for this, the performance isn’t even close to the dedicated environments. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

In our new SAN environment I was told it is a RAID 10 configuration with drive partitions of C, D and E. The development and user test servers are virtualized, while production servers are not. I’m not clear on how the partitioning works with a SAN. I know our database servers will be assigned C, D and E, but other servers may be assigned more or less drives on the same SAN. In a SAN are disk spindles assigned to a server or do all server utilizing the SAN share the same spindles? If the DB servers are assigned C, D and E as the partitions, are these partitions typically isolated? I’m trying to picture how the isolation works so I know that another server would not be utilizing disk spindles or the same disk platter (assuming a SAN still uses platters) that the DB servers would be usings. Thanks, Dave

We need to clarify a few things for you. Let me know if you have any other questions after this. 1. We don’t really have platters anymore. A lot of the issues, associated with alignment, etc also don’t necessarily exist anymore. 2. The SAN environment has the following physical and logical layers to address a drive letter or mount point to a server. –A. At the physical layer, you have arrays (shelves) of disk drives. Typically, you see 14-15 drives per array or shelf. –B. At the physical layer, you create RAID Groups. This creates a physical RAID group from several physical disks. —-One physical disk can only participate in one physical RAID group. –C. At the logical layer, you create a LUN (logical unit). —-The layer takes one or multiple pieces of one or multiple RAID groups to create a unit you can present to a server as a mount point or drive letter. —-You can sometimes (depending on vendor and model) specify a different kind of RAID array for this LUN. —-As an example, I could create 3 RAID groups. ——RAID Group 1: 6 disks (RAID 10): I create a LUN that uses the entire group and present it as drive letter c: to a server. ——RAID Group 2 and 3: both 5 disks (RAID 5): I combine half of each of these to create a LUN and present it as drive letter d: to a server. I take the rest and make several more mount points or drive letters on one or more hosts. Hopefully, I didn’t just confuse you more. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> A server can share disk spindles with other servers. It can also have dedicated spindles. It really depends on how the RAID Groups, LUNS, and storage groups have been created on the server. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

I appreciate the explanation. It gives me a better picture of a SAN. A couple of questions. 1. We don’t really have platters anymore. A lot of the issues, associated with alignment, etc also don’t necessarily exist anymore.
I don’t understand #1. I thought disks in a SAN still consist of platters rotating at a specific speed such as 15k rpm. Without a platter what purpose would a spindle serve? If I understand #2C, multiple LUNs can share the same RAID configuration. I was told our SAN is a RAID 10 (not sure how many RAID 10 configs). It sounds like it’s possible for mutliple LUNS to be created from one RAID 10. If that’s true, what are the ramifications of one LUN being created for database data files and a second for database log files. Since data files are accessesed randomly and log files, for the most part, are sequential, could performance issues exists since they share the same physical RAID configuration? Thanks again, Dave

#1: You do have disk drives; however, they are enclosed packages now. You can’t do much with them except attempt to attempt to spread the IO out to obtain the most efficient scan/seek/spin rates on the drives. So, you do still have "platters"; however the terminology isn’t really used anymore. Again, it’s a terminology thing. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> #2: <blockquote id="quote">quote:<hr height="1" noshade id="quote">It sounds like it’s possible for mutliple LUNS to be created from one RAID 10. <hr height="1" noshade id="quote"></blockquote id="quote"> That’s correct. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> If that’s true, what are the ramifications of one LUN being created for database data files and a second for database log files. Since data files are accessesed randomly and log files, for the most part, are sequential, could performance issues exists since they share the same physical RAID configuration? <hr height="1" noshade id="quote"></blockquote id="quote"> Well, the answer is…..it depends. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> It really does depend on the nature of your database environment. Sometimes having multiple LUNs spread across one physical array will perform just fine. There are other times, when you should seperate them out and give dedicated LUNs. This provides you a few things: 1. You have no other contention issues with the IO operations on that array. 2. You can tailor the physical disk layout and IO capacities/traits much easier and more efficiently. I would recommend on an extremely high IO environment that you break things out and create seperate physical arrays for the data and log files. Tailor each array to server the needs of the particular operation you will be performing on it. For transaction logs, this happens to be extremely heavy sequential writes with little or no reads. On data files, it can vary greatly depending on the database. It will be random writes, with an extremely variable difference in write/read percentages. There are some environments that will run more efficiently on shared disk LUNs; however, it can be extremely difficult to manage and troubleshoot. You are basically depending on the SAN to have the intelligence to balance the load and IO distribution across all disks. If you have an extremely critical set of files, I believe it’s best to maintain a seperation from that set and any other secondary that might cause issues. MeanOldDBA [email protected]mail.com When life gives you a lemon, fire the DBA.

Thanks again. Do you recommend any books or white papers on SAN technology? Dave

I believe Joe is posting his SQLConnections training session here when he is done with it: http://www.sql-server-performance.com/joe_chang.asp This has some information about setting up SQL Server on a SAN. EMC has several white papers here: http://www.emc.com/techlib/ Microsoft also has compiled a decent list of resources here: http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp Just scroll down to the middle of the page for the SAN information. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks again for all of the advice. I confirmed today they are indeed creating one RAID 10 for the entire SAN. Now we’ll see what happens to the performance of our systems. One last question. When it comes to using perfmon, is disk monitoring approached differently? I typically look at the following counters, Physical Disk: Current Disk Queue Length _Total (divide the disk queue value by the # of physical drives in the array). Physical Disk: % Disk Time_Total SQL Server: Access Methods: Page Splits/sec and sometimes Physical Disk: Avg. Disk Queue Length Dave

The counters are still valid. I also pull the write queue and read queue lengths so I can quickly troubleshoot hot spots. Per physical disk, you never want the queue to be over 1 or 2. Over 2 is definitely bad. How many disks are in the SAN? They are using RAID 10 for the entire SAN. What RAID are they using when they bind the LUNs? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Earlier this morning we requested the configuration details from the consulting company managing the new storage environment. I probably won’t know the answers to your questions until next Monday. Dave

If they are actively managin the SAN, they should have a design workbook and a Visio. Let use know what you find out. What kind of SAN is this? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

They still owe us the details on the SAN configuration, but I was told again it is one large RAID 10. Hopefully I’ll know more about the config in a few days. Thanks, Dave

Ok. Find out the manufacturer/make/model also. That does make a difference. All the SANs have little idiosyncracies. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>