As more and more companies continue to use the platform, Microsoft SharePoint has become a prominent collaboration tool for many organization. SharePoint is not only used to replace other collaboration tools, but also legacy content repositories such as file shares. However, this kind of change is not easy for everyone, and employees will need to get used to their new SharePoint portal. In order to convince all employees that they can work better together on the platform, SharePoint has to be both user friendly and fast.

However, when you begin to type “Why is SharePoint…” into your favorite search engine, the first automated result you will see is, “Why is SharePoint so slow?” Clearly, many organizations are facing SharePoint performance challenges.

SharePoint content databases have to satisfy a variety of tasks, including search, Enterprise Content Management (ECM), workflows, collaboration (co-authoring), and more. If your content databases are not properly maintained, these operations can have a negative impact on your performance. One reason for this dependency is because SharePoint content databases are developed to work well without high configuration requirements. That means you can simply install the platform and it works. Unfortunately, simply following this “just install” approach avoids the best practice configuration settings. If you don’t change this setup, SharePoint will begin to perform slowly.

To better understand SharePoint content database settings, you will need to know five standard configurations and how to change them. With all of these configurations set properly, you can speed up your SharePoint performance up to four times.

1. MAXDOP Value
2. Memory Allocation
3. AutoGrowth Settings
4. TempDB Settings
5. Cluster size on SQL drives

MAXDOP
The Maximum Degree of Parallelism (MAXDOP) limits how many CPU cores can be used simultaneously in order to complete queries. Hence, the more cores, the faster Microsoft SQL Server performs. This works well for real database queries, because SQL can allocate queries very efficiently.

Nevertheless, it becomes complex if you include SharePoint in this context. Why? Because SharePoint optimizes the queries before it sends them to SQL. The additional effort required by SQL optimizing and allocating the queries will make the entire framework inefficient. Thus, the optimal value for this setting is “1” so that all queries will be completed as they come from SharePoint.

Memory Allocation
 Are you familiar with the number 2,147,483,647? This is the memory value in megabytes (MB) that SQL is allowed to use at a maximum.

SQL utilizes a large amount of memory and does not care if there is any left for the operating system (OS). Consider the following scenario: SQL takes so much memory, that even the OS is not able to complete requests and threads, and therefore the server hangs. The CPU usage and Disc I/O increase heavily, because Windows starts to offload more and more RAM to the drive. In doing this, the response times extend and SharePoint becomes slow or goes down entirely. You may have to restart the database server in order to solve the problem.

To avoid this scenario, the maximum memory allocation value should be configured in a way that always leaves some space for the OS. For small server deployments, 80-85 percent of maximum available memory is a recommended setting. If there is a large amount of memory available [more than 64 gigabytes (GB)], then you should reserve around 8 GB for the OS.
The minimum server memory allocation is generally not problematic. However, I recommend setting this value to 1-2 MB. In doing so, SQL runs more stably and responds quicker to operations.

AutoGrowth Settings

Bad:

Good:

Unfortunately, the AutoGrowth standard settings are also not optimal. Due to an initially small database size and small automated increasing steps, data cannot be written continuously into extents and pages. This happens for all INSERT, UPDATE, and DELETE actions. The result is a fragmentation, which leads to bad performance.

In addition, server resources are used for each AutoGrowth process. For changes based on a percentage value, the server even has to perform calculations. These are all additional operations that SQL has to carry out before it can work on the real SharePoint queries. Hence, it is easy to understand that we can influence performance a great deal through the AutoGrowth setting.

In the ideal scenario, all databases are already pre-configured (by the database administrator) for the SharePoint installation. The initial sizes should roughly represent the expected size of the corresponding databases. However, this does not have to be the recommended maximum size from Microsoft, because a large database will also influence the backup size and time. Nevertheless, you should be able to run your SharePoint databases without size changes for a couple of weeks.

If you eventually reach the maximum size, the AutoGrowth operation will start. Depending on the database, the incremental AutoGrowth operation should have a static number and not a percentage value. This setting should cover around 10 percent of the (initial) database size. As an example, if you have a 10 GB content database, you should configure the AutoGrowth value to 1 GB. (For databases with an expected size of more than 100 GB, you can configure even larger incremental AutoGrowth steps.) While configuring these settings, do not forget the log-file. You can use the same recommendations for log-file settings.

TempDB Settings
As a point of comparison, you can think of the TempDB of SQL as being similar to the RAM memory of the OS. This database saves temporary user objects like tables, procedures, and internal objects. As an example, it saves interim results and row versions for data change transactions. The TempDB will be created from scratch for each SQL initiation. Saved data will be deleted when the SQL connection stops.

With this RAM memory analogy in mind, it is easy to understand that optimizing the TempDB will also improve entire SQL performance and therefore also speed up SharePoint. There are some opportunities where TempDBs can be optimized:

– Set Recovery Model to SIMPLE

– Split up the TempDB into various data files
o up to eight logical cores: #cores = #TempDB-files
o for more than eight cores, configure exact eight TempDB-files
o if you detect In-Memory-Contention, increase TempDB-files by four until conflicts reach a normal level
*Note: a general guidance says #cores = ½ to ¼ TempDB-files
o all TempDB-files should have same size and same settings
o each file should be around 25 percent of the largest content DB
o you can also allocate the files across various storage spindles for an even better performance
*Note: since all additional data files are SECONDARY files per definition, please use the format *.ndf for them instead of *.mdf as you have for the initial PRIMARY file

– Use the fastest drives for the TempDBs

– There should be at least 25 percent of free space available for the TempDB drive. This is necessary to ensure a file growth during peak hours (use a similar AutoGrowth setting as described for the content DBs).

SQL Drive Cluster Size
Did you install your SQL databases on a standard NTFS partition and are now encountering performance issues? The cluster size of the corresponding storage partition could be the reason for that. Did you know that the standard cluster size for creating an NTFS drive is 4 kilobytes (KB)? This is another good example of standard configurations that do not work properly for SQL.

SQL prefers a cluster size of 64 KB. This is due to how SQL manages the storage of its data files (MDF or NDF). The storage will be split logically into pages that are 8 KB in size. This is the basic unit for data savings in SQL. For an even more efficient storage management, 8 physical coherent pages will be collected as extents. The resulting calculation is: 8 pages x 8 KB = 64 KB.

If you have performance issues, please also consider locating your data files on a drive with 64 KB cluster size. Even if you are using a virtual machine within the Infrastructure as a Service (IaaS) model through Microsoft Azure, you can realize an improved drive performance of approximately 20 percent. Thus, you should also consider this option if you want to improve your SharePoint performance.

All these mentioned settings show that SharePoint itself is not always to blame for slow performance. Moreover, you don’t always need to buy additional and better hardware in order to improve the platform performance. Alternatively, you should not always use the standard configurations of SharePoint-related technologies (SQL, Windows Server, etc.). It is better to configure the optimal settings for an optimal performance. Ultimately, better performance can help you increase the user acceptance and adoption of SharePoint.

Happy “SharePointing”!  Robert Mulsow

AvePoint offers integrated tools to improve SharePoint performance while reducing storage costs. DocAve Storage Manager, part of the award-winning DocAve Software platform, effectively mitigates the negative consequences of exponential content growth by implementing and enforcing BLOB externalization strategies throughout SharePoint’s lifecycle.

About the author AvePoint:

AvePoint is the established leader in enterprise-class big data management, governance, and compliance software solutions for next-generation social collaboration platforms. Founded in 2001 and headquartered in Jersey City, NJ, AvePoint serves over 13,000 organizations worldwide. AvePoint is privately held and backed by Goldman Sachs and Summit Partners. www.avepoint.com.