Category Archives: Performance

Improving SharePoint performance using SQL Server settings

SharePoint performance is a recursive problem and preoccupation. As a Database Administrator, we have to deal with SharePoint when configuring SQL Server databases.

In this article, I will propose a list of best practices in SQL Server settings aimed to reduce SharePoint performance issues.


Do not keep the default value which is 1 MB. We can illustrate with a simple example why this is a bad idea.

When a document of 5 MB is uploaded, it means there are 5 Autogrowth which are activated. In fact, there are 5 allocations of space which must slow your system.

Moreover, your uploaded document will be fragmented across your different data files. This configuration will decrease your performance a second time.

To avoid performance issues and reduce fragmented data files, you should set the autogrowth value to a fixed number of megabytes.

My recommendation is 1024 MB for data files and 256 MB for log files. But keep in mind, this is a global recommendation. In fact, the bigger the database, the bigger the growth increment should be.

SQL Server disk cluster size

The default value of SQL Server is 4 KB. But in fact, it is nearly the worst value you can choose for this configuration!

Globally, 64 KB is a safe value. Indeed, the server reads 64 KB at the time and can deliver larger chunks of data to the SQL Server database.

TempDB Optimization

First, the TempDB recovery model should be set to simple. Indeed, this model automatically reclaims log space to keep space requirements small.

Also, you should put your TempDB on the fastest disks you have, because TempDB is heavily used by SharePoint. Do not let SQL Server use this disk for any other needs, except TempDB utilization!

Furthermore, each TempDB file should be 25% larger than the largest content database. Not many DBAs realize how a TempDB is used by SharePoint and to what extent a TempDB can grow!

Index Fragmentation

WSS_Content database, for example, is used to store site collection as well as lists and its tables are shared. Therefore, indexes are very important!

So do not forget to manage the fragmentation of your databases.

My recommendation is to perform a Reorganize when your fragmentation is between 10% and 30 % as well as a Rebuild index when your fragmentation is above 30%.

Take care about indexes with more than 1’000 pages!


Do not enable Auto-Create Statistics on an SQL Server that supports SharePoint Server! Let SharePoint Server configure the required settings alone.

Auto-Create Statistics can significantly change the execution plan of a query from one instance of SQL Server to another.

Therefore, do not enable Auto-Update Statistics and use instead SharePoint Auto-Update capability instead.

SQL Server Memory Allocation

The default values of SQL Server for memory allocation are 0 MB for Minimum server memory and 2147483647 MB for Maximum server memory.

The default value of the Maximum server memory is not optimized at all!

You should set a custom value depending on the total amount of physical memory, the number of processors, and the number of cores.

To calculate your SQL Max Memory, I suggest you to read this article.

Recycle Bin

Be aware that items in the recycle Bin may affect the performance.

Moreover, after a certain limit of days or after a deletion, these items are moved to a second stage recycle bin that may also affect your performance.

As a result, you have to manage your recycle bin depending on your needs to ensure that the size of your recycle bin will not continue to grow out of control.


The default value of your MAXDOP is 0. But for better performance, you should make sure that a single SQL Server process serves each request.

Therefore, you must set MAXDOP to 1.

Fill Factor

The default value is 0, which is equal to 100. It means that you do not provide space for index expansion.

But when a new row is added to a full index page, the Database Engine make a reorganization called Page Split.

Page Split can take time to perform, and can cause fragmentation increasing I/O operations.

I recommend to set a Fill Factor value of 80. It means that 20 % of each-level page will be left empty.

Therefore, you can support growth and reduce fragmentation.

Instant File initialization

This feature, when enabled, allows SQL Server to initialize database files instantly, without physically zeroing out each and every 8K page in the file.

Therefore, depending on the size of files you have, you can save a lots of time.


The default settings of the content database in SQL Server are pretty bad and far from what we really need. You should always opt for a pre-allocate size strategy and not rely on autogrowth.

Monitoring your databases for space and growth to avoid bad surprises is very important.

Also, do not forget to modify your model database for size allocation rules.

Ans if you do not want to suffer from bad performances, do not use the Auto-Shrink capability.


Accounts used by application pools or service identities are in the local machine Administrators group SharePoint

Rule Name:  Accounts used by application pools or service identities are in the local machine Administrators group.

Summary:  A user account that is used by application pools or services must have permissions of a domain user account and must not be a member of the Farm Administrators group or a member of the Administrators group on the local computer. Using highly privileged accounts for application pools or services poses a security risk to the farm, and could allow malicious code to execute.

