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 or SQL Server 2008 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. 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 db’s let the growth size to be 256MB or 512MB)
- What if you don’t do it? if you upload a 100MB file and you 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 its: 4K. Use “chkdsk d:” in command prompt to check current allocation size.)
- Distribute data files across separate disks. Use same sized multiple data files for Temp DB, Content & Search DBs. For e.g. Use 2 data files on a 2 core processor, 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 in 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 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, 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 it is important to modify the Model DB property after SharePoint installation.
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.
Keep in mind 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. Its 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 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: 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 back up 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 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
- Setup maintenance plans on regular intervals: daily, weekly, monthly, Quarterly. Typically Check for database integrity, rebuild index, Maintenance cleanup task.
- 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, and 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 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%, 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 usually can 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:
In SharePoint side:
In Addition to SQL server best practices for SharePoint 2010, There are certain aspects on SharePoint side also to look in to:
- 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 a same database
- Although SharePoint support content DB size to 400 GB (in sp1), Keep the size between 25GB-100 GB. Because larger db would require more time for backup/restore, performance issues, can cause increased maintenance windows.
- Keep the number of Content database per web application < 100, otherwise it will degrade the web application performance (SharePoint 2010 supports 300 Content Db’s)
- Use dedicated SQL Server for SharePoint. Devote SQL server only to SharePoint , Don’t use a Shared one with other applications.
- Use dedicated database for large site collections: > 50 GB
- Set “Quotas” in SharePoint at web application level
- Set Max Degree of parallelism to 1 for 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