Optimizing SQL Server for SharePoint
User Rating:
/ 0
- Details
- Alexander Zoutenbier
- Created on Thursday, 17 November 2011 15:51
- Hits: 221
For my job at Cadac I get most times performance issues complains because the SQL server is not optimized for SharePoint. In this article you find the most settings for optimizing the SQL server.
You can also download the full PDF document here: Optimizing SQL for SharePoint/Cadac Organice
http://www.zoutenbier.nl/downloads/Optimizing%20SQL%20for%20Cadac%20Organice.pdf
Optimize the SQL Server
- Do not run non-SQL applications on the SQL Server
- Best performance is a dedicated SQL Server to one SharePoint Farm
- If the SQL server is virtualized it will affect the performance
Optimize the SQL Instance
- Adjust database default locations. This can be found in the Database Instance Properties
- Default location is never the system drive C:
- Log files and Data on separate LUN's/physical disks
- Adjust server memory settings if running multiple instances
Optimizing tempdb
- Set to 25% of largest DB
- Use AutoGrow of 100MB if growing, increase initial size
Optimizing Content Database
- Increase size of model DB to ~100MB
- If DB is expected to grow large – pre-size it
- Set autogrow between 50-100MB per file
- Preset log to about 25%
- Set autogrow between 20-40MB
- keep size < 200GB
Optimizing Search DBs
- Max of 25 million items per each crawl and property DB
- Store crawl and property DBs on separate, dedicated LUNs
- Add data files to match Content DBs
- Consider storing on dedicated SQL server
Remote BLOB Storage (RBS)
- Best in file-heavy environments
- Built in RBS support with SQL Server 2008 R2 (FILESTREAM provider)
- implement BLOB caching on WFE
http://technet.microsoft.com/en-us/library/cc261797.aspx
Best Practices
- Use SQL 2008 R2 Enterprise for best performance
- Backup logs regularly to prevent runaway log files
- Do not use autoshrink
- Do not use multiple file groups
- Run database integrity checks
- Use Instant File Initialization