Cause:  Accounts that are used by application pools or services are members of the Administrators group on the local computer.

Resolution: Change the user account to a predefined account, or to a domain user account that is not a member of the Administrators group.

  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators group.
  2. On the Central Administration home page, in the Security section, click Configure service accounts.
  3. On the Service Accounts page, in the Select the component to update list, click the application pool or service that uses the credentials of a member of the Administrators group on the local computer as its security account.
  4. In the Select an account list, click an appropriate account for this component — for example, the predefined account Network Service — or click Register new managed account, and then on the Register Managed Account page, specify the credentials and the password change settings that you want.
  5. Click OK.

Cache Monitoring SharePoint 2013

SharePoint 2013 provides three types of caches that help improve the speed at which web pages load in the browser: the BLOB cache, the ASP.NET output cache, and the object cache.

  • The BLOB cache is a disk-based cache that stores binary large object files that are used by web pages to help the pages load quickly in the browser.
  • The ASP.NET output cache stores the rendered output of a page. It also stores different versions of the cached page, based on the permissions of the users who are requesting the page.
  • The object cache reduces the traffic between the web server and the SQL database by storing objects such as lists and libraries, site settings, and page layouts in memory on the front-end web server. As a result, the pages that require these items can be rendered quickly, increasing the speed with which pages are delivered to the client browser.

Monitoring consists of regularly viewing specific performance monitors and making adjustments in the settings to correct any performance issues. The monitors measure cache hits, cache misses, cache compactions, and cache flushes. The following list describes each of these performance monitors.

  • A cache hit occurs when the cache receives a request for an object whose data is already stored in the cache. A high number of cache hits indicates good performance and a good end-user experience.
  • A cache miss occurs when the cache receives a request for an object whose data is not already stored in the cache. A high number of cache misses might indicate poor performance and a slower end-user experience.
  • Cache compaction (also known as trimming), happens when a cache becomes full and additional requests for non-cached content are received. During compaction, the system identifies a subset of the contents in the cache to remove, and removes them. Typically these contents are not requested as frequently.
    Compaction can consume a significant portion of the server’s resources. This can affect both server performance and the end-user experience. Therefore, compaction should be avoided. You can decrease the occurrence of compaction by increasing the size of the cache. Compaction usually happens if the cache size is decreased. Compaction of the object cache does not consume as many resources as the compaction of the BLOB cache.
  • A cache flush is when the cache is completely emptied. After the cache is flushed, the cache hit to cache miss ratio will be almost zero. Then, as users request content and the cache is filled up, that ratio increases and eventually reaches an optimal level. A consistently high number for this counter might indicate a problem with the farm, such as constantly changing library metadata schemas.

You can monitor the effectiveness of the cache settings to make sure that the end-users are getting the best experience possible. Optimum performance occurs when the ratio of cache hits to cache misses is high and when compactions and flushes only rarely occur. If the monitors do not indicate these conditions, you can improve performance by changing the cache settings.

The following sections provide specific information for monitoring each kind of cache.

Monitoring BLOB cache performance

You can monitor the effectiveness of the cache settings by using the performance monitors that are listed in the following table.

SharePoint Publishing Cache counter group

Counter name Ideal value or pattern Notes
Total Number of cache Compactions 0 If this number is continually or frequently high, the cache size is too small for the data being requested. To improve performance, increase the size of the cache.
BLOB Cache % full >= 90% shows red>= 80% shows yellow

<80% shows green

This can show that the cache size is too small. To improve performance, increase the size of the cache.
Publishing cache flushes / second 0 Site owners might be performing actions on the sites that are causing the cache to be flushed. To improve performance during peak-use hours, make sure that site owners only perform these actions during off-peak hours.
Publishing cache hit ratio Depends on usage pattern. For read-only sites, the ratio should be 1. For read-write sites, the ratio may be lower. A low ratio can indicate that unpublished items are being requested, and these cannot be cached. If this is a portal site, the site might be set to require check-out, or many users have items checked out.

Note :

For the BLOB cache, a request is only counted as a cache miss if the user requests a file whose extension is configured to be cached. For example, if the cache is enabled to cache .jpg files only, and the cache gets a request for a .gif file, that request is not counted as a cache miss.

Monitoring ASP.NET output cache performance

You can monitor the effectiveness of the cache settings by using the performance monitors that are listed in the following table.

ASP.NET Applications counter group

Counter name Ideal value or pattern Notes
Cache API trims 0 Increase the amount of memory that is allocated to the ASP.NET output cache.
Cache API hit ratio Depends on usage pattern. For read-only sites, the ratio should be 1. For read-write sites, the ratio may be lower. Potential causes of a low hit ratio include the following:

  • If you are using anonymous user caching (for example, for an Internet-facing site), users are regularly requesting content that has not yet been cached.
  • If you are using ASP.NET output caching for authenticated users, many users may have edit permissions on the pages that they are viewing.
  • If you have customized any of the VaryBy* parameters on any page (or master page or page layout) or customized a cache profile, you may have configured a parameter that prevents the pages in the site from being cached effectively (For example, you might be varying by user for a site that has many users).

Note : 

For the ASP.NET output cache, all pages are cached for a fixed duration that is independent of user actions. Therefore, there are flush-related monitoring events.
For more information about the ASP.NET output cache, see Output Caching andCache Profiles ( or cache Element for caching (ASP.NET Settings Schema) (

Monitoring object cache performance

  • The object cache is used to store metadata about sites, libraries, lists, list items, and documents that are used by features such as site navigation and the Content Query Web Part.
  • This cache helps users when they browse to pages that use these features because the data that they require is stored or retrieved directly from the object cache instead of from the content database.

  • The object cache is stored in the RAM of each web server in the farm. Each web server maintains its own object cache.

  • You can monitor the effectiveness of the cache settings by using the performance monitors that are listed in the following table.

SharePoint Publishing Cache counter group

Counter name Ideal value or pattern Notes
Total number of cache compactions 0 If this number is high, the cache size is too small for the data being requested. To improve performance, increase the size of the cache.
Publishing cache flushes / second 0 Site owners might be performing actions on the sites that are causing the cache to be flushed. To improve performance during peak-use hours, make sure that site owners perform these actions only during off-peak hours.
Publishing cache hit ratio Depends on usage pattern. For read-only sites, the ratio should be 1. For read-write sites, the ratio may be lower. If the ratio starts to decrease, this might be caused by one or more of the following:

  • The cache was recently flushed or compacted.
  • Users are accessing content that was recently added to the site. This might occur after lots of new content is added to the site.

Site slow taking long time querying sharepoint

What you can do if SharePoint is sometimes very slow.

E.g.: on the first start of a Site

Sometimes during the day a search query will take about a minute until you get results…..

  1. Just look on that article:

it will improve your “feeled” performance (site response times) massive, if you’re going to implement both solutions.

  1. Disabling CRL Check is just necessary if the SP Server does not have internet connectivity! -> that means proxy settings must be configured for the server itself, and your proxy must allow traffic from the server of course.

Performance Test SharePoint


Why Performance Testing ?

If you work on a team responsible for the delivery of SharePoint into your organization there's usually a mountain of tasks to complete before making it live. One of the most vital tasks is to make sure that SharePoint is "fast" enough to meet the requirements of your users. If you get performance problems after going live this can easily ruin all of your hard work and - even worse - your users will think SharePoint sucks!

So, by the time you "go live" you should be confident of the following :

  • You know the maximum load your service can take.
  • You know your service can run for a pro-longed period of time.
  • You know the point at which you may need to add more servers, so you can plan.
  • When you do get to maximum load you need to know where the bottle necks in your service are, (e.g. SQL, Network, SP servers).

If you don't know these metrics up front, then you are going to have to react as problems occur. If you have to react it will be probably be done under extreme pressure, usually because your boss is yelling at you!

So how do you find out these performance metrics ?

One of the best ways to discover how well your environment can cope is to carry out some performance tests using a tool that will emulate the load and expected journeys of your user base.

User Journeys

Not every user will use SharePoint in the same way. You will have some users that download a document, some that will publish documents, some that will use Search, or even some that will use be 100% socialites. In the testing world these usage patterns can be mapped onto something called a User Journey. To get started with Performance Testing, you will need to know what these journeys are likely to be and also what the mix will be. You can either take an educated guess ("guestimate") on what these journeys will be or even better, run a pilot on a good cross-section of staff before going live. You can then use the actual usage data from the pilot phase and feed that into a typical user journey. Both SharePoint analytics and the IIS logs will give you all the data you need.

Once you have this data you can then list all of the types of journeys,

e.g. :

"Casual Browser"

  • Log-in
  • Hit the home page
  • Do a search
  • Read a document

Another journey may be :

"The Social User"

  • Log-in
  • Hit the home page
  • Go to their MySite (some pre-provisioned, some not)
  • Add a status update
  • Add a forum post
  • Like a document

Visual Studio Performance Testing

If you have Visual Studio Ultimate, you can take advantage of the testing tools that ship with it. There are a few others such as Load Runner, but they can be pricey. If you take time to learn Visual Studio test tools you will soon be able to perform adequate tests. The other great side effect is that you also are functionally testing your application. So once you automate your testing, you can use it to repeatedly test your app.

Once really cool thing about Visual Studio Testing is that it's really easy to record a user journey by simply using the browser to use SharePoint. Once you have completed the journey it will be saved as a web test so that it can automatically be reused under load in your Performance Tests.

To read more about Visual Studio Testing tools go here.

Visual Studio Test Agents

For large implementations, it's impossible to emulate anything more than 50 users from 1 machine (aka Test Agent). You will be constrained by the machines CPU, Memory or Network I/O. To increase the load you need to install the Visual Studio Test Agent service on more than 1 machine. As an example, to emulate 600 concurrent users I used 10 machines to spread the load.(It's quite easy to see when a machine is constrained as VS collects performance data from the agent and things go RED.)

Once configured, each Test Agent, will emulate several users using SharePoint. Once the Agent has the performance data they will log it to a common SQL Server where it can be analysed.

Note! You don't need to buy specific machines for this, you should consider using your existing developer machines. It's VERY likely you will be doing this kind of testing out of hours, so it's silly not to use them.

What performance data to you need to gather ?

To understand what to look for does require a good understanding of machine architecture and performance counters. However, Visual Studio really helps as it has a pre-recorded set of "important" counters. Each counter has a pre-defined threshold, so it will be obvious if there is a problem. For example, if your SQL Server CPU hits 95% this be will clear to see.

The main things to you want to record for each type of test are :

  • Test Duration
  • WFE’s in Load
  • Agents used
  • Avg. User Load
  • Pages / Sec
  • Avg. Page Time (Sec)
  • Requests / Sec
  • Requests Cached %
  • Avg. Response Time

Please note, you should gather performance data from all servers involved in providing your SharePoint service. This includes application servers, WFE's, SQL Servers and also non SharePoint servers. There could be a bottleneck in any one of these that can cause knock on effects and resulting slow performance. 

Always test customised code.

Microsoft have already tested standard SharePoint functionality. If there were performance issues they would already be well known and probably fixed. However, Microsoft cannot test any customizations (i.e. code) you have done. Make sure anywhere you have new code (e.g web parts, event receivers, custom pages ect), you include it under load. Custom code is the most likely cause of performance issues as it's unlikely your developers will have tested it at the loads it will be used at.

Having said that, you still want to test standard SharePoint as you may have under powered kit. I am just saying pay special attention to custom code.

Vary the users, browsers and network mix.

Visual Studio allows you to log-in as different types of users. Do this. Different types of users can cause different paths of code to fire. You may have personalisation features, or different security models that come into play based on user.

You can also emulate different browsers and networks. Try and match your organization closely.

What type of tests do you need to perform ?

The type of tests you can carry out will generally fall into one of these types :

Goal based Test

The intention of the Goal-based test is to identify the number of page / requests that can be served while the WFE’s are running at around 70% utilisation. The test runs for 10 minutes and readjusts load based on CPU utilisation.

Soak Test

The intention of this test is to hit the production farm at about 50 – 75% of expected peak Usage over long periods of time. This will hopefully identify specific problems that are time related, such as memory leaks, or scheduled tasks that disrupt service.

Stepped Tests

The purpose of this test is to gradually ramp up the User load to find the point at which response rates start to fall away.

Constant Load Tests

This type of testing hits the servers with a constant load that is expected at peak performance.

What are the performance steps ?

In summary, here's the high-level steps that you need to go through :

  1. Identify your user journeys.
  2. Records your user Journeys using Record and Play with a browser and Visual Studio.
  3. Create Test Scenarios in Visual Studio - (Vary the users, time, browsers, networks, journeys).
  4. Configure "Test Agents" to increase the amount of load.
  5. Run the Tests.
  6. Analyse the results.
  7. Publish findings and make recommendations.

The Sample Report 

This sample performance testing report is a 25 page report taken from an actual SharePoint 2010 farm serving 15,000 users. The Farm has 2 App Servers and 4 WFE's. If you need to do Performance Testing in your organization, you may wish to use it as a reference point.