SharePoint stores most of the data in SQL Server. Stress on SQL servers causes stress on front-end servers, which ultimately degrades the user experience. Healthy SQL Server = Healthy SharePoint! So, here are the best practices for SQL Server with SharePoint 2010. Whether SQL Server 2005/2008/2012 or any other versions doesn’t matter, these best practices are applicable in common.
SQL Server Best Practices for SharePoint
Pre-Growing & Auto growth
- Pre-grow content databases to approximately their estimated size – This helps reduce the impact of fragmentation on performance.
- Auto growth should be left ON for safety. Do not rely on the default auto-growth settings. The recommended auto growth increment setting is: 10% of the DB. E.g., for a 50GB content database, you would configure an increment of just over 5GB (or small DBs let the growth size be 256MB or 512MB)
- What if you don’t do it? If you upload a 100MB file and are using default auto-growth settings(1 MB), your content database needs to grow by an amount equal to 100 times your growth increment to accommodate your file!
Data Files Placement
- Format SQL Server data/log disks to 64k allocation size Because SQL server reads and writes 64k at a time (By default, it: 4K. Use “chkdsk d:” in the command prompt to check the current allocation size.)
- Distribute data files across separate disks. Use same-sized multiple data files for Temp DB, Content & Search DBs. E.g. Use 2 data files on a 2-core processor and 4 data files on 4-core systems (Let the Max no. of data files be: 8)
- More disks = more performance. Separate the Log & Data files into different spindles. Data files should be spread onto separate LUNs using unique spindle sets. Database log files for all content databases should be placed onto a single shared LUN with its own isolated spindle set.
- Place DB files in this Order: Search DBs: Read/write optimized, Temp DB & T-Logs: write-optimized, Content DBs- Read optimized. In other words, (Fastest to Slowest): Temp db – Transaction Logs – Search DB data files- Content DB data files
- Place SharePoint Search Crawl and Query Processing DB’s in separate spindles
- Volumes that will host the SharePoint content database should be on RAID 10 logical units when available. Otherwise, RAID 5 volumes can be used as an alternative
- The Tempdb is the most read/write-intensive database used during search operations and sorting of views. It should be pre-sized to 25% of the predicted size of the largest content database.
Model Database Settings
Every SharePoint database that you create inherits most of its database settings from the system DB called Model, such as the initial size of the primary data file, the initial size of the transaction log, the recovery model, etc. Problems – The default initial size is small, the Auto growth is small, and the file path is the default path is C:\. So, modifying the Model DB property after SharePoint installation is important.
Configuration, Central Administration Content, and Service Applications Content Databases
- These databases are rarely used and do not need multiple data files.
- These database data and log files can all reside on a single spindle.
Min & Max Memory settings:
- Min & max memory settings: Set min = max = (Total Physical Memory – OS Memory – Other applications Memory). Or the easy way: let 3 to 4 GB over for the OS. How about Page file size? OS memory x 1.5.
Remember that when you set the Min Server Memory setting, SQL may not utilize that memory. It will only consume the minimum amount of memory once it needs to.
- Do not shrink the databases or Auto-shrink or Configure a maintenance plan that programmatically shrinks your databases.
- If it must be done, shrink a database only when 50% or more of the content in it has been removed by user or administrator deletions, and you do not anticipate the unused space being re-used by more data. It’s recommended that you shrink only content databases.
- Shrinking databases is an extremely resource-intensive operation. Therefore, if you absolutely must shrink a database, carefully consider when you schedule the shrink operation.
- If you need to recover space, Databases and database files can be shrunk manually by executing the DBCC SHRINKFILE and DBCC SHRINKDATABASE statements.
- If you are looking to recover the space in a quick manner, Set the recovery model to SIMPLE, execute the DBCC SHRINKFILE, and then set the recovery model back to FULL.
Other Best Practices and Database Maintenance
- Use SQL alias – This is extremely helpful when you have to point to some other SQL server using DNS change during some urgency or migration.
- Modification to SQL server SharePoint db’s is not supported by Microsoft J
- Externalize BLOB Data – Store big files outside the database. SharePoint 2010 supports RBS (Remote Blog Storage)
- Install only needed components and features. For example, the Filestream feature is not used in SharePoint.
- Set the Recovery model to FULL for all DBs, except Tempdb, which should be “simple”.
- To prevent the transaction log from growing too large, schedule a transaction log backup after a full database backup. Make sure “Truncate on Backup” is active! Related post: Truncate and Shrink SharePoint Transaction Logs, Databases
- Use Fail-over clustering with database mirroring (which is obvious!)
- Configure NTFS allocation unit size 64k is best. (Default is: 4k). To view CHKDSK <drive>
- Verify these capacities: RAM > 32 GB, Processor L2 cache > 2 MB, Bus bandwidth: High, Disk latency (msec) <10 (for data) <5 for T-logs, network speed in Gigabyte, Network latency: <1. Use a dedicated network adapter for SQL-SharePoint communication.
Not just configurations, but its a best practice to have a regular maintenance plan in place on SQL Server in SharePoint deployments
- Set up maintenance plans at regular intervals: daily, weekly, monthly, and Quarterly. Typically Check for database integrity, rebuild the index, and perform Maintenance cleanup tasks.
- Exclude your database files (MDF, LDF) location from Anti-Virus.
- Have a regular process to do: DBCC CHECKDB – Database consistency checks should be performed at least once per week on your SharePoint 2010 databases whenever events such as database server or I/O subsystem failures occur. Monitor SQL Server performance regularly – user DMVs, SQL Server data collectors, Perfmon, etc.
- Don’t rely solely on SharePoint timer jobs to perform statistics and index maintenance. Create a good SQL maintenance plan. You can use the procedure outlined in KB 943345 https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 to help along with the white paper on SharePoint database maintenance https://technet.microsoft.com/en-us/library/cc262731.aspx
Indexes, IO, De-fragment, and Fill factor:
- De-fragmenting indexes by either reorganizing them or rebuilding them (applies mainly to indexes; fragmentation can be reduced by setting a large database start size).
- Re-organize or Rebuild the indexes by analyzing: DMV sys.dm_db_index_physical_stats. Reorganize When Fragmentation <10%, and rebuild when it is >75%. Perform an index rebuild after long-running operations or massive migrations of data. Use the procedure outlined in KB 943345 https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15
- Turn on page checksums. Checking database integrity (ensures that your data and indexes are not corrupted)
- De-fragment physical files. Data fragmentation inside of SQL Server can usually be explained by the normal course of data manipulation, including inserts, updates, and deletes.
- Turn OFF Auto_create_statistics. Disable Auto-update statistics as well, as SharePoint runs a daily timer job that updates the statistics for you!
- Use SQLIO.exe to measure I/O performance. Recommended Disk seconds per transfer: data files <10 msec. T-Log: <5 msecs.
- Fine-tuning index performance by setting fill factor, SharePoint databases work best with an 80% fill factor. To view the fill factor value of one or more indexes, query the sys.indexes catalog view. But setting fill factor will occupy more space. Set the default fill factor in server properties.
- To determine if we have the appropriate amount of RAM allocated to your SQL Server instance, we can monitor the following two SQL Server performance counters:
On the SharePoint side:
In Addition to SQL server best practices for SharePoint 2010, There are certain aspects on the SharePoint side also to look into:
- Pay attention to Maximum limits. Refer https://docs.microsoft.com/en-us/sharepoint/install/software-boundaries-and-limits for recommendations such as:
- Avoid more than 2000 site collections in the same database
- Although SharePoint supports content DB size to 400 GB (in sp1), keep the size between 25 GB-100 GB. Because a larger db would require more time for backup/restore, performance issues, can cause increased maintenance windows.
- Keep the number of Content databases per web application < 100; otherwise, it will degrade the web application performance (SharePoint 2010 supports 300 Content Db’s)
- Use a dedicated SQL Server for SharePoint. Devote SQL server only to SharePoint, Don’t use a Shared one with other applications.
- Use a dedicated database for large site collections: > 50 GB
- Set “Quotas” in SharePoint at the web application level
- Set the Max Degree of parallelism to 1 for the SQL Server instance.
- Use SharePoint column indexes on columns that are used to sort and filter views of large lists or libraries
- Use PowerShell to create databases, So that you avoid GUIDs in the database names
References for SQL Server best practices in SharePoint Deployments:
- Database Maintenance for Microsoft SharePoint 2010 Products https://www.microsoft.com/download/en/details.aspx?id=24282
- Storage and SQL Server capacity planning and configuration https://technet.microsoft.com/en-us/library/cc298801.aspx