7 Replies Latest reply on Oct 14, 2010 11:32 AM by MaxStr

    SQL Server best practices?

    MaxStr Enthusiast

      I am wondering how to set up my SAN for best performance with a SQL server. Currently we have 4-5 SQL server VM's, and we will be migrating them all into one VM. That VM will be running on Windows 2008 R2 and ESX 4.1, with a Sun Storage 7210 SAN. 

       

      On the SAN, I am planning two iSCSI LUN's, one for data and the other for logs. Some of the options I have are compression, provisioning, checksum, sychronous write bias, block size,and deduplication.

       

      -Compression options are GZIP or GZIP-2, along with some others, or just none. It's enabled by default.

      -Provision I am thinking thin, but since the database grows 24/7, maybe thick is better?

      -I know that sychronous write bias should be set for "throughput" for data, and "low latency" for the logs.

      -I read that 8K block size is optimal for the data, but I don't know what is a good block size for the logs.

      -Deduplication: will this be beneficial for either the data or log LUN's?

       

       

       

       

       

      The VM is pretty standard: 4 gigs ram, 4 CPU, thin provisioned, default NIC and drive controller.

      The ESX networking side uses iSCSI with jumbo frames, multi-pathing, and NIC teaming 2 gigabit connections.

       

      Any suggestions?

       

       

       

      Thanks

        • 1. Re: SQL Server best practices?
          nofragger Novice

          -Provision I am thinking thin, but since the database grows 24/7, maybe thick is better?

          Some people experience performance issues when there is lots of writing on the storage in combination of thin provisioning.

          I don't experience that much diffirence, offcourse vmware has to allocate more disk space on writing, but i don't experience that much difference.

          Here is a blog about making SQL thin provisioning friendly:[Click|http://derek858.blogspot.com/2009/12/make-sql-20052008-thin-provisioning.html]

           

          -I know that sychronous write bias should be set for "throughput" for data, and "low latency" for the logs.

          I think thats correct ( other vmmers correct me if i am wrong. )

           

          -I read that 8K block size is optimal for the data, but I don't know what is a good block size for the logs.

          On the datastore there issn't much diffirence sinds the 4.0 version i think, here a good blog about it: Click

          • 2. Re: SQL Server best practices?
            RParker Guru

            SQL Best Practices apply to LOCAL DISKs, not SAN.  So you don't need to separate, OS, Logs, Data into separate drives.  They can all be on the same disk.  SAN uses the same pool of disks for LUN's anyway, so by separating them only means adding more overhead to the VM, but the performance will not be affected.

             

            -Provision I am thinking thin, but since the database grows 24/7, maybe thick is better?

             

            yes, thick.

             

            -I know that sychronous write bias should be set for "throughput" for data, and "low latency" for the logs.

             

            Again this is RAID controller of the LOCAL machine, not SAN.  You can't control SAN IO, that's why more spindles is better, to mitigate the IO and latency.  If your SAN is good, you shouldn't need to worry about this.  If your SAN utilizes SATA, not good.  SAS/Fiber drives for high IO always, SQL qualifies as high IO.  More spindles is better, especially for SQL. Although memory for the VM IS a huge factor for caching, so really caching techniques will keep the SAN from being overrun.....

             

            -I read that 8K block size is optimal for the data, but I don't know what is a good block size for the logs.

             

            That's local disk, not SAN.  Typically you have no control over how the SAN sets up the block storage, unless you use RDM.  But you DO have the ability to set it in the VM OS, but still not important, since ultimately the data resides on the SAN anyway (block alignment on SAN should be mentioned here).  This is where you want to ensure good block alignment with VMFS, VMs and SAN.

             

            -Deduplication: will this be beneficial for either the data or log LUN's?

             

            YES!!  VERY beneficial for LOGS, less so for DATA but STILL some impact.  I don't know of a negative reason to use DeDuplication, so I would ALWAYS enable it.  It really can't hurt.. the things I read just means doing the dedupe at off hours to minize IO.. but nothing I have read discourages deduplication for anything.

             

             

            Any suggestions?

             

            I assume you are setting up the SQL servers to set memory to no more than 3072, and minimum OS to 512K.  Basically you reserve 500 Meg for the OS, and don't let SQL have more than 75% of TOTAL RAM in the OS... you don't want SQL running out of memory.. that wouldn't be good at all.. So you limit the RAM for SQL.  by default SQL takes ALL available RAM until its exhausted.

             

            Also 4 CPU you might want to watch that.. SQL isn't really all that CPU intensive, even with many queries running.. 2 vCPU definately, 4.. uh.. a slight overkill, besides that reaches the point where VM's are in contention for CPU, so I suggest you start with 2 vCPU, you will discover that should be plenty... especially if you have Turbo Intel Procs.

            1 person found this helpful
            • 3. Re: SQL Server best practices?
              MentholMoose Hot Shot

              I'm using using a 7410 at work. We haven't optimized any LUNs specifically for SQL since we don't have any SQL servers with extremely high performance requirements, so I can't suggest block sizes for SQL data or logs. However I think 8K block size is too low for the OS and general use. 64K has worked well for us. I believe you are correct regarding synchronous write bias, based on this blog post: Synchronous write bias property. We use thin provisioning, have compression disabled, and other options at default.

               

              Dedup is unstable on these things; be very, very careful with it. We enabled it on a couple LUNs, and it worked fine for a few days. After that the 7410 became nonresponsive and the VMs on it crashed. The only solution was to create new LUNs without dedup and migrate all the data. Things were fine for a while, until we tried deleting those LUNs with dedup, and the 7410 became nonresponsive again! Slowness (to the point of being unusable) is a known issue when deleting LUNs that had ever had dedup enabled. To avoid that slowness requires deleting the entire pool. With one pool per appliance being the recommendation, this requires migrating everything off and back on.

               

              The latest software has some bug fixes for dedup (along with other fixes, such as a single bad disk causing the appliance to crash, which we also suffered). Basically, unless you are on the latest software, don't even think about dedup.

               

               

               

              ---

              Kirk Kosinski

              VCP 4, MCITP: EA / VA / SA / EDA7

              • 4. Re: SQL Server best practices?
                MaxStr Enthusiast

                 

                Thanks for the helpful information. However, I am able to make these settings to individual LUN's. I know the LUNs all come from the same pool of disks, but it does allow me to change all the settings I listed for each individual LUN. So for example, I can enable deduplication on the logs LUN but not the data LUN, and I can change latency vs throughput on each LUN as well.

                 

                 

                 

                 

                 

                • 5. Re: SQL Server best practices?
                  RParker Guru

                  you said you are using thin provisioning along with deduplication, that's a ton of disk activity.. probably why your machine hung, no response from the disk.

                   

                  I wouldn't recommend thin provision for anything remotely concerned with IO.  thin is good for static stuff like file servers, and light weight web UI, but not for anything that utilizes defrag.. thin provision can thrash and cause many many extra IO on your SAN.

                  • 6. Re: SQL Server best practices?
                    MaxStr Enthusiast

                     

                    The other problem is that the LUN that the vm's are currently using are set up for 128K block size, thin provisioning, and no dedup. But I have like 30 vm's using it, so I'm not going to change any of those settings :P

                     

                     

                     

                     

                     

                    It's starting to sound way more complicated than I though. I think it'll be easier to just install SQL on a VM on the C: and leave it at that :P

                     

                     

                    • 7. Re: SQL Server best practices?
                      MaxStr Enthusiast

                      I'm going to actually simplify it a little. Rather than assigning the iscsi luns directly to the VM, I'm going to add the luns to the host. Then, I'm just going to add virtual hard drives to the VM, and point them toward the correct luns.