In an ideal world, you would account for SharePoint performance optimization in the planning and design stages with adequately sized and architected servers, support teams, and underlying infrastructure. But in the real world, you'll have trouble predicting user adoption rates. Your budget may be cut or staff downsized. You may inherit a poorly performing SharePoint environment. Even if your infrastructure at first meets performance expectations, growing numbers of documents, groups, lists, and sites may increase page load times and decrease satisfaction.

One of the biggest challenges you'll face in your efforts to optimize SharePoint performance will be navigating through the many configuration options that the underlying IIS, .NET, and SQL Server technologies provide during the planning and design stages, as well as in hands-on operation. The sheer number of options is daunting, not to mention trying to figure out which option is best suited to your needs. For example, SQL Server houses the vast majority of SharePoint configuration data and content, yet the search, content, configuration, and temp databases have very different read/write patterns that require appropriate disk throughput and RAM. To complicate the picture, you also can use caching in IIS or offload indexing to a front-end server to help increase disk throughput.

A second challenge revolves around determining the root causes of performance issues. SharePoint relies not only on the core SQL, IIS, and .NET components, but also on interdependencies such as Active Directory, the network, SharePoint architecture, and physical server hardware. This means a performance issue may have more than one root cause, and similarly require making multiple changes for problem resolution. Operational jobs, backup routines, and third-party tools add more possible root causes to performance issues.

In this column, I present an overview of key SharePoint architecture components, describe how they can lead to common performance issues, and discuss how to resolve and troubleshoot problems.

Performance Criteria

Before I go into the relationships between IIS and SQL Server design, configuration options, and impact on performance, let's establish the target of performance optimization. Put simply, it is improved user and administrator experience in terms of key indicators such as page load times, search, and crawling. If pages don't load fast for your users, then your effort to optimize performance by eliminating 10 round trips to the SQL Server databases doesn't matter.

When considering how quickly a page appears for a user, be sure to think about initial and subsequent load times. You might have instances in which users load a single page once but, generally, SharePoint use involves people accessing many sites and document libraries repeatedly. That's why focusing on opportunities that yield decreased load times for all page requests is so important. Keep in mind that because of browser caching, the first time a page loads the render time will be different than it is for subsequent page loads.

Performance Interdependencies

In my May 2008 column, "Building Your SharePoint Infrastructure", I covered the SharePoint architecture and explained at a basic level how IIS, SQL Server, and .NET work together to render requested pages. Now let's consider how to configure the core technologies to meet your performance needs. Figure 1 shows the key components that relate to optimization options.

In the basic request scenario, the ASP.NET page parser services the incoming request, once it's authenticated, and renders the result to the browser. The underlying content includes data from the file system and SQL Server content databases, such as list items, binary large objects (BLOBs), graphics, and text. Even submitting content to a blog or news page with a few Web parts requires verification of appropriate permissions, the page parser to compile the ASP.NET page, and multiple trips to the SQL Server for reading and writing to and from the temp, transaction logs, and content databases.

Performance issues can happen at any point in this process. For example, if the page contains many small artifacts, such as images, and your environment uses Windows NT LAN Manager and a remote domain controller (DC), then the full page will load slowly because HTTP GET requests require a round trip to the DC. The Windows NT LAN Manager authentication architecture is the constraint, not IIS or SQL Server operations. Similarly, a user might request a page with thousands of list items or import many items from a spreadsheet to a list, affecting the load times for all other users accessing sites housed on the same SQL Server.

Operational tasks and background processes, such as resource-intensive nightly backups, also can influence performance. Nightly backups can cause problems for business users in global environments that operate 24 hours every day. Background tasks impact performance because they strain system resources. For example, scheduled timer jobs, database cleanup tasks and, especially, indexing and crawling processes use large amounts of disk I/O, CPU, and RAM on front- and back-end servers.

Resolving Performance Issues

Regardless of the framework and methodology you use in trying to understand your SharePoint performance issues, isolate their root causes, and resolve the problems, you need baseline data that reflects acceptable performance against which you compare data that reflects poor performance. You can obtain a baseline set of performance data either right after your initial deployment, during periods of acceptable performance, or from best-practice recommendations. If you have no basis for comparison, then you can use Microsoft published performance recommendations for various measurements, available at technet.micro-soft.com/en-us/library/cc262787.aspx.

As I already mentioned, the most common performance issues happen as a result of interrelated components. This can be troublesome because the underlying indicators are often the same. For example, one common indicator of a performance issue is a spike in resource utilization, such as RAM, CPU, or disk I/O. To resolve the performance issue, you need to look at all available data, understand the sequence of events leading to the issue, and correlate events to determine the underlying root cause. Figure 2 shows common performance issues, possible root causes, and resolution options.

Do not use SharePoint Team Services Administration (STSADM). Use SQL backup, DPM, Litespeed, or SQL 2008 with compression, ensure fill factor is set to 70% on content databases, enforce 100GB growth limit.

You can rely on a diverse range of tools for digging down to the specifics of a SharePoint performance issue and gathering evidence that would help you make a diagnosis, determine the root causes, and resolve the problem.

The following tools are especially helpful in pinpointing causes of performance issues:

WireShark When you need to look into network issues, use WireShark. It works with many media, and you can capture packets from recreated TCP/IP conversations when you recreate issues. For more information, see wireshark.org.

Visual RoundTrip Analyzer (VRTA) You can use VRTA to examine the round-trip performance from request to response. VRTA examines the communications protocol, identifying the causes of excessive round trips, and recommending solutions. You can download it from Microsoft downloads, Visual Round Trip Analyzer.

SQL Profiler You can use this useful tool, installed with SQL Server, for monitoring an instance of SQL Server Database Engine or SQL Server Analysis Services. It enables you to discover issues with queries, deadlocks, timeouts, recompilations, and general errors and exceptions.

SQLDiag This tool, also installed with SQL Server, collects valuable information about the configuration of the computer running SQL Server, the operating system, and the information that is reported to the SQL Server error logs.

SQL Query Analyzer This is a low-level debug tool for analyzing query performance issues. It also is part of the SQL Server toolset.

Custom tool for client-based URL ping This is one of the most useful tools because it enables the comparison of statistics before and after implementing configuration changes to the environment. See the appendix on the SharePoint Performance Optimization page.

Constant Monitoring

SharePoint performance tuning, like most things SharePoint, is complex. You need to understand the object model, details about the SharePoint architecture, and the interactions between IIS/.NET and SQL Server. You also need to know database administration and troubleshooting best practices. The good news is that if you lack this understanding, you can still do well at optimizing your SharePoint infrastructure by following established best practices, recommendations, and knowledge, and addressing the common issues pointed out here.

Pav Cherny is an IT expert and author specializing in Microsoft technologies for collaboration and unified communication. His publications include white papers, product manuals, and books with a focus on IT operations and system administration. Pav is president of Biblioso Corporation, a company that specializes in managed documentation and localization services.