locked
RAID Level Configuration Best Practices RRS feed

  • Question

  • Hi Guys ,

       We are building new Virtual environment for SQL Server and have to define RAID level configuration for SQL Server setup.

    Please share your thoughts for RAID configuration for SQL data, log , temppdb, Backup files .

    Files  RAID Level 

    SQL Data File -->

    SQL Log Files-->

    Tempdb Data-->

    Tempdb log-->

    Backup files--> .

    Any other configuration best practices   are more then welcome . 

    Like Memory Setting at OS level , LUN Settings. 

    Best practices to configure SQL Server in Hyper-V with clustering.

    Thank you


    Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah

    Friday, June 20, 2014 5:12 PM

Answers

  • SQL Data File(s) --> Raid 10 or Raid 5

    SQL Log File(s) --> Raid 1

    Tempdb Data --> Raid 10 or Raid 5

    Temdb log --> Raid 1

    Backup files --> Whatever meets your requirements for disaster recovery

    My personal other "best practices"

    1. Never run a production SQL Server in a virtual environment.
    2. Memory - make sure the service account has the lock pages in privilege and manually designate all available RAM for SQL Server other than about 4GB for the OS.  Unless you have far more RAM than you need for SQL Server and that's just about never the case.
    3. Hyper-V with clustering - NO.  Whomever came up with this idea never managed a SQL Server in a busy environment where many variables were completely beyond the control of the DBA.
    4. LUN settings - Isolate data files on separate independent disks connected to independent controllers or ports.  The same goes for log files.  Be ready to add additional independent LUNS for additional data files should you find you have excessive contention.
    5.  MAXDOP - if you have more than 4 cores available then set the MAXDOP at the server level (sp_configure) to 4 and be prepaid to tweak the setting.

    • Marked as answer by -kit Friday, June 20, 2014 6:37 PM
    Friday, June 20, 2014 6:07 PM
  • Hi,

    If you can shed some bucks you should go for RAID 10 for all files. Also as a best practice keeping database log and data files on different physical drive would give optimum performance. Tempdb can be placed with data file or on a different drive as per usage. Its always good to use dedicated drive for tempdb

    For memory setting.Please refer This link for setting max server memory

    You should monitor SQL server memory usage using below counters taken from this Link

    1. SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR): IIf your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again
    2. SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.  
    3. SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.  
    4. SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.  
    5. SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx
    6. SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
    7. SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

    For other settings I would like you to discuss with vendor. Storage questions IMO should be directed to Vendor.

    Below would surely be a good read

    SAN storage best practice For SQL Server

    SQLCAT best practice for SQL Server storage


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by -kit Friday, June 20, 2014 6:45 PM
    Friday, June 20, 2014 6:37 PM

All replies

  • SQL Data File(s) --> Raid 10 or Raid 5

    SQL Log File(s) --> Raid 1

    Tempdb Data --> Raid 10 or Raid 5

    Temdb log --> Raid 1

    Backup files --> Whatever meets your requirements for disaster recovery

    My personal other "best practices"

    1. Never run a production SQL Server in a virtual environment.
    2. Memory - make sure the service account has the lock pages in privilege and manually designate all available RAM for SQL Server other than about 4GB for the OS.  Unless you have far more RAM than you need for SQL Server and that's just about never the case.
    3. Hyper-V with clustering - NO.  Whomever came up with this idea never managed a SQL Server in a busy environment where many variables were completely beyond the control of the DBA.
    4. LUN settings - Isolate data files on separate independent disks connected to independent controllers or ports.  The same goes for log files.  Be ready to add additional independent LUNS for additional data files should you find you have excessive contention.
    5.  MAXDOP - if you have more than 4 cores available then set the MAXDOP at the server level (sp_configure) to 4 and be prepaid to tweak the setting.

    • Marked as answer by -kit Friday, June 20, 2014 6:37 PM
    Friday, June 20, 2014 6:07 PM
  • Hi,

    If you can shed some bucks you should go for RAID 10 for all files. Also as a best practice keeping database log and data files on different physical drive would give optimum performance. Tempdb can be placed with data file or on a different drive as per usage. Its always good to use dedicated drive for tempdb

    For memory setting.Please refer This link for setting max server memory

    You should monitor SQL server memory usage using below counters taken from this Link

    1. SQLServer:Buffer Manager--Buffer Cache hit ratio(BCHR): IIf your BCHR is high 90 to 100 Then it points to fact that You don't have memory pressure. Keep in mind that suppose somebody runs a query which request large amount of pages in that case momentarily BCHR might come down to 60 or 70 may be less but that does not means it is a memory pressure it means your query requires large memory and will take it. After that query completes you will see BCHR risiing again
    2. SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.  
    3. SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.  
    4. SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.  
    5. SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article: http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx
    6. SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.
    7. SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.

    For other settings I would like you to discuss with vendor. Storage questions IMO should be directed to Vendor.

    Below would surely be a good read

    SAN storage best practice For SQL Server

    SQLCAT best practice for SQL Server storage


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    • Marked as answer by -kit Friday, June 20, 2014 6:45 PM
    Friday, June 20, 2014 6:37 PM
  • Thanks you very much Ed for detailed information this is really helpful to me. 

    Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah

    Friday, June 20, 2014 6:37 PM
  • We follow with almost all points as mentioned by Ed, some changes you may think off too.

    Log disk may be Raid 10

    TempDB log disk raid 10 

    Also note, before setting up any environment, you must collect all information from Application\Project team about application behavior and to size the environment properly, like how much RAM, how much CPU and core.

    Also how many concurrent users peak offpeak hours, HA is required or not, and if then what mode of operation would be.

    Somewhere people say NFR document wherein all these details are gathered and maintained.

    Also if this setup needs to be for some existing environment then setup some monitoring perfmon counters and get data for IOPS, CPU, Memory usage. These information will help you to size your new environment properly.


    Santosh Singh

    Friday, June 20, 2014 6:42 PM
  • IMHO keeping Log file on RAID 1 would make writing slow as compared to RAID 10. Data needs to be mirrored and would not be much good in terms of capacity as its half due to mirroring.

    Still I would say RAID 10 even RAID 5 has been seen to have slower writing speed for log files. But it altogether depends on write produced by transaction log if its heavy and log file durability is high priority you should go for RAID 10 if you have average write request for log files RAID1 is good.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 6:47 PM
  • We are planing to Visualization, Database/server consolidation as well and upgrade . So it will shared server VM or Individual  on two Blades. 

    Any thoughts to have SQL Clustering with Hyper-V or should be totally separate from Hyper-V?

     planning to setup clustering for  Two Blades. 


    Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah

    Friday, June 20, 2014 8:18 PM
  • Any thoughts to have SQL Clustering with Hyper-V or should be totally separate from Hyper-V?

     planning to setup clustering for  Two Blades. 


    Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah

    Hi,

    Clustering on Hyper V is fully supported and widely implemented you can go ahead. Issue or problems always depends on your level of command in SQL Server :)


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 8:39 PM