• Article
  • 42 minutes to read

APPLIES TO: yes-img-132013 yes-img-162016 yes-img-192019 yes-img-seSubscription Edition no-img-sopSharePoint in Microsoft 365

The chapters planning data that we provide contains guidelines to help you program and configure the storage and SQL Server database tier in a SharePoint Server surround. This data is based on testing performed at Microsoft on live properties. Even so, your results may vary based on the equipment you utilize and the features and functionality that you implement for your sites.

Learn about Managing site storage limits for SharePoint in Microsoft 365.

Although tests were not run on SQL Server 2014 (SP1), SQL Server 2016, SQL Server 2017 RTM, or SQL Server 2019 you lot tin use these examination results as a guide to help you plan for and configure the storage and SQL Server database tier in SharePoint Server Subscription Edition, 2019, or 2016 environments. For training about how to configure and tune SQL Server 2012, run across SQL Server 2012 for SharePoint Server 2013. The test results are the same equally in SharePoint 2013.

This document is intended for joint use past SharePoint Server farm implementers and SQL Server database administrators every bit SharePoint Server frequently runs in environments in which databases are managed by separate SQL Server database administrators. It assumes significant understanding of both SharePoint Server and SQL Server.

This commodity assumes that you lot are familiar with the concepts that are presented in Chapters management and sizing for SharePoint Server 2013.

We recommend that yous break the storage and database tier design process into the following steps. These sections provide detailed data most each blueprint step, including storage requirements and best practices:

  1. Gather storage and SQL Server space and I/O requirements

  2. Choose SQL Server version and edition

  3. Pattern storage architecture based on chapters and I/O requirements

  4. Approximate memory requirements

  5. Understand network topology requirements

  6. Configure SQL Server

  7. Validate and monitor storage and SQL Server performance

Get together storage and SQL Server space and I/O requirements

Several SharePoint Server architectural factors influence storage design. The fundamental factors are: the amount of content, enabled features, deployed service applications, number of farms, and availability requirements.

Before you outset to program storage, you should sympathize the databases that SharePoint Server can employ.

In this section:

  • Databases used by SharePoint Server

  • Understand SQL Server and IOPS

  • Judge core storage and IOPS needs

  • Estimate service application storage needs and IOPS

  • Make up one's mind availability needs

Databases used by SharePoint Server

The databases that are installed with SharePoint Servers (Subscription Edition, 2019, or 2016) depend on the service applications that are used in the surroundings. All SharePoint Server environments rely on the SQL Server organization databases. This section provides a summary of the databases installed with SharePoint Servers. For detailed database data, see Database types and descriptions in SharePoint Server.

Some SharePoint Server, SQL Server Database Engine, and SQL Server Reporting Services (SSRS) databases take specific location recommendations or requirements. For information near these database locations, see Database types and descriptions in SharePoint Server. The Quick reference guide: SharePoint Servers 2016 and 2019 Databases, is available to download as either a PDF or Visio file.

The following databases are the SharePoint Server system databases and are installed automatically.

  • Configuration

  • Primal Administration content

  • Content (one or more)

The following listing shows the SharePoint Server service applications that have databases:

  • App Direction Service

  • Apps for SharePoint

  • Business Data Connectivity

  • Managed Metadata

  • PerformancePoint Services

  • Project Server (SharePoint Server 2013 only)

  • Search Service

    • Search Administration

    • Analytics Reporting

    • Crawl

    • Link

  • Secure Shop Service

  • SharePoint Translation Service

  • SQL Server Ability Pivot Service

  • Country Service

  • Subscription Settings Service

  • Usage and Wellness information collection

  • User Profile Service

    • Profile

    • Social Tagging

    • Synchronization

  • Discussion Automation Services

The following list shows the SharePoint Foundation 2013 databases:

  • Configuration

  • Fundamental Administration content

  • Content (one or more)

  • App Management Service

  • Search service application:

    • Search assistants

    • Analytics Reporting (one or more)

    • Clamber (ane or more)

    • Link (one or more)

  • Secure Store Service

  • Subscription Settings Service Awarding (if enabled through Windows PowerShell)

  • Usage and Health Information Collection Service

  • Discussion Conversion Service

If you are integrating farther with SQL Server, your surround may as well include more databases, equally in the post-obit scenario. SQL Server Power Pivot for SharePoint can be used in a SharePoint Server 2016 environment simply if y'all utilize SQL Server 2016 RTM Enterprise Edition and SQL Server 2016 SQL Server Analysis Services (SSAS). If in use, you lot must also plan to back up the Power Pivot application database, and the extra load on the organisation. For more information, download the new Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 white paper. For details about configuring and deploying concern intelligence in a multiple server SharePoint Server 2016 farm, download Deploying SQL Server 2016 PowerPivot and Power View in a Multi-Tier SharePoint 2016 Farm.

The SQL Server 2016 Reporting Services (SSRS) add-in can be used with any SharePoint Server 2016 environment. If you are using the add-in, plan to back up the two SQL Server Reporting Services databases and the actress load that is required for SQL Server Reporting Services.

  • SQL Server 2012 Power Pivot for SharePoint 2013 tin be used in a SharePoint 2013 environs that includes SQL Server 2008 R2 Enterprise Edition and SQL Server Analysis Services. If in apply, you must also plan to support the Power Pivot application database, and the extra load on the organisation. For more information, run across Program a PowerPivot deployment in a SharePoint farm, Ability Pivot - Overview and Learning and Power View - Overview and Learning.

  • The SQL Server 2008 R2 Reporting Services (SSRS) plug-in can be used with any SharePoint 2013 environment. If you lot are using the plug-in, plan to back up the ii SQL Server 2008 R2 Reporting Services databases and the extra load that is required for SQL Server 2008 R2 Reporting Services.

Sympathize SQL Server and IOPS

On any server that hosts a SQL Server example, it is important that the server achieves the fastest response possible from the I/O subsystem.

More than and faster disks or arrays provide sufficient I/O operations per second (IOPS) while maintaining low latency and queuing on all disks.

Yous cannot add together other types of resource, such as CPU or retention, to recoup for slow response from the I/O subsystem. However, it tin influence and cause issues throughout the subcontract. Plan for minimal latency before deployment, and monitor your existing systems.

Before you lot deploy a new subcontract, we recommend that you benchmark the I/O subsystem by using the Diskspd Utility. This tool works on all Windows Server versions with all versions of SQL Server. For more than information, run into Diskspd Utility: A Robust Storage Testing Tool.

Stress testing besides provides valuable information for SQL Server. For information, see Storage Benchmarking with DiskSpd.

For detailed information near how to analyze IOPS requirements from a SQL Server perspective, encounter Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications.

Estimate core storage and IOPS needs

Configuration and content storage and IOPS are the base layer that you must plan for in every SharePoint Server deployment.

Configuration storage and IOPS

Storage requirements for the Configuration database and the Central Administration content database are not large. We recommend that you allocate 2 GB for the Configuration database and ane GB for the Central Administration content database. Over time, the configuration database may grow beyond ane GB. It does not grow chop-chop — it grows by approximately 40 MB for each l,000 site collections.

Transaction logs for the configuration database can be large. Nosotros recommend that you back up the transaction log for the configuration database regularly to force truncation. If y'all are using SQL Server Always On availability groups or database mirroring, yous should also keep the database running in full recovery mode. For more information, run across The Transaction Log (SQL Server).

Tip

If you are non using a SQL Server high availability solution which requires the utilize of the full recovery model, you may consider irresolute the configuration database to the uncomplicated recovery model.

IOPS requirements for the configuration database and Central Administration content database are minimal.

Content storage and IOPS

Estimating the storage and IOPS required for content databases is not a precise activity. In testing and explaining the following information, nosotros intend to help you derive estimates to apply to make up one's mind the initial size of your deployment. However, when your surround is running, we look that yous'll revisit your capacity needs based on the information from your live environment.

For more information about our overall capacity planning methodology, see Capacity management and sizing for SharePoint Server 2013.

Formula to estimate content database storage

The following process describes how to approximately gauge the storage required for content databases, without considering log files:

  1. Employ the following formula to estimate the size of your content databases:

    Database size = ((D × V) × Due south) + (10 KB × (Fifty + (5 × D)))

    Note

    The value, 10 KB, in the formula is a constant that approximately estimates the amount of metadata required by SharePoint Server. If your organization requires significant employ of metadata, y'all may want to increase this abiding.

  2. Calculate the expected number of documents. This value is known as D in the formula.

    How y'all calculate the number of documents will exist determined by the features that y'all are using. For example, for My Sites or collaboration sites, we recommend that you calculate the expected number of documents per user and multiply past the number of users. For records management or content publishing sites, you may calculate the number of documents that are managed and generated by a process.

    If you are migrating from a current system, it may be easier to extrapolate your current growth rate and usage. If yous are creating a new system, review your existing file shares or other repositories and estimate based on that usage charge per unit.

  3. Judge the average size of the documents that you lot'll be storing. This value is known as S in the formula. It may exist worthwhile to estimate averages for different types or groups of sites. The average file size for My Sites, media repositories, and different department portals can vary significantly.

  4. Estimate the number of list items in the surround. This value is known equally L in the formula.

    List items are more difficult to guess than documents. We generally use an estimate of iii times the number of documents (D), but estimation formula this will vary based on how you expect to utilise your sites.

  5. Determine the approximate number of versions. Estimate the boilerplate number of versions any certificate in a library will have. This value will ordinarily exist much lower than the maximum allowed number of versions. This value is known as Five in the formula.

    The value of Five must be above zippo.

As an example, use this formula and the characteristics in the following table to estimate the required storage space for data files in a content database for a collaboration environs. The upshot is that you need approximately 105 GB.

Input Value
Number of documents (D)
200,000
Calculated past assuming 10,000 users times 20 documents
Average size of documents (S)
250 KB
Listing items (L)
600,000
Number of non-current versions (Five)
2
Assuming that the maximum versions immune is x

Database size = (((200,000 x ii)) × 250) + ((10 KB × (600,000 + (200,000 x 2))) = 110,000,000 KB or 105 GB

Note

Efficient File I/O in SharePoint Server is a storage method in which a file is separate into pieces that are stored and updated separately. These pieces are streamed together when a user requests the file. This increases the I/O performance but it unremarkably does not increase the file size. Withal, small files tin can see a small-scale increase in the disk storage that is required.

Features that influence the size of content databases

The following SharePoint Server features can bear on the size of content databases:

  • Recycle bins Until a document is fully deleted from both the start stage and second stage recycle bin, information technology occupies space in a content database. Summate how many documents are deleted each calendar month to determine the effect of recycle bins on the size of content databases.

  • Auditing Audit data tin quickly compound and use large amounts of space in a content database, especially if view auditing is turned on. Rather than letting audit data grow without constraint, nosotros recommend that you lot enable auditing just on the events that are of import to meet regulatory needs or internal controls. Apply the following guidelines to estimate the space that you must reserve for auditing information:

    • Estimate the number of new auditing entries for a site, and multiply this number by two KB (entries more often than not are limited to 4 KB, with an average size of about 1 KB).

    • Based on the space that y'all desire to allocate, determine the number of days of audit logs you want to continue.

Annotation

Part Online Server is the next version of Part Spider web Apps Server. Using Role Online Server with SharePoint Servers 2016, 2019, Subscription Edition doesn't affect the size of the content database. To deploy Office Online Server in your SharePoint Server 2016 farm, see Deploy Office Online Server.

Guess content database IOPS requirements

IOPS requirements for content databases vary based on how your environment is being used, bachelor disk space, and the number of servers that yous take. In general, nosotros recommend that you compare the predicted workload in your environment to one of the solutions that we tested. For more data and tin be applied to newer version of SharePoint, see Functioning and chapters test results and recommendations (SharePoint Server 2013).

In tests, nosotros establish that the content databases tend to range from 0.05 IOPS/GB to around 0.2 IOPS/GB. We also constitute that a best practise is to increase the acme-terminate to 0.5 IOPS/GB. This increased proportion is more than necessary and can be much more than you'll need in your surround. If you lot utilize mirroring, this increased proportion results in much more IO than the primary content databases. Exist aware that the mirrored content databases are never lightweight.

Estimate service application storage needs and IOPS

Afterwards y'all judge content storage and IOPS needs, you must determine the storage and IOPS required by the service applications that are being used in your environment.

SharePoint Server service application storage and IOPS requirements

To estimate the storage requirements for the service applications in the system, you must first be aware of the service applications and how y'all'll apply them. Service applications that are bachelor in SharePoint Server 2016 and that have databases are listed in the post-obit tables. The storage and IOPS data for all of the service applications in SharePoint Server Subscription Edition, 2019, or 2016 remains the aforementioned as in SharePoint Servers 2010 and 2013.

Search service awarding storage and IOPS requirements

Database Scaling Disk IOPS Disk size 10M items 100M items
Crawl
Ane DB per 20M items
SQL IOPS: 10 per a DPS
Medium/Loftier
Medium
15 GB
two GB log
110 GB
50 GB log
Link
One DB per 60M items
SQL IOPS: 10 per 1M items
Medium
Medium
10 GB
0.i GB log
80 GB
5 GB log
Analytics Reporting
Split when reaching 100-300 GB
Medium
Medium
Usage dependent
Usage dependent
Search Assistants
One DB
Depression
Low
0.4 GB
1 GB log
1 GB data
2 GB log

Service application storage requirements and IOPS recommendations

Service awarding Size estimation recommendation
User Contour
The User Profile service application is associated with three databases: Profile, Synchronization, and Social Tagging.
Note: The testing for the User Profile database storage requirements and IOPS recommendations is non yet complete. Check back for additional information.
For User Profile database information, come across Database types and descriptions in SharePoint Server.
Managed Metadata Service
The Managed Metadata service application has 1 database. The size of the database is affected by the number of content types and keywords used in the organisation. Many environments will include multiple instances of the Managed Metadata service awarding.
Secure Shop Service
The size of the Secure Store service application database is determined past the number of credentials in the store and the number of entries in the audit table. We recommend that y'all classify five MB for every 1,000 credentials for it. It has minimal IOPS.
State Service
The State service awarding has one database. We recommend that you classify i GB for it. Information technology has minimal IOPS.
Give-and-take Automation Services
The Word Automation service application has one database. We recommend that you allocate 1 GB for it. Information technology has minimal IOPS.
PerformancePoint Services
The PerformancePoint service awarding has one database. Nosotros recommend that you allocate ane GB for it. Information technology has minimal IOPS.
Business organization Data Connectivity service
The Business Information Connectivity service application has i database. This database is pocket-sized and significant growth is unlikely. It has minimal IOPS. The PerformancePoint Services is not applicable for Subscription Edition.
App Management
The App Management service application has one database. This database is small and pregnant growth is unlikely. It has minimal IOPS.
Ability Pivot
The Power Pivot Service application has one database. This database is small and has no pregnant I/O bear on. We recommend that yous apply the same IOPS as the SharePoint content database. Content databases take significantly higher I/O requirements than the Power Pivot service application database.

Determine availability needs

Availability is how much a SharePoint Server surround is perceived by users to exist available. An available system is a system that is resilient — that is, incidents that impact service occur infrequently, and timely and constructive action is taken when they practise occur.

Availability requirements tin significantly increase your storage needs. For detailed information, meet Create a loftier availability architecture and strategy for SharePoint Server. Also, see the SQL Server 2012 white paper AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solutions by Using AlwaysOn Availability Groups.

Choose SQL Server version and edition

We recommend that for SharePoint Server Subscription Edition, 2019, or 2016 yous consider running your surroundings on the Enterprise Edition of the following SQL Servers to take advantage of the other performance, availability, security, and management capabilities that these versions provide.

  • SQL Server 2019 (SharePoint Subscription Edition, 2019, and 2016)

  • SQL Server 2017 RTM (SharePoint Servers 2016 and 2019)

  • SQL Server 2016 (SharePoint Servers 2016 and 2019)

  • SQL Server 2014 with Service Pack 1 (SP1) (SharePoint Server 2016 only)

For more than information about the benefits of these versions, run across Features Supported past the Editions of SQL Server 2014, Editions and supported features of SQL Server 2016, Editions and supported features of SQL Server 2017, and https://docs.microsoft.com/sql/sql-server/editions-and-components-of-sql-server-version-fifteen?view=sql-server-ver15).

Nosotros recommend that for SharePoint Server 2013 you lot consider running your environs on the Enterprise Edition of SQL Server 2008 R2 with Service Pack 1 (SP1), SQL Server 2012, or SQL Server 2014 to take advantage of the other operation, availability, security, and direction capabilities that these versions provide. For more information nigh the benefits of SQL Server 2008 R2 with SP1, SQL Server 2012, and SQL Server 2014 Enterprise Edition, see Features Supported past the Editions of SQL Server 2014, Features Supported by the Editions of SQL Server 2012, and Features Supported past the Editions of SQL Server 2008 R2.

In item, you should consider your need for the following features:

  • Backup compression Fill-in pinch can speed up any SharePoint backup, and is available in every edition of SQL Server 2008 and subsequently. Past setting the pinch selection in your backup script, or by configuring the server that is running SQL Server to compress past default, y'all tin can significantly reduce the size of your database backups and shipped logs. For more than data, run across Backup Compression (SQL Server).

    Notation

    SQL Server data compression is non supported for SharePoint Server, except for the Search service application databases.

  • Transparent information encryption If your security requirements include the need for transparent data encryption, you lot must use SQL Server Enterprise Edition.

  • Content deployment If you plan to apply the content deployment feature, consider SQL Server Enterprise Edition and so that the organisation can take reward of database snapshots.

    Note

    If you are using a Remote BLOB storage provider that does non back up database snapshots, yous can't utilise snapshots for content deployment or backup.

  • Remote Hulk storage If you desire to take advantage of remote BLOB storage to a database or location exterior the files associated with each content database, you must use the Enterprise Edition of:

SharePoint Server Subscription Edition

  • SQL Server 2016

  • SQL Server 2017 RTM

  • SQL Server 2019

SharePoint Server 2019

  • SQL Server 2016

  • SQL Server 2017 RTM

  • SQL Server 2019

SharePoint Server 2016

  • SQL Server 2014 (SP1)

  • SQL Server 2016

  • SQL Server 2017 RTM

  • SQL Server 2019

SharePoint 2013

  • SQL Server 2008 R2 with SP1

  • SQL Server 2012 Enterprise Edition

  • Resource governor Resource Governor is a engineering introduced in SQL Server 2008 to enable yous to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. Resource Governor enables you to differentiate workloads and allocate CPU and memory every bit they are requested, based on the limits that you specify. For more than information about how to use Resource Governor, see Resource Governor.

    Nosotros recommend that yous use Resources Governor with SharePoint Server to:

    • Limit the corporeality of SQL Server resources that the web servers targeted by the search clamber component swallow. As a best practice, we recommend limiting the crawl component to 10 percentage CPU when the organisation is under load.

    • Monitor how many resources are consumed by each database in the organisation — for instance, you tin employ Resources Governor to help you determine the best placement of databases amid computers that are running SQL Server.

  • Microsoft Power Pivot for SharePoint Enables users to share and collaborate on user-generated information models and assay in Excel on the web while automatically refreshing those analyses. You must take Function on the spider web to utilize Excel on the web with Power Pivot for SharePoint and SharePoint Server 2016. You tin can use SQL Server 2014 (SP1) or SQL Server 2016 RTM Enterprise Edition and SQL Server Assay Services for concern intelligence with SharePoint Server 2016. Withal, you can merely employ Ability Pivot for SharePoint with SQL Server 2016 RTM, non with SQL Server 2014 (SP1).

  • Power Pivot for SharePoint 2013 Enables users to share and collaborate on user-generated data models and analysis in Excel and in the browser while automatically refreshing those analyses. It is function of SQL Server 2008 R2 Analysis Services (SSAS) Datacenter and Enterprise Edition, SQL Server 2012 SP1 Analysis Services (SSAS) Enterprise Edition, and SQL Server 2014 Analysis Services (SSAS) Enterprise and Business Intelligence Edition.

Design storage architecture based on chapters and I/O requirements

The storage architecture and disk types that you select for your environs tin affect system performance.

In this section:

  • Choose a storage architecture

  • Cull disk types

  • Cull RAID types

Choose a storage architecture

SharePoint Server supports Direct Fastened Storage (DAS), Storage Area Network (SAN), and Network Attached Storage (NAS) storage architectures, although NAS is only supported for use with content databases that are configured to use remote Hulk storage. Your choice depends on factors inside your business solution and your existing infrastructure.

Whatsoever storage architecture must support your availability needs and perform fairly in IOPS and latency. To be supported, the organisation must consistently return the offset byte of data within twenty milliseconds (ms).

Direct Attached Storage (DAS)

DAS is a digital storage organization that is directly attached to a server or workstation, without a storage network in between. DAS physical deejay types include Serial Attached SCSI (SAS) and Serial Attached ATA (SATA).

In general, we recommend that you lot choose a DAS architecture when a shared storage platform can't guarantee a response time of 20 ms and sufficient capacity for boilerplate and peak IOPS.

Storage Area Network (SAN)

SAN is an compages to attach remote computer storage devices (such as deejay arrays and tape libraries) to servers in such a style that the devices appear as locally attached to the operating system (for example, block storage).

In full general, nosotros recommend that you choose a SAN when the benefits of shared storage are important to your system.

The benefits of shared storage include:

  • Easier to reallocate disk storage between servers.

  • Can serve multiple servers.

  • No limitations on the number of disks that can be accessed.

Network Attached Storage (NAS)

A NAS unit is a self-independent figurer that is connected to a network. Its sole purpose is to supply file-based data storage services to other devices on the network. The operating system and other software on the NAS unit of measurement provide the functionality of data storage, file systems, and access to files, and the management of these functionalities (for example, file storage).

Note

NAS is only supported for use with content databases that are configured to use remote BLOB storage (RBS). Any network storage architecture must respond to a ping within 1 ms and must return the kickoff byte of information within 20 ms. This restriction does not apply to the local SQL Server FILESTREAM provider, because information technology only stores data locally on the same server.

Note

Some confusion exists nearly if y'all use the Internet Pocket-sized Figurer Arrangement Interface (iSCSI) and presume that it is a NAS protocol. If you lot access this iSCSI storage through the Common Internet File Organisation (CFIS), it is a NAS protocol. This means that you can't utilize this storage with content databases if they aren't configured to employ RBS. If however, you access this iSCSI storage through a locally attached hd, it is considered a SAN compages. This ways that you lot tin use information technology with NAS.

Choose disk types

The disk types that you use in the system can affect reliability and operation. All else beingness equal, larger drives increment mean seek fourth dimension. SharePoint Server supports the following types of drives:

  • Small-scale Estimator Organisation Interface (SCSI)

  • Serial Advanced Technology Attachment (SATA)

  • Series-fastened SCSI (SAS)

  • Fibre Aqueduct (FC)

  • Integrated Device Electronics (IDE)

  • Solid Land Drive (SSD) or Flash Disk

Choose RAID types

RAID (Redundant Array of Independent Disks) is often used to both better the performance characteristics of individual disks (by striping data across several disks) and to provide protection from private disk failures.

All RAID types are supported for SharePoint Server. Nonetheless, nosotros recommend that you utilize RAID ten or a vendor-specific RAID solution that has equivalent functioning.

When yous configure a RAID array, make sure that y'all marshal the file system to the get-go that is supplied past the vendor.

For more information near provisioning RAID for SQL Server, see RAID.

Estimate retentivity requirements

The memory that is required for SharePoint Server is directly related to the size of the content databases that you are hosting on a server that is running SQL Server.

As you add service applications and features, your requirements are likely to increase. The following table gives guidelines for how much memory we recommend.

Combined size of content databases RAM recommended for computer running SQL Server
Minimum for small production deployments
eight GB
Minimum for medium production deployments
xvi GB
Recommendation for upwardly to 2 terabytes
32 GB
Recommendation for the range of 2 terabytes to 5 terabytes
64 GB
Recommendation for more five terabytes
Actress RAM over 64 GB can improve SQL Server caching speed

For information about SQL Server capacity limits and specifications, run into Compute Capacity Limits past Edition of SQL Server and Maximum Capacity Specifications for SQL Server.

Other factors that may influence the retentivity that is required include:

  • The use of SQL Server mirroring.

  • The frequent utilise of files larger than 15 megabytes (MB).

Understand network topology requirements

Plan the network connections inside and betwixt farms. We recommend that y'all utilize a network that has low latency.

The post-obit listing provides some all-time practices and recommendations:

  • All servers in the farm should have LAN bandwidth and latency to the server that is running SQL Server. Latency should be no greater than i millisecond.

  • Nosotros do non recommend a wide expanse network (WAN) topology in which a server that is running SQL Server is deployed remotely from other components of the farm over a network that has latency greater than 1 ms., because this topology has not been tested.

  • Plan for an acceptable WAN network if you program to use SQL Server the AlwaysOn implementation suite, mirroring, log shipping, or Failover Clustering to keep a remote site up to date.

  • We recommend that web servers and application servers have 2 network adapters: one network adapter to handle user traffic and the other to handle advice with the servers that are running SQL Server.

    Note

    If you employ iSCSI, make certain each network adapter is dedicated to either network communication or iSCI, non both.

Configure SQL Server

The post-obit sections depict how to program to configure SQL Server for SharePoint Server.

In this department:

  • Approximate how many servers are required

  • Configure storage and retentivity

  • Set SQL Server options

  • Configure databases

Estimate how many servers are required

In general, SharePoint Server is designed to accept advantage of SQL Server scale out. For example, SharePoint Server may perform better with many medium-size servers that are running SQL Server than with just several large servers.

Always put SQL Server on a dedicated server that is not running whatsoever other farm roles or hosting databases for whatever other application. The only exception to this recommendation is if you deploy the system on a stand-alone server for a development or a non-performance oriented exam environment. Although SQL Server can run on the same server as SharePoint, we recommend running SQL Server on a separate server for improve functioning.

The following instructions are general guidance for when to deploy an extra server that will run a SQL Server instance:

  • Add together some other database server when yous have more than four web servers that are running at chapters.

  • Add some other database server when your current server has reached its constructive resource limits of RAM, CPU, disk IO throughput, disk capacity, or network throughput.

For more data, see Compute Capacity Limits by Edition of SQL Server and Maximum Chapters Specifications for SQL Server.

To promote secure credential storage when you lot are running the Secure Store service awarding, we recommend that the Secure Store database be hosted on a carve up database example where access is limited to one administrator.

Configure storage and retentiveness

On the server that is running SQL Server, we recommend that the L2 enshroud per CPU have a minimum of two MB to improve retentiveness.

Follow vendor storage configuration recommendations

For optimal performance when you configure a physical storage array, adhere to the hardware configuration recommendations supplied by the storage vendor instead of relying on the default values of the operating arrangement.

If you lot do not have guidance from your vendor, nosotros recommend using the PowerShell storage cmdlets that are bachelor for Windows Server 2012 R2. For more than information, see Storage Cmdlets in Windows PowerShell.

Provide as many resources as possible

Ensure that the SQL Server I/O channels to the disks are not shared by other applications, such equally the paging file and Internet Data Services (IIS) logs.

Provide as much bus bandwidth as possible. Greater autobus bandwidth helps improve reliability and operation. Consider that the disk is not the just user of bus bandwidth — for example, yous must also account for network admission.

Set SQL Server options

The post-obit SQL Server settings and options should exist configured before y'all deploy SharePoint Server.

  • Do not enable autocreate statistics on a server that hosts SQL Server and supports SharePoint Server. SharePoint Server configures the required settings upon provisioning and upgrade. Autocreate statistics can change the execution plan of a query from one instance of SQL Server to another instance of SQL Server. Therefore, to provide consistent support for all customers, SharePoint Server provides coded hints for queries as needed to provide the best performance across all scenarios.

  • To ensure optimal operation, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server databases. For more than information about how to fix max degree of parallelism, see Configure the max degree of parallelism Server Configuration Option.

Configure databases

The following guidance describes all-time practices to program for as y'all configure each database in your surround.

Separate and prioritize your data among disks

Ideally, you should place the tempdb database, content databases, database, search databases, and SQL Server 2019, SQL Server 2017 RTM, SQL Server 2016, SQL Server 2014 (SP1), SQL Server 2012, and SQL Server 2008 R2 with SP1 transaction logs on dissever physical hard disks.

The post-obit list provides some best practices and recommendations for prioritizing data:

  • When you prioritize data among faster disks, use the following ranking:

    • Tempdb data files and transaction logs

    • Database transaction log files

    • Search databases, except for the Search administration database

    • Database data files

      In a heavily read-oriented portal site, prioritize information over logs.

  • Testing and customer information show that SharePoint Server farm performance tin be impeded by insufficient disk I/O for tempdb. To avoid this issue, classify dedicated disks for tempdb. If a loftier workload is projected or monitored — that is, the average read action or the average write action requires more 20 ms — you lot might have to ease the bottleneck by either separating the files across disks or by replacing the disks with faster disks.

  • For best performance, place the tempdb on a RAID x array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be fix at an equal size. Count dual core processors as ii CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU. For more information, see Optimizing tempdb Performance.

  • Separate database data and transaction log files across different disks. If files must share disks considering the files are too small to warrant a whole disk or stripe, or you take a shortage of disk space, put files that have unlike usage patterns on the aforementioned deejay to minimize concurrent access requests.

  • Consult your storage hardware vendor for information about how to configure all logs and the search databases for write optimization for your particular storage solution.

Use multiple data files for content databases

Follow these recommendations for best performance:

  • Only create files in the master filegroup for the database.

  • Distribute the files across carve up disks.

  • The number of data files should be less than or equal to the number of core CPUs. Count dual core processors every bit ii CPUs for this purpose. Count each processor that supports hyper-threading every bit a unmarried CPU.

  • Create data files of equal size.

Important

Although yous tin can utilise the fill-in and recovery tools that are built in to SharePoint Server to back upwardly and recover multiple information files, if you overwrite in the same location, the tools tin can't restore multiple data files to a different location. For this reason, we strongly recommend that when you use multiple data files for a content database, you lot employ SQL Server fill-in and recovery tools. For more information about how to support and recover SharePoint Server, encounter Plan for backup and recovery in SharePoint Server.

For more than information about how to create and manage filegroups, come across Physical Database Files and Filegroups.

Limit content database size to better manageability

Plan for database sizing that volition meliorate manageability, operation, and ease of upgrade for your environment.

To help ensure system performance, we recommended that you limit the size of content databases to 200 GB, except when specific usage scenarios and atmospheric condition support larger sizes. For more data about content database size limits, see the "Content database limits" section in Software boundaries and limits for SharePoint Servers 2016 and 2019.

We generally recommend that a site drove should non exceed 100 GB unless it is the only site collection in the database so that yous can utilize the SharePoint Server granular backup tools to move a site collection to another database if you demand to.

Proactively manage the growth of information and log files

We recommend that you lot proactively manage the growth of data and log files by considering the following recommendations:

  • Every bit much as possible, pre-abound all data and log files to their expected concluding size.

  • We recommend that you enable autogrowth for safety reasons. Do not rely on the default autogrowth settings. Consider the post-obit guidelines when yous configure autogrowth:

    • When you plan content databases that exceed the recommended size (200 GB), set the database autogrowth value to a fixed number of megabytes instead of to a percentage. This setting reduces the frequency with which SQL Server increases the size of a file. Increasing file size is a blocking action that involves filling the new space with empty pages.

    • If the calculated size of the content database is non expected to attain the recommended maximum size of 200 GB within the next twelvemonth, set up it to the maximum size the database is predicted to attain in a year — with twenty percent extra margin for error — by using the Alter DATABASE MAXSIZE property. Periodically review this setting to make certain that it is notwithstanding an advisable value, depending on past growth rates.

  • Maintain a level of at least 25 percentage available space across disks to allow for growth and peak usage patterns. If you lot are managing growth past adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of infinite.

Validate and monitor storage and SQL Server performance

Examination that your performance and backup solution on your hardware enables you to meet your service level agreements (SLAs). In particular, test the I/O subsystem of the computer that is running SQL Server to make sure that performance is satisfactory.

Test the fill-in solution that y'all are using to make certain that it tin support the system within the available maintenance window. If the backup solution can't see the SLAs your business requires, consider using an incremental backup solution such as Microsoft Organisation Eye Data Protection Manager.

It is important to track the following resources components of a server that is running SQL Server: CPU, memory, enshroud/striking ratio, and I/O subsystem. When i or more of the components seems ho-hum or overburdened, analyze the advisable strategy based on the current and projected workload. For more data, see Monitor and Tune for Performance.

The following section lists the performance counters that we recommend that you use to monitor the performance of the SQL Server databases that are running in your SharePoint Server surroundings. Also listed are approximate good for you values for each counter.

For details about how to monitor performance and use performance counters, see Windows Performance Monitor and Monitoring Performance.

SQL Server counters to monitor

Monitor the following SQL Server counters to ensure the health of your servers:

  • General statistics This object provides counters to monitor full general server-wide activity, such as the number of electric current connections and the number of users connecting and disconnecting per 2nd from computers that are running an case of SQL Server. Consider monitoring the following counter:

    • User connections This counter shows the number of user connections on your computer that is running SQL Server. If y'all run across this number increment by 500 pct from your baseline, you lot may run into a performance reduction.

  • Databases This object provides counters to monitor majority re-create operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activeness is occurring in the database and how full the transaction log is becoming. The amount of user activeness tin decide the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help yous identify performance bottlenecks. Consider monitoring the following counter:

    • Transactions/sec This counter shows the number of transactions on a given database or on the entire server per second. This number is more than for your baseline and to assistance yous troubleshoot bug.

  • Locks This object provides information near SQL Server locks on individual resource types. Consider monitoring the following counters:

    • Average Wait Fourth dimension (ms) This counter shows the average amount of look time for each lock request that resulted in a wait.

    • Lock Expect Time (ms) This counter shows the await time for locks in the terminal 2d.

    • Lock waits/sec This counter shows the number of locks per second that couldn't be satisfied immediately and had to wait for resources.

    • Number of deadlocks/sec This counter shows the number of deadlocks on the figurer that is running SQL Server per 2nd. This number should not increment above 0.

  • Latches This object provides counters to monitor internal SQL Server resource locks chosen latches. Monitoring the latches to decide user activity and resources usage tin help you identify performance bottlenecks. Consider monitoring the following counters:

    • Average Latch Wait Time (ms) This counter shows the boilerplate latch wait time for latch requests that had to wait.

    • Latch Waits/sec This counter shows the number of latch requests that couldn't be granted immediately.

  • SQL Statistics This object provides counters to monitor compilation and the type of requests sent to an instance of SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of SQL Server gives you lot an indication of how quickly SQL Server is processing user queries and how effectively the query optimizer is processing the queries. Consider monitoring the following counters:

    • SQL Compilations/sec This counter indicates the number of times the compile code path is entered per second.

    • SQL Re-Compilations/sec This counter indicates the number statement recompiles per second.

  • Buffer Director This object provides counters to monitor how SQL Server uses memory to shop data pages, internal information structures, and the procedure cache, and too counters to monitor the physical I/O as SQL Server reads and writes database pages. Consider monitoring the following counter:

    • Buffer Cache Striking Ratio

    • This counter shows the percentage of pages that were found in the buffer cache without having to read from disk. The ratio is the total number of enshroud hits divided by the total number of cache lookups over the final few thousand page accesses. Because reading from the enshroud is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer enshroud hit ratio by increasing the memory bachelor to SQL Server.

  • Program Cache This object provides counters to monitor how SQL Server uses retentiveness to store objects such as stored procedures, unprepared and prepared Transact-SQL statements, and triggers. Consider monitoring the following counter:

    • Cache Hitting Ratio

    • This counter indicates the ratio between cache hits and lookups for plans.

Physical server counters to monitor

Monitor the post-obit counters to ensure the health of your computers that are running SQL Server:

  • Processor: % Processor Time: _Total This counter shows the percentage of time that the processor is executing application or operating organization processes other than Idle. On the figurer that is running SQL Server, this counter should be kept between 50 percent and 75 pct. If there is abiding overloading, investigate whether there is abnormal process activity or if the server needs more CPUs.

  • System: Processor Queue Length This counter shows the number of threads in the processor queue. Monitor this counter to brand sure that it remains less than two times the number of cadre CPUs.

  • Memory: Available Mbytes This counter shows the physical memory, in megabytes, bachelor to processes running on the computer. Monitor this counter to make sure that yous maintain a level of at least 20 percent of the full available physical RAM.

  • Retentiveness: Pages/sec This counter shows the rate at which pages are read from or written to disk to resolve hard page faults. Monitor this counter to make certain that it remains under 100.

For more data and memory troubleshooting methods, run across the post-obit resources:

  • SQL Server 2014 (SP1) -Monitor Memory Usage

  • Monitor Deejay Usage

  • Monitor CPU Usage

  • SQL Server 2017, SQL Server 2017, & SQL Server 2019 -Monitor Retentiveness Usage

  • Monitor Deejay Usage

  • Monitor CPU Usage

For more information and memory troubleshooting methods, see Monitoring Memory Usage for SQL Server 2008 R2 with SP1, Monitoring Retention Usage for SQL Server 2012, and Monitor Retention Usage for SQL Server 2014.

Deejay counters to monitor

Monitor the post-obit counters to ensure the health of disks. The post-obit values stand for values measured over time — not values that occur during a sudden spike and non values that are based on a single measurement.

  • Physical Deejay: % Deejay Time: DataDrive This counter shows the per centum of elapsed fourth dimension that the selected deejay drive is busy servicing read or write requests-it is a full general indicator of how decorated the disk is. If the PhysicalDisk: % Disk Time counter is high (more than 90 percent), cheque the PhysicalDisk: Current Deejay Queue Length counter to run across how many system requests are waiting for deejay admission. The number of waiting I/O requests should be sustained at no more than than 1.5 to two times the number of spindles that make upward the physical disk.

  • Logical Deejay: Deejay Transfers/sec This counter shows the charge per unit at which read and write operations are performed on the disk. Use this counter to monitor growth trends and forecast appropriately.

  • Logical Disk: Disk Read Bytes/sec and Logical Deejay: Disk Write Bytes/sec These counters evidence the rate at which bytes are transferred from the disk during read or write operations.

  • Logical Disk: Avg. Deejay Bytes/Read This counter shows the average number of bytes transferred from the disk during read operations. This value can reverberate disk latency — larger read operations can result in slightly increased latency.

  • Logical Deejay: Avg. Disk Bytes/Write This counter shows the average number of bytes transferred to the deejay during write operations. This value can reflect disk latency — larger write operations tin result in slightly increased latency.

  • Logical Deejay: Current Deejay Queue Length This counter shows the number of requests outstanding on the disk at the time that the performance data is collected. For this counter, lower values are meliorate. Values greater than ii per disk may indicate a bottleneck and should be investigated. Therefore, a value of up to eight is acceptable for a logical unit (LUN) fabricated up of four disks. Bottlenecks tin can create a backlog that can spread beyond the electric current server that is accessing the disk and result in long wait times for users. Possible solutions to a bottleneck are to add together more than disks to the RAID array, replace existing disks with faster disks, or move some data to other disks.

  • Logical Disk: Avg. Disk Queue Length This counter shows the boilerplate number of both read and write requests that were queued for the selected disk during the sample interval. The dominion is that in that location should be ii or fewer outstanding read and write requests per spindle. But this request-count can exist difficult to measure considering of storage virtualization and differences in RAID levels betwixt configurations. Look for larger than average disk queue lengths in combination with larger than average deejay latencies. This combination can signal that the storage array cache is being overused or that spindle sharing with other applications is affecting functioning.

  • Logical Disk: Avg. Disk sec/Read and Logical Disk: Avg. Disk sec/Write These counters show the average fourth dimension, in seconds, of a read or write operation to the disk. Monitor these counters to make sure that they remain beneath 85 percentage of the deejay capacity. Deejay access fourth dimension increases exponentially if read or write operations are more than 85 percent of disk capacity. To determine the specific capacity for your hardware, refer to the vendor documentation or utilize the Diskspd Utility, storage testing tool to summate it. For more information, encounter Diskspd: A Robust Storage Performance Tool.

    • Logical Disk: Avg. Disk sec/Read This counter shows the average time, in seconds, of a read operation from the disk. On a well-tuned arrangement, ideal values are from i through five ms for logs (ideally 1 ms on a cached array), and from 4 through xx ms for data (ideally less than 10 ms). Higher latencies can occur during peak times. However, if high values occur regularly, you should investigate the crusade.

    • Logical Disk: Avg. Disk sec/Write This counter shows the boilerplate time, in seconds, of a write performance to the disk. On a well-tuned organisation, ideal values are from 1 through 5 ms for logs (ideally 1 ms on a buried array), and from 4 through xx ms for data (ideally less than 10 ms). Higher latencies tin occur during summit times. Yet, if high values occur regularly, you lot should investigate the crusade.

      When you are using RAID configurations with the Logical Disk: Avg. Deejay Bytes/Read or Logical Disk: Avg. Deejay Bytes/Write counters, use the formulas listed in the following tabular array to decide the rate of input and output on the disk.

RAID level Formula
RAID 0
I/Bone per disk = (reads + writes) / number of disks
RAID ane
I/Os per disk = [reads + (2 × writes)] / 2
RAID five
I/Os per disk = [reads + (iv × writes)] / number of disks
RAID 10
I/Os per deejay = [reads + (ii × writes)] / number of disks

For example, if yous have a RAID 1 organization that has ii concrete disks, and your counters are at the values that are shown in the following tabular array.

Counter Value
Avg. Disk sec/Read
80
Logical Disk: Avg. Disk sec/Write
70
Avg. Disk Queue Length
v

  • The I/O value per deejay can exist calculated as follows: (lxxx + (2 × 70))/ii = 110

  • The disk queue length can be calculated as follows: 5/2 = 2.v

  • In this situation, you have a borderline I/O bottleneck.

Other monitoring tools

You lot tin can also monitor disk latency and analyze trends by using the sys.dm_io_virtual_file_stats dynamic direction view in SQL Server 2008. For more information, encounter sys.dm_io_virtual_file_stats (Transact-SQL).

Thank you to Bill Baer, Microsoft Senior Production Marketing Manager and Brian Alderman, CEO and Founder of MicroTechPoint for providing a series of online SQL Server 2012 training modules. Special thank you to Aqueduct 9 Microsoft for hosting these online grooming modules. The following training modules provide details about SQL Server 2012 database settings to help you larn how to improve SharePoint Server 2016 performance, availability, and security.

  • Tuning SQL Server 2012 for SharePoint 2013: (01) Key SQL Server and SharePoint Server Integration

  • Tuning SQL Server 2012 for SharePoint 2013: (02) Best Practices for SQL Server Database Settings

  • Tuning SQL Server 2012 for SharePoint 2013: (03) Server Settings for SQL Server

  • Tuning SQL Server 2012 for SharePoint 2013: (04) SQL Server and SharePoint Availability

See besides

Concepts

Overview of SQL Server in SharePoint Server 2016 and 2019 environments

Optimize operation for SharePoint Server 2013

Best practices for SQL Server in a SharePoint Server subcontract

Operation planning in SharePoint Server 2013

Chapters direction and sizing for SharePoint Server 2013

Chapters planning for SharePoint Server 2013

Other Resources

Overview of SQL Server in a SharePoint Server 2013 environs