SharePoint SQL Server Performance Tuning Roundup

1. Blocking

2. System resource contention

3. Application design problems

4. 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:

1. 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.

2. 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.

3. 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:

1. BIOS updates

2. SAN drivers (if using a SAN)

3. NIC firmware

4. NIC driver

Disable hyper-threading Hyper-threading should be turned off for SQL Server computers because applications that can cause high levels of contention (such as SharePoint) may cause decreased performance in a hyper-threaded environment on a SQL Server computer.

Defragment all disks on a regular basis Excessive disk fragmentation in the SQL Server will negatively affect performance. Defragment all disks (local and SAN/NAS) on a regular basis by scheduling off-hours disk defragmentation. Defragment the Windows PageFile and pre-allocate the Master File Tables of each disk in the BizTalk Server environment to boost overall system performance. Use the PageDefrag Utility (http://go.microsoft.com/fwlink/?LinkId=108976) to defragment the Windows PageFile and pre-allocate the Master File Tables.

Synchronize Time on All Servers Many operations involving tickets, receipts and logging rely on the local system clock being accurate. This is especially true in a distributed environment, where time discrepancies between systems may cause logs to be out of sync or tickets issued by one system to be rejected by another as expired or not yet valid. For more information on configuring a server to automatically synchronize time, see Configure a client computer for automatic domain time synchronization (http://go.microsoft.com/fwlink/?LinkId=99420).

Disable real-time scanning of data and transaction files Real-time scanning of the SQL Server data and transaction files (.mdf, .ndf, .ldf, .mdb) can increase disk I/O contention and reduce SQL Server performance.

Review disk controller stripe size and volume allocation units When configuring drive arrays and logical drives within your hardware drive controller, ensure you match the controller stripe size with the allocation unit size that the volumes will be formatted with. This will ensure disk read and write performance is optimal and offer better overall server performance. Configuring larger allocation unit (or cluster or block) sizes will cause disk space to be used less efficiently, but will also provide higher disk I/O performance as the disk head can read in more data during each read activity. To determine the optimal setting to configure the controller and format the disks with, you should determine the average disk transfer size on the disk subsystem of a server with similar file system characteristics. Use the Windows Performance Monitor tool to monitor the Logical Disk object counters of Avg. Disk Bytes/Read and Avg. Disk Bytes/Write over a period of normal activity to help determine the best value to use. Although smaller allocation unit sizes may be warranted if the system will be accessing many small files or records, an allocation unit size of 64 KB delivers sound performance and I/O throughput under most circumstances. Improvements in performance with tuned allocation unit sizes can be particularly noted when disk load increases.

Monitor drive space utilization The less data a disk has on it, the faster it will operate. This is because on a well-defragmented drive, data is written as close to the outer edge of the disk as possible, as this is where the disk spins the fastest and yields the best performance. Disk seek time is normally considerably longer than read or write activities. As noted above, data is initially written to the outside edge of a disk. As demand for disk storage increases and free space reduces, data is written closer to the center of the disk. Disk seek time is increased in locating the data as the head moves away from the edge, and when found, it takes longer to read, hindering disk I/O performance. This means that monitoring disk space utilization is important not just for capacity reasons but for performance also. As a rule of thumb, work towards a goal of keeping disk free space between 20% to 25% of total disk space. If free disk space drops below this threshold, then disk I/O performance will be negatively impacted