GD Bloggers

This is the blog site for Microsoft Global Delivery Communities focused in sharing the technical knowledge about devices, apps and cloud.
Visitor Globe
Search Blogs
Tags
Archive
Archives
Options
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

A Crash Course in Optimizing SQL Server for SharePoint

TechNet Blogs » GD Bloggers » A Crash Course in Optimizing SQL Server for SharePoint

A Crash Course in Optimizing SQL Server for SharePoint

Yousef Omar Yousef Omar
2 Dec 2011 5:34 AM
  • 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
Comments
  • Anonymous John
    4 Jan 2012 9:49 PM

    "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?

  • Yousef Omar Yousef Omar
    9 Jan 2012 5:49 PM

    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