Time is Money, slow page load is Time, SharePoint is powerful and user is unhappy, therefore unhappy users are expensive. Pages have to load in less than two seconds to make a happy user theses days. If your Farm is experiencing slow page load time, the cause is usually a combination of multiple complex system interactions. Below is a list, of some of the most common items that can add up to slow page loads.
Limit the number of site collections per content database per the guidelines. A smaller number of site collections in a content database not only benefits efficiency of operations, but can also mitigate the exposure of any locking that may occur within that database to a small subset of users as opposed to a large population. Set your maximum allowable site collection sizing quota to keep your content databases at 100GB GB range, and set max allowable site collection quantity per database to acceptable limits, this will allow for control
It helps if AD is local and not on other side of world. Check latency.
List items – should not exceed 2,000 items in a SP 2007 list or a single folder. Users may be continuously launching many records in large lists and not filtering down to subsets of records and fields.
Pages may contain enough web parts to deteriorate the page load time. Make sure your web parts and any third party web parts are functioning correctly and loading fast and that they take advantage of the object cache to save round trips to the database.
Configure site Output caching for caching your content. See Technet Article Output Caching is configured at the Site Collection Level and can significantly improve the throughput and user response time. This is a Publishing Site feature and uses Layout pages with individual cache control. You can control time to live for cache via web.config, again, only publishing pages though. Use the “Vary by” settings for each language in output cache, one for each language to ensure readers get the language of their browser settings.
Use the BLOB cache to minimize database calls when the content database contains lots of static content (.css files, XML files, HTML files, or attachments in article pages). No frequently changed files should go in the cache and combined with using authentication (not anonymous), this feature can hider performance. Blob cache needs to go to its own drive, not same drive a logs and anything else. It is important to note that the blob cache does not cache any items from the SharePoint root (hive) including css, xml etc., it is designed to cache static items in the Content db for anonymous read only sites not internal sites where content is changing all the time.
Object cache is asp.net http native cache and is on by default when you install SharePoint. Need to ensure to configure super user accounts for SharePoint site object and below before caching will work. SharePoint Object caching, plan for 500k ram.
Configure HTTP compression where possible – do be sure to closely monitor processor utilization where using HTTP compression. A 0-byte file may be returned when compression is enabled on a server that is running IIS see support article here
Run 64-bit servers everywhere for larger data processing chunks, larger address space, etc.
Proactively address garbage collection on 64-bit machines, custom code and other external factors can potentially litter the address space with unintended assemblies and permanent memory allocations.
Consider Kerberos authentication where possible, significantly reducing the number of round trips per page versus NTLM.
Consider WAN acceleration to manage traffic generated by satellite or remote offices and/or data replication scenarios.
Adjust IIS timeout settings to accommodate large file uploads by remote users on slow links, VPN, etc.
use Office SharePoint Server 2007 with SQL Server 2008 with the most recent service pack unless you have a significant business reason to use an earlier version.
We recommend that you deploy an additional server running SQL Server 2008 when you have more than four Web servers that are running at full capacity.
For SQL Server 2008, 4 gigabytes (GB) is the minimum required memory, 8 GB is recommended for medium size deployments, and 16 GB and larger is recommended for large deployments. Other factors that can influence your memory requirements include the following: The use of SQL Server 2008 mirroring., The frequent use of files larger than 15 megabytes (MB).
On the server that is running SQL Server 2008, we recommend that the L2 cache per CPU have a minimum of 2 MB to improve memory.
All servers in the farm should have LAN bandwidth and latency to the server that is running SQL Server 2008 (up to 1 millisecond (ms) latency).
Sometimes someone accidently leaves the debug=true value in their web.config. This is useful during development however, it can have a marked impact on the overall performance of the customer’s site.
Make sure that all servers that are running the Timer Service are in the same time zone and that time zone matches the one of the server, or servers, running the Central Administration Service.
Is the system running on the minimum hardware required? Does it meet the minimum hardware requirements for the operation of our product.
Check for errors in the crawl logs. Users may have broken sites, errors will be logged as the crawler visits each URL on the farm adding the the server load.
List View Lookup Threshold may have been set to a value greater than 8 allowing for slow running List Lookups.
Search index files should not be located on the system drive. The OS and the Index files must be on separate drives.
All servers, Windows Server, SQL Server and SharePoint Server must have matching version information.
The network link should meet latency and bandwidth requirements for SharePoint, this is critical for SharePoint to perform well.
Certain folders may have to be excluded from antivirus scanning when you use a file-level antivirus program in SharePoint, failure to do so can result in sever performance issues.. Read this article to find out details. here
Custom Coding or Branding
In your custom code, when you comment your code if you use this syntax <%comment%> it does not download to client, while other methods do.
When adding images to pages in html, If you use capital in name of gif, it will have to download twice. do not use capitals in your xyz.gif files.
Do not use multiple css files, should have one, to download once, no multiple trips.
Caching is case sensitive, if users enters a request for a page and the entry is in a different case that what the programmer gave for the file, it will fetch a fresh copy and not use the cache.
If using cookies, specify the path and not use the default as it will cause extra round trip to the database.
Do not put files in random areas on WFE disk outside of the applications of SharePoint. It will not be cached. dome developers do this as an easy way to add application pages.
when retrieving a subset of list items use caml query, it is much faster than pulling a large data set and iterating through till you find the one you need.
Database/Disks
Consistently maintaining the databases that host SharePoint can significantly improve the health and performance of your system below are many points to consider.
Size your content databases to the desired size (fixed) as they are created because default configuration is set to grow incrementally at 1MB chunks, causing performance impact each time it grows. For example, if you anticipate a large databases for your planned content, set the fixed size upfront using SQL Server tools. Growing a data or transaction log file by a percentage can cause SQL Server to progressively slow down as a result of progressively larger growth increments.
Carefully consider database distribution – where clustering, databases should be distributed across two or more instances – and with the underlying storage, provide as many spindles as possible to your data LUNs.
Always put SQL Server 2005/2008 on a dedicated server that is not running any other farm roles, or hosting databases for any other application (unless you are deploying your system on a stand-alone server)
Install SQL Server 64-bit version on a 64-bit operating system, unless you have a significant business reason not to.
Ensure that the SQL Server input/output (I/O) channels to/from the disks are not shared by other applications, such as the swap file and Internet Information Services (IIS) logs.
Greater bus bandwidth helps improve reliability and performance. Consider that the disk is not the only user of bus bandwidth — for example, you must also account for network access. The following list provides some best practices and recommendations for optimizing bus bandwidth:
· Greater bus bandwidth provides improved performance in systems that frequently use large block transfers and sequential I/O.
· In smaller servers that use mostly sequential I/O, PCI becomes a bottleneck with three disks. For a small server that has eight disks performing mostly random I/O, PCI is sufficient. However, it is more common for PCI-X to be found on servers ranging from small to very large.
· Greater bus bandwidth is necessary to support a large number of disks.
· The capacity of bus bandwidth might be limited by the topology of the system. If the system uses direct attached disks, the number of slots limits the bus bandwidth capacity. However, for storage area network (SAN) systems, there is no physical limiting factor.
· More expensive servers typically have larger and faster buses. There is often no way to increase the capacity of the buses’ bandwidth without replacing the servers. However, the largest servers are more configurable. Consult with server providers for specifications.
Disk and SAN interfaces – The interfaces you use in your system can affect reliability and performance. Larger drives, all else being equal, increase mean seek time.
More and faster disks or arrays enhance performance. The key is to maintain low latency and queuing on all disks.
For high availability and performance (random read/write), configure your array for RAID 10.
Place the tempdb, content databases, and SQL Server 2008 transaction logs on separate physical hard disks. Placing both data and log files on the same device can cause contention for that device and result in poor performance. Placing the files on separate drives allows the I/O activity to occur at the same time for both the data and log files.
· When prioritizing data among faster disks, use the following ranking:
1. Tempdb data and transaction logs
2. Database transaction log files
3. Search database
4. Database data files
In a heavily read-oriented portal site, prioritize data over logs.
Testing with customer data have shown that Office SharePoint Server 2007 farm performance can be significantly impeded by insufficient disk I/O for the tempdb. To avoid this issue, allocate dedicated disks for the tempdb, that is, the average read operation or the average write operation requires more than 20 ms, you might need to ease the bottleneck by either separating the files across disks, or by replacing your disks with faster disks. For best performance, place the tempdb on a RAID 10 array or use a Solid state Drive (SSD) but it has a shorter shelf life and can burn out under high load in big farms.
TempDB database Size is important. Creating a tempdb file that is too small or leaving the default size set to 8 MB can trigger several auto grow operations during the peak times. As a result, the tempdb file may have to expand constantly. This will affect the performance.
If files must share disks because the files are too small to warrant a whole disk or stripe, or you have a shortage of disk space, put files that have different usage patterns on the same disk to minimize simultaneous access requests.
For improved performance for large content databases and the SSP search database, consider using multiple data files.
In respect to growth potential and peak usage patterns, maintain a level of at least 25 percent available space across disks to allow for growth. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.
To further improve index data storage and performance, use fill factor. When indexes are created or rebuilt, the fill factor value (1-100) determines the percentage of space that can be filled with data on each leaf level page. The remaining space is reserved for future growth. For many situations, the default server-wide fill factor level of 0 (fill each page to 100% full) is optimal. However, for SharePoint, a server-wide setting of 80 is optimal to support growth and minimize fragmentation.
For SharePoint 2007 it is recommended to enable AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS, if disabled, it may require manual statistics update. If one or more indexes with row modification counter exceeds 500+20% of rows, performance issues could be occurring.
For SharePoint 2010 it is recommended to disable AUTO_UPDATE_STATISTICS & AUTO_CREATE_STATISTICS. Enabling the Auto Update stats option can result in serious blocking issues with databases. Microsoft recommends disabling this option and let SharePoint Time jobs take care of it for you. more here
DBCC CHECKDB should be run weekly to look for problems. The following checks are performed: Validate each table’s storage engine metadata, Read and check data index and text pages, Check all inter-page relationships, Check the page header counts. Do not configure it to run any necessary repairs, send errors to logs for analysis. SharePoint does not Support running repair tools. How long can it take, read here What does CHECKDB really do? here
Defrag your indexes, Index Fragmentation exists when the logical ordering of pages in an index does not match the physical ordering of data on the disk. In addition, fragmentation can decrease performance by increasing the number of reads that are necessary to retrieve information. An index that is more than 5 percent or less than 30 percent fragmented should be reorganized. An index that is more than 30 percent fragmented should be rebuilt.
The SQL Server Global Configuration Setting: Max Degree of parallelism (MAXDOP) should be set to 1 for SharePoint environments. see article. You do not have to gradually increase it, If a storedproc needs it, its
defined in the storedproc that the MAXDOP is overridden.
Running the same collation on the SQL instance as SharePoint uses for the databases has a positive
Performance impact. Reason: Whenever SharePoint has to do anything with the temp DB, the data has to be converted to the collation of the temp DB. So when you run the same collation, there is no conversion needed. The Temp DB is always running in the collation of the SQL Instance. Changing the collation of a SQL Instance basically means reinstalling the instance.
Some Disk Performance Counters to Consider
Logical disk interface and/or Physical disk interface can be busy with less than 20 percent idle causing bottlenecks or write responsiveness is very slow with a spike of more than 25 milliseconds.
Heavy average page file reads per second may be high number of pages read from the hard disk into the page file in order to resolve hard page faults. Spike in Pages/sec of greater than 1,000 on the database server. When a system begins paging at a rate of 1,000 pages per second, there is the potential for a performance bottleneck. As a best practice, Microsoft recommends that you ensure paging does not exceed 1,000 pages per second without appropriate justification.
Physical disk queue length is greater than 2ms. When a physical disk’s average queue length exceeds the number of spindles (this number would be one if it truly is a physical disk), there is the potential for a performance bottleneck. As a best practice, Microsoft recommends that the physical disks deployed to any server have an average queue length of no greater than one per spindle.
Physical disk read responsiveness can be slow with spikes of more than 25 milliseconds.
Logical disk’s seconds per read may be high and over 25 milliseconds (0.025) Microsoft recommends as a best practice that logical disk seconds per read are consistently below 25 milliseconds with averages below 15 milliseconds and spikes below 50 milliseconds.
When a logical disk’s idle time measures below 50 percent, there is the potential for performance bottlenecks. As a best practice, Microsoft recommends you ensure that the logical disks deployed to the servers maintain at least 50 percent idle time during production hours. Tools to assist in determining which process or user is utilizing the disk resource can include Process Explorer.
Link to SharePoint 2010 database counter targets by Product Group
Monitor Disk Latency and Analyze Trends
The amount of I/O and latency specific to SQL Server data files can be found by using the sys.dm_io_virtual_file_stats dynamic management view in SQL Server 2008. For more information, see sys.dm_io_virtual_file_stats.
Example to returns I/O statistics for all data and log files (insert DB_ID to get 1db)
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(), 2); GO
Returns this table
| Column name | Data type | Description |
|---|---|---|
| database_id | smallint | ID of database. |
| file_id | smallint | ID of file. |
| sample_ms | int | Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function. |
| num_of_reads | bigint | Number of reads issued on the file. |
| num_of_bytes_read | bigint | Total number of bytes read on this file. |
| io_stall_read_ms | bigint | Total time, in milliseconds, that the users waited for reads issued on the file. |
| num_of_writes | bigint | Number of writes made on this file. |
| num_of_bytes_written | bigint | Total number of bytes written to the file. |
| io_stall_write_ms | bigint | Total time, in milliseconds, that users waited for writes to be completed on the file. |
| io_stall | bigint | Total time, in milliseconds, that users waited for I/O to be completed on the file. |
| size_on_disk_bytes | bigint | Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots. |
| file_handle | varbinary | Windows file handle for this file. |
Recommendations
Follow this article – Information about the Maintenance Plan Wizard in SQL Server 2005 and about tasks that administrators can perform against SharePoint 2007 databases here
Follow this article – Support for changes to the SQL 2005 databases that are used by Office server products and by SharePoint 2007 here
Follow the guidelines for Database maintenance for Office SharePoint Server 2007 (white paper) here
Download the SharePoint 2010 White papers and use them as your guide to ensuring performance on the new Farm Databases, here. Also for 2010, here is how to determine your capacity requirements.
Before you deploy a new farm, we recommend that you benchmark the I/O subsystem by using the SQLIO disk subsystem benchmark tool. For details, see SQLIO Disk Subsystem Benchmark Tool
For detailed information about how to analyze IOPS requirements from a SQL Server perspective, see Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications
Windows Server 2008 R2, the Next Generation TCP/IP Stack brings a number of benefits to improve performance including receive window auto-tuning, compound TCP, improved routing path detection and recovery, and more. See whitepaper available “Enhanced Network Performance with Microsoft Windows Vista and Windows Server 2008“.
Microsoft’s position on antivirus solutions for Microsoft SharePoint Portal Server read more here and how to choose antivirus software to run on computers that are running SQL Server. here
Install antivirus software specifically designed for SharePoint where it scans files as they are uploaded and downloaded, preventing users from accidentally or purposefully introducing infected files into the environment.
Microsoft also recommends running antivirus software on all servers, including servers running SharePoint Products & Technologies.
Note: information in this article is from TechNet and associated White Papers.
Timebars Products 