Any seasoned SharePoint consultant (except the consultant referred to above) will tell you SQL is the heart of SharePoint. Yes that finicky collaboration product we all love relies heavily on SQL Server. So if your SQL Cluster isn’t healthy, you’re going to feel the pain.
Aside from settings you should apply when configuring SQL Server there are maintenance tasks you should carry out monthly during your maintenance windows:
- Checking database integrity
- De-fragmenting indexes
- Setting fill factor
- Pre-grow databases
Note SharePoint health check looks after these via timer jobs but a sanity check makes sense if you have checked everything else.
The following are some general guidelines:
- Physical Servers
- Four (4) Core small medium and eight (8) core large.
- 64 GB RAM up to 5 TB 128-256 10 TB and larger
- Multiple NICs for SharePoint traffic, operational traffic such as backups and monitoring
- Multiple drives for TempDB, SharePoint Search DBs and Content DBs
- Dedicated of SAN storage that can support 2 IOPs / GB and is aggressively monitored if highly utilized
If your in a situation where your still running into issues with performance, here are some ideas to help:
- Implement RAID 10
- Make sure you have enough drives to handle the I/O (Work with vender to model)
- Design the SAN for 2 IOPs / GB
- Isolate the TempDB on a dedicated disk channel (Your fastest disk)
- Isolate the Transaction logs on a dedicated disk channel (Your next fastest disk)
- Isolate the Content DBs on a dedicated disk channel
- Set your index fill factor to 80%
- Pre-grow your databases (200GB)
- Leave MAXDOP at 1
- If you dont want to pre-grow, set autogrow in MB (e.g. 250mb)
- Isolate traffic (Client, Inter-farm Operations) using dedicated NICs and VLANs
- Use 64k blocks (Windows and SAN) – Make sure the SAN and NTFS are formatted with the same block sizes
- If your SAN(s) are highly utilized consider Dedicated SAN or DAS
- Anti virus exclusions – the following should be excluded: mdf, ndf, ldf, bak, trn, dif, and trc. All SQL Server data and log directories on shared storage should be excluded as well. Additionally, the Quorum drive and the C:\Windows\Cluster directory should be excluded.
- Caching https://technet.microsoft.com/en-us/library/cc261797.aspx
- Make sure you have experienced SQL and SAN administrators
- Know who your sharing infrastructure with
- Stagger long running jobs so they dont overlap
If you’re not a DBA, the following guides will help you:
- For those just learning SQL Server, refer to this link for learning materials – http://msdn.microsoft.com/en-us/library/ms174173.aspx.
- Improving performance – https://technet.microsoft.com/en-us/library/2009.08.insidesharepoint.aspx
- SharePoint 2013 specifics – http://www.itprotoday.com/microsoft-sql-server/configure-sql-server-2012-sharepoint-2013
- Database Maintenance for SharePoint Products – http://technet.microsoft.com/en-us/library/cc262731.aspx
- Database Maintenance Best Practices – http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx
- Top Tips for Database Maintenance – http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
- SQL Best Practice for SharePoint – http://mscerts.programming4.us/sharepoint/Optimizing%20SQL%20Server%20for%20SharePoint%202010%20%28part%201%29.aspx
- SQL I/O problem troubleshooting – http://blogs.msdn.com/b/sqljourney/archive/2013/06/04/how-to-troubleshooting-sql-server-i-o-bottlenecks.aspx
- SQL I/O Monitoring – https://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/
- PAL – How to interpret the counters http://blogs.msdn.com/b/askjay/archive/2011/07/08/troubleshooting-slow-disk-i-o-in-sql-server.aspx
Many large organizations utilize a shared services model to optimize IT cost structures. Specifically, SQL Server is a share service that support the organizations DB needs. Generally the environment isn’t optimized for SharePoint, is highly leveraged and the SQL team isn’t funded / staffed to support the specifics of each application SQL supports. In this case its important to provide the SQL with SharePoint specifics and to do your and ask for proof to answers to remove any doubt. Also, don underestimate the level of organization push back, I had to face Directors and the like head on as they chose to not comply with fact finding exercises and took the heat for it.