Team blog of MCS @ Middle East and Africa

This blog is created by Microsoft MEA HQ near shoring team, and it aims to share knowledge with the IT community.With its infrastructure and development sides,It brings to you the proven best practices and real world experiences from Subject Matter Experts
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

A Crash Course in Optimizing SQL Server for SharePoint

A Crash Course in Optimizing SQL Server for SharePoint

  • Comments 2
  • Likes
      • Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server
      • To ensure optimal performance, it is recommend that you set max degree of parallelism (MAXDOP) to 1
      • To improve ease of maintenance, configure SQL Server connection aliases for each database server in your farm
      • Create a secondary FILEGROUP for each database and mark it as DEFAULT*
      • Only create files in the primary file group for the content database
      • Spread database files on separate disks
      • Use RAID 1 or RAID 10 when possible
      • For collaboration or update-intensive sites, use the following ranking for storage distribution: http://technet.microsoft.com/en-us/library/hh292622.aspx
        1. tempdb data files and transaction logs on the fastest disks
        2. Content database transaction log files
        3. Search databases, except for the Search administration database
        4. Content database data files
      • In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows: http://technet.microsoft.com/en-us/library/hh292622.aspx
  1. tempdb data files and transaction logs on the fastest disks
  2. Content database data files
  3. Search databases, except for the Search administration database
  4. Content database transaction log files
    • Set the autogrow property of database files to a percentage. A general rule of thumb you can use for testing is to set your autogrow setting to about one-eight the size of the file. http://support.microsoft.com/kb/315512
    • Rebuild indices daily
    • Limit a content database size to 200GB

* updated the post as per the guidance from the technet article http://technet.microsoft.com/en-us/library/cc298801.aspx (Storage and SQL Server capacity planning and configuration)

 

Comments
  • "Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server."

    I don't understand this point.  That is enabled by database, not server.  Also, doesn't SharePoint set that by database when it creates them?

  • Hi John,

    Yes, true, this is a database-level option, and it can be set on an index-level as well, and what is meant here is: don't enable auto-create statistics for SharePoint databases, I guess. I went through the list of SharePoint databases for a basic installation and found out that some databases have the option set to True by default and some to False. As per the guidance, what I would do is set this option to False for all SharePoint databases (Service Applications, Content, and Admin) - I believe that the purpose of this is to leave SharePoint manage the statistics on its own - and this is a guess as well. The source of the recommended practice is the technet article technet.microsoft.com/.../hh292622.aspx

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment