SPC373: Performance Tuning SharePoint 2010

Eric Shupps, the “SharePoint Cowboy”, held an intense session focusing on performance tuning SharePoint 2010. It was filled with live demos and Eric’s usual high energy. It also contained a great amount of helpful tips and tricks for improving performance on your farm installation.

Infrastructure

SharePoint has an array of areas where performance is an issue and bottlenecks occur. The first and obvious issue is database sizes, which can be estimated in advance using the calculation provided in the SharePoint 2010 Deployment Guide:

Formula: Database size = ((D * V) * S) + (10 KB * (L + (V * D)))

Where: D = total number of documents, V = average number of versions, S = average document size, 10kb = average metadata size, L = total number of list items

As an example, given by Eric in the presentation, an application with a million documents, averaging at just 150 KB, with 3 million list items, would give you an estimated content database size of 486.5 GB. Now this is obviously an issue as the recommended maximum size for a content database is 200 GB. The time to take a backup alone of these databases would take some considerable time, not to mention the possibility of making a restore of said database.

To get around this, plan to distribute large amounts of data by creating separate databases for:

• Site collections with large lists
• Large number of subsites
• Intensive read/write operations
• Data isolation (security)

This is without mentioning the service applications within SharePoint. Out of all services, the search database is the one with the greatest potential for growth. The crawl databases can become extremely large, are highly index sensitive, very high I/O and has high transactional volume. To overcome these issues:

• Isolate crawl and temp databases for the search service on separate disks or separate SQL instances!
• Distribute databases across spindles and LUN’s
• Use the highest performance disk that you can find for the job

For databases in general, it is not enough to just create these from SharePoint and then leave them alone. For example, the default auto-growth setting on a database is only 1 Mb, causing the database to lock while growing. At 1 Mb, this happens with high frequency. A few recommendations for databases in general:

• Manually configure auto-growth settings
• Defragment indexes on a regular basis
• Limit content DB size per site collection
• Assign disks based on size, volume and sensitivity
• Isolate transaction logs (on separate disk(s))
• Implement regular backup schedule to reduce log file size
• Enforce quotas

Configuration

SharePoint has a variety of caching techniques:

• Page Cache – First request is requested from content database, output is written to memory. Subsequent requests for the same resource is read from memory.
• Disk Cache – File-system objects are cached by IIS
• Object Cache – Commonly requested objects are stored in memory, cross-site/cross-list queries are cached in memory
• IIS Compression – IIS reduces size of files transmitted, saving bandwidth. Turned on by default but set at level 0, i.e. no compression. Can be set to 0-9, where level 6-7 is standard for best balance between compression and CPU usage for the WFEs.

Issues:

• Query controls on pages should be cached. This includes all objects that iterate data, such as navigation, content queries, listings, delegates, search security trimmings.
• Pages that are customized by SharePoint Designer become “Ghosted”, i.e. stored as a whole in the database. This causes on average a 10% increase in load time on standard out-of-the-box pages.

In a demo, Eric showed a load test of 100 simultaneous users where the standard average load time of the page was 4.5 seconds. After enabling blob cache, object cache and IIS compression the load time went to an average of 0.5 seconds.

More Information

SharePoint Server 2010 Capacity Management: Software Boundaries and Limits

http://technet.microsoft.com/en-us/library/cc262787.aspx

Capacity Management and Sizing Overview for SharePoint Server 2010

http://technet.microsoft.com/en-us/library/ff758647.aspx

Capacity Planning for SharePoint Server 2010

http://technet.microsoft.com/en-us/library/ff758645.aspx

Performance Testing for SharePoint Server 2010

http://technet.microsoft.com/en-us/library/ff758659.aspx

Storage and SQL Server Capacity Planning and Configuration

http://technet.microsoft.com/en-us/library/cc298801.aspx

Performance and Capacity Technical Case Studies

http://technet.microsoft.com/en-us/library/cc261716.aspx

Monitoring and Maintaining SharePoint Server 2010

http://technet.microsoft.com/en-us/library/ff758658.aspx

Performance Testing for SharePoint Server 2010

http://technet.microsoft.com/en-us/library/ff758659.aspx

1. SPC223: Deploying SharePoint 2010 in Private, Public and Hybrid Cloud Architectures The conference sessions has had a few ups-n-downs but there...
2. Windows 7 Deployment  Earlier this week I made available on the blog my...