System resource contention
Application design problems
Queries or stored procedures that have long execution times
Apply Filegroups for the Search DB's The whole goal of using filegroups is to improve the performance of the system. This is done by providing an additional file. This file must be placed on a different set of spindles to see any kind of performance enhancement. If your SQL machine is not IO bound for the Search database then implementing filegroups will not provide you with any benefits. http://blogs.msdn.com/b/enterprisesearch/archive/2008/09/16/sql-file-groups-and-search.aspx
Configure Blob Cache in the SharePoint web.config The BLOB cache is disk-based caching that increases browser performance and reduces database loads. When you open a web page for first time, the files will be copied from the database to the cache on the hard drive on SharePoint server and then all subsequent requests to this site will be accessed from the local hard drive cache instead of issuing a resource intensive request to the SQL Server database. “enable” attribute to “true”. It is strongly recommended to store the cache on a dedicated partition, which isn’t a part of the operating system (C: partition is not recommended).]
Manage Index Fragmentation As data is modified in a system, pages can split, and data can become fragmented or physically scattered on the hard disk. Contrary to popular belief, Microsoft SQL Server is not a self-healing system. Use the DBCC SHOWCONTIG command to see the density and the degree of fragmentation for an index for a table. The SQL Fool Index Defrag Script (http://sqlfool.com/2011/06/index-defrag-script-v4-1/) is a great tool for dealing with SQL fragementation.
Locate Logs and the Tempdb Database on Separate Devices from the Data You can improve performance by locating your database logs and the tempdb database on physical disk arrays or devices that are separate from the main data device. Because data modifications are written to the log and to the database, and to the tempdb database if temp tables are used, having three different locations on different disk controllers provides significant benefits.
Provide Separate Devices for Heavily Accessed Tables and Indexes If you have an I/O bottleneck on specific tables or indexes, try putting the tables or indexes in their own file group on a separate physical disk array or device to alleviate the performance bottleneck.
Pre-Grow Databases and Logs to Avoid Automatic Growth and Fragmentation Performance Impact If you have enabled automatic growth, ensure that you are using the proper automatic growth option. You can grow database size by percent or by fixed size. Avoid frequent changes to the database sizes. If you are importing large amounts of data that tend to be of a fixed size on a weekly basis, grow the database by a fixed size to accommodate the new data. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth. Based on past performance and index expansion rates, the SharePoint Operations team reccommends the database fill factor to 70 percent on all content databases.
Maximize Available Memory Use performance counters to decide the amount of memory that you need. Some performance counters that you can use to measure your need for memory are listed below:
The SQLServer:Buffer Manager:Buffer cache hit ratio counter indicates that data is retrieved from memory cache. The number should be around 90. A lower value indicates that SQL Server requires more memory.
The Memory:Available Bytes counter shows the amount of RAM that is available. Low memory availability is a problem if the counter shows that 10 megabytes (MB) of memory or less is available.
The SQLServer:Buffer Manager: Free pages counter should not have a sustained value of 4 or less for more than two seconds. When there are no free pages in the buffer pool, the memory requirements of your SQL Server may have become so intense that the lazy writer or the check pointing process is unable to keep up. Typical signs of buffer pool pressure are a higher than normal number of lazy writes per second or a higher number of checkpoint pages per second as SQL Server attempts to empty the procedure and the data cache to get enough free memory to service the incoming query plan executions. This is an effective detection mechanism that indicates that your procedure or data cache is starved for memory. Either increase the RAM that is allocated to SQL Server, or locate the large number of hashes or sorts that may be occurring.
Install the latest BIOS, storage area network (SAN) drivers, network adapter firmware and network adapter drivers Hardware manufacturers regularly release BIOS, firmware, and driver updates that can improve performance and availability for the associated hardware. Visit the hardware manufacturer’s Web site to download and apply updates for the following hardware components on each computer in the BizTalk Server environment:
SAN drivers (if using a SAN)