Tuning SQL Server for SharePoint

SharePoint is highly dependent on SQL Server.  For optimal performance and in fact even to use SQL at all, there are some settings that are required and some that are suggested.
  1. Collation
    1. SharePoint databases require Latin1_General_CI_AS_KS_WS.  It will not let you create a database if the collation is otherwise.
    2. Your best bet is to use a separate instance of SQL for SharePoint and set the server default configuration to this collation during install (the only time you can set the server default).
    3. If you do not have this collation set, just make sure to set this collation when you create a new blank database for use with SharePoint.
  2. Instance
    1. It is recommended to create a separate instance of SQL for SharePoint and not to share other applications with this database.
  3. Disks
    1. RAID 10
      1. Use this setting for all SQL server disk arrays on a production farm.
      2. This could be modified slightly if using Solid State (i.e. using a pair of SSD drives in RAID 1 for tempdb may be sufficient versus cost of the enterprise SSDs that can run $1,000 to $3,000 each)
    2. Priority (Fast to slow I/O) - each should be on its own disk(s)
      1. tempdb (data and log files)
      2. Transaction Logs
      3. Search Database data files
      4. Content Database data files
    3. Use multiple data files for Content and Search databases.
      1. Distribute Equally-Sized Data Files Across Separate Disks (if able).
      2. Number of Data Files Should Be <= Number of Processor Cores.
      3. Multiple Data Files Not Supported for Other databases.
  4. Memory
    1. Set the maximum SQL server memory to only use what you need.  This may be the server memory minus 4GB for the host, less any other service that is running like SSRS.
    2. Or Use this formula:
      1. SQL Max Memory = TotalPhyMem - (NumOfSQLThreads * ThreadStackSize) - (1GB * CEILING(NumOfCores/4))
      2. NumOfSQLThreads = 256 + (NumOfProcessors*- 4) * 8
      3. ThreadStackSize = 2MB on x64 or 4 MB on 64-bit (IA64)  (*  If NumOfProcessors > 4, else 0)
    3. Set the minimum to at least half of the maximum.  Or use a number that you know it should have through historical memory usage.
  5. Temp DB
    1. Set the file quantity to equal the number of processors.
    2. Ensure the Tempdb size is approximately 10% of entire corpus of data, or all of the content databases size combined.
  6. Config DB
    1. Set the recovery model to Simple.
  7. Use Technet article for SP2010 database maintenance:
    1. http://technet.microsoft.com/en-us/library/hh133453(v=office.14).aspx
  8. Fill Factor
    1. For SharePoint, Microsoft recommends using a server-wide setting of 80.
  9. Recovery Model
    1. All important databases, anything but master, tempdb, and SharePoint Configuration, should be in Full recovery model.
    2. Check here for a great explanation of the models, their loss exposure, and pros/cons.
      1. http://msdn.microsoft.com/en-us/library/ms189275.aspx
    3. To recovery a loss of a database in Full Mode:
      1. http://msdn.microsoft.com/en-us/library/ms187495.aspx
      2. This means that after a checkpoint is created and transactions from the log are written to the mdf data file, these transactions are kept in the log until the next transaction log backup is performed.
  1. Avoiding Huge Transaction Logs (Truncate)
    1. Need to know when we care less about the entries in the transaction log.
    2. After a full backup (successful), perform a transaction log backup to truncate the log.  You could even delete this transaction log backup because one is already included in the latest backup.
    3. This only recovers you from a data file loss or corruption.  To really save you from a log corruption or loss, you need to backup the transaction log frequently, perhaps every hour even.
  2. Backups
    1. Primary - Retain a local copy on disk for Quick Recovery.
      1. These may stay here until the next backup is performed.
      2. The length of time to retain these depends on your disk available space.
      3. The backup location should definitely be on different disks or storage arrays as your log and data files.
    2. Secondary - Local Copy not on Local System.
      1. Think about storing your local copies in another system, not in the same server room or even building, but locally accessible quickly in case you lose a server or a room.
    3. Tertiary - Remote Copy
      1. Distance away from primary site depends on natural disasters. Hurricane, tornado, earthquake, etc will determine how far away the remote backup should be distanced.
    4. Size
      1. Consider size of backups and length of time to transfer the backup.
      2. Most restores happen within a 3-week or 21-day window.
      3. Use large slow disks in RAID 5.  1-4TB, 5400 or 7200 rpm.

Fragmentation - Reports in SQL Server Management Studio

  1. Right-Click on a content Database, Select Reports flyout, Standard Reports, Index Physical Statistics.
  2. Expand the partitions and note the fragmentation percentages.
  3. It will advise operations, usually Rebuild.


Popular posts from this blog

SharePoint Designer 2013 Approval Workflow with Comments

Change SharePoint server hostname and Web Application Names

SharePoint Search - Content Processing Pipeline Failed to Process the Item