SharePoint, the fastest growing product in Microsoft's history, is used to store reams of documents, meaning application performance is a key component for successful SharePoint deployment and adoption. Here are 10 steps to improve the performance of your SharePoint servers.
Step 1: Separate user and database traffic
A common misconception is that servers connected to a high speed network segment will have plenty of bandwidth to perform all required operations. But SharePoint places a tremendous amount of demand on SQL, each request for a page can result in numerous calls to the database, not to mention service jobs, search indexing and other operations.
In order to mitigate the conflict between user and database traffic, connectivity between front-end servers and SQL should be isolated, either via separate physical networks or virtual LANs. Typically this requires at least two separate network interface cards in each front-end web server with static routes configured to ensure traffic is routed to the correct interface. The same configuration may also be applied to application and index server.
Step 2: Isolate search indexing
A typical medium server farm consists of one or more web front-end servers, a dedicated index or application server and a separate SQL database server. Search traffic initiated by the index server must be processed by the same servers responsible for delivering user content. In order to prevent search and user traffic from conflicting, an additional server may be added to the farm, which is dedicated solely to servicing search queries (in smaller environments, the index server may also serve this function). The farm administrator would then configure the search service to perform crawls only against this dedicated server. This configuration may reduce traffic to the web front-end servers by as much as 70% during index operations.
Step 3: Adjust SQL parameters
One quick way to avoid future headaches is to provision the major SharePoint databases on separate physical disks (or LUNs if a storage-area network is involved). This means one set of disks for search databases, one for temporary databases and still another for content databases. Additional consideration should be given to isolating the log files (*.ldf). Although these do not incur the same level of I/O as other files, they do play a primary role in backup and recovery and they can grow to several times the size of the master database files.
Another technique is to proactively manage the size and growth of individual databases. By default, SQL grows database files in small increments, either 1MB at a time or as a fixed percentage of database size (usually 10%). These settings can cause SQL to waste cycles constantly expanding databases, and prevents further data from being written while the databases are expanding. An alternative approach is to pre-size the databases up to the maximum recommended size (100GB) if space is available and set auto growth to a fixed size (e.g. 10MB or 20MB).