SharePoint SQL Server Performance tuning

This article will provide tips and tricks regarding SQL performance improvement.

The SharePoint server health is directly connected with the SQL server health. If your SQL server is suffering because of poor performance the impact will be reflected in your SharePoint environment. User experience will be degraded more and more if you are not acting in time.

The top 5 performance killers are:

1. Indexing and crawling

2. SQLSharePoint Backup

3. Profile import

4. Timer Jobs execution

5. Storage configuration

What can be improved in order to have a stable and well running farm?

A. I will start with the topology recommendations:

– Use a dedicated SQL server for your SharePoint farm that is not running any other farm roles or hosting databases for any other application.

– Install SQL Server 64-bit version on a 64-bit operating system.

Memory

– 8 GB is recommended memory for medium size deployments and 16 GB and greater is recommended for large deployments.

– In a virtual environment ideal is to have 24 GB

CPU

– Plan for 2 WFE (dual core)

– Plan for 1 DB proc core

– Scale out beyond 8 processors

– Recommend that the L2 cache per CPU have a minimum of 2 MB to improve memory

– In a virtual environment use 4 vCPU per server

Network

– Up to 1 millisecond (ms) latency

– In a virtual environment

o aggregate multiple NICs on host for the guest networks

o Allocate Passthrough/RDM NICs for best performance

B. The Storage part I will separate it in different areas.

1. First we need to start with database estimation:

– Estimate core storage and IOPS needs – it’s important to achieve the fastest response possible from I/O subsistem http://technet.microsoft.com/en-us/library/cc298801.aspx#Section1

– Storage requirements – 1 GB for Central Administration and 2 GB Configuration db. Over time, the Configuration database may grow beyond 1 GB, but it does not grow quickly — it grows by approximately 40 MB for each 50,000 site collections

– Estimate content database storage

Database size = ((D × V) × S) + (10 KB × (L + (V × D)))

Where:

D = Number of documents

V= Number of non-current versions

S= Average size of documents

L= List items

– Recommended Disk seconds per transfer:

o Data files < 10 msec

o T-log files < 5 msec

2. Second step is database configuration

– We need to separate and prioritize your data among disks. Recommended database file placement priority (fastest to slowest drive):

o Tempdb data files and transaction logs

o Database transaction log files

o Search databases, except for the Search administration database

o Database data files

– The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size. Also tempdb should be to be at least 10% of total Content db size, or the size of the largest table. Additional information regarding temp_db configuration in this article http://sharepointboco.com/sharepoint-tempdb/

– When using Enterprise Search, place SharePoint Search crawl & query processing tables on separate disks.

– Use dedicated database for large Site Collections (> 50GB)

– Limit the content DB to 100GB for MOSS2007 and to 200 GB for SPS2010

– Externalize BLOBs. Additional information in this article http://sharepointboco.com/performance-and-blob-storage/

C. Maintenance

– Monitor SQL server performance. Additional information in this article http://sharepointboco.com/troubleshooting-performance-issues-sharepoint-2010/

– Execute DBCC CHECKDB – Checks the logical and physical integrity of all the objects in the specified database- additional information http://msdn.microsoft.com/en-us/library/ms176064.aspx

o use REPAIR_REBUILD option to fix errors

o REPAIR_ALLOW_DATA_LOSS not supported

Note: Execute these queries during non-peak hours

– Avoid database shrink operations – shrinking data files is not recommended unless the content database has lost at least half of its content

Thank you for reading this article!

Sources of this article:

http://technet.microsoft.com/en-us/library/cc263261(office.12).aspx Planning and Monitoring SQL Server Storage for Office SharePoint Server: Performance Recommendations and Best Practices (white paper)

http://technet.microsoft.com/en-us/library/hh292622.aspx Best practices for SQL Server 2008 in a SharePoint Server 2010 farm

http://technet.microsoft.com/en-us/library/cc298801.aspx Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

http://msdn.microsoft.com/en-us/library/ms175527.aspx Optimizing tempdb Performance

http://msdn.microsoft.com/en-us/library/ee410782.aspx Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications

http://technet.microsoft.com/en-us/library/ff758647.aspx Capacity management and sizing overview for SharePoint Server 2010

http://technet.microsoft.com/en-us/library/cc678868.aspx Database types and descriptions (SharePoint Server 2010)

http://programming4.us/database/2924.aspx SharePoint 2010: Maintaining SQL Server in a SharePoint Environment