Data Optimizations in SharePoint

SharePoint mainly concentrates on the following optimization techniques:

·         Infrastructure Optimization

·         Database Optimization

·         Caching Optimization

·         Server Optimization

·         Page Optimization

Infrastructure Optimization:

Search in SharePoint is very memory intensive. It is often the first source of performance headaches.

The better the links to the database are optimized, the better the overall performance will be.

If all the servers (front-end servers and application servers) are behind the same switch, the application servers that run search will be going out through the switch each time an incremental crawl is kicked off. 

Database Optimization

Database Operations:

Different things in SharePoint have different effects on the databases

Order of items by their impact (1 being the biggest killer whereas 10 have the least impact):

·        Performance Point Services

·        Search

·        Content Query

·        Security Trimming (SPSecurityTrimmedControl delegate)

·        Collaboration

·        Workflow

·        Publishing

·        Social

·        Client Access

·        Browsing

Database Size:

Even though Microsoft says that each content database can hold up to 4 TB, the recommended practical limit is 200 GB for easily manageable backup and restores.

Analytics databases grow very quickly to very large sizes. Try to isolate Analytics databases. Analytics reports can have significant impact on CPU load.

Search uses multiple databases for its operations. It uses separate databases for crawl, properties and administration. Crawl databases can be extremely large. Crawl databases also have heavy transactional volumes. Try to isolate temp and crawl databases if possible.

Database Management:

Performing the below steps at the database might result in better performance:

·        Manually configure auto-growth settings. The default auto-growth setting in SQL Server is 1 MB. Set it to 100MB / 200 MB depending on your environment. This allows the database to grow in larger chunks, which is more efficient since these databases tend to grow rapidly.

·        Defragment database indexes regularly.

·        Limit content DB size per site collection.

·        Isolate transaction logs by writing them off onto separate disks.

·        Enforce site collection quotas in Central Administration.

Caching Optimization:

Make sure that all the custom controls / web parts use caching.

SharePoint supports the following types of cache:

1.      BLOB Cache

2.      Output Cache

3.      Object Cache

4.      Branch Cache

Server Optimization:

IIS Compression:

IIS Compression is turned on by default in Windows Server 2008. An important thing to note is that it is just enabled but not configured.  IIS Compression takes all the objects in the site, compresses them and delivers them as smaller packages to the clients.  It can be configured to be set at a level between 0 and 9. By default, it is set to 0 when it is turned on. 9 means lot of pressure on CPU utilization.  We recommend it being set to 6/7/8/9 depending upon your hardware.

Page Optimization:

Optimize Pages:

SharePoint pages contain lot of resources; these can include but are not limited to:

·        JavaScript Files

·        CSS Files

·        Navigation Controls

·        Menus

·        Web Parts

·        Custom Controls

·        Ribbon Control

·        Delegates

·        SPSecurityTrimmedControls

·        Publishing Fields

·        Search Controls

·        Hidden Controls

Customized pages (unghosted pages as they are called in earlier versions) may be easy to develop but they are bad in performance. However, The advantage with customized pages is that they can be created and modified using SharePoint Designer.  When a page is customized in SharePoint and saved, it will no longer be served from file system. Instead a copy of the page will be written into the database and from there on whenever the page is requested; it will be retrieved dynamically from the database.

Thanks Amol for your contribution for this text.