SQL Server Performance

Partitioning a SAN for Data, Log, TempDb

Discussion in 'Performance Tuning for Hardware Configurations' started by DBADave, Mar 17, 2005.

  1. DBADave New Member

    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
  2. joechang New Member

    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

  3. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. DBADave New Member

    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
  5. derrickleggett New Member

    We need to clarify a few things for you. Let me know if you have any other questions after this.<br /><br />1. We don't really have platters anymore. A lot of the issues, associated with alignment, etc also don't necessarily exist anymore.<br />2. The SAN environment has the following physical and logical layers to address a drive letter or mount point to a server.<br />--A. At the physical layer, you have arrays (shelves) of disk drives. Typically, you see 14-15 drives per array or shelf.<br />--B. At the physical layer, you create RAID Groups. This creates a physical RAID group from several physical disks.<br />----One physical disk can only participate in one physical RAID group.<br />--C. At the logical layer, you create a LUN (logical unit).<br />----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.<br />----You can sometimes (depending on vendor and model) specify a different kind of RAID array for this LUN.<br />----As an example, I could create 3 RAID groups.<br />------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.<br />------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.<br /><br /><br />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.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  6. DBADave New Member

    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
  7. derrickleggett New Member

    #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=':)' /><br /><br />#2: <br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" 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"></font id="quote"></blockquote id="quote"><br />That's correct.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />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?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />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:<br /><br />1. You have no other contention issues with the IO operations on that array.<br />2. You can tailor the physical disk layout and IO capacities/traits much easier and more efficiently.<br /><br />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.<br /><br />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.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  8. DBADave New Member

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

    Dave
  9. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  10. DBADave New Member

    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
  11. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  12. DBADave New Member

    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
  13. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  14. DBADave New Member

    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
  15. derrickleggett New Member

    Ok. Find out the manufacturer/make/model also. That does make a difference. All the SANs have little idiosyncracies.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page