規劃及設定儲存設備與 SQL Server 容量 (SharePoint Server)Storage and SQL Server capacity planning and configuration (SharePoint Server)

摘要: 了解如何在 SharePoint Server 2016 和 SharePoint Server 2013 中規劃及設定 SQL Server 的儲存區與資料庫層。Summary: Learn how to plan and configure the storage and database tier for SQL Server in SharePoint Server 2016 and SharePoint Server 2013.

我們提供的容量規劃資訊包含可協助您在 SharePoint Server 環境中規劃及設定儲存區與 SQL Server 資料庫層的指導方針。這項資訊是以 Microsoft 內部實際機器上測試所得之結果。但您的結果可能會因為所用設備及網站實作的功能而異。The capacity planning information that we provide contains guidelines to help you plan and configure the storage and SQL Server database tier in a SharePoint Server environment. This information is based on testing performed at Microsoft on live properties. However, your results may vary based on the equipment you use and the features and functionality that you implement for your sites.

注意

本文中的效能和容量測試是與 Microsoft SQL Server 2014 Service Pack 1 (SP1)、Microsoft SQL Server 2016、SQL Server 2017 RTM 和 SharePoint Server 2016 有關。測試結果與 SharePoint Server 2013 相同。Performance and capacity tests in this article relate to Microsoft SQL Server 2014 with Service Pack 1 (SP1), Microsoft SQL Server 2016, SQL Server 2017 RTM, and SharePoint Server 2016. The test results are the same as in SharePoint Server 2013.

注意

雖然測試並不是在 SQL Server 2014 (SP1)、SQL Server 2016 或 SQL Server 2017 RTM 上執行的,但這些測試結果仍可引導您在 SharePoint Server 2016 環境中規劃及設定儲存區與 SQL Server 資料庫層。如需如何設定及調整 SQL Server 2012 的相關訓練,請參閱 適用於 SharePoint Server 2013 的 SQL Server 2012Although tests were not run on SQL Server 2014 (SP1), SQL Server 2016 , or SQL Server 2017 RTM you can use these test results as a guide to help you plan for and configure the storage and SQL Server database tier in a SharePoint Server 2016 environment. For training about how to configure and tune SQL Server 2012, see SQL Server 2012 for SharePoint Server 2013.

由於 SharePoint Server 經常執行於由個別 SQL Server 資料庫管理員來管理資料庫的環境中,因此本文同時適用於 SharePoint Server 伺服器陣列實作人員與 SQL Server 資料庫管理員。本文假設使用者對 SharePoint Server 與 SQL Server 皆已有充分了解。Because SharePoint Server often runs in environments in which databases are managed by separate SQL Server database administrators, this document is intended for joint use by SharePoint Server farm implementers and SQL Server database administrators. It assumes significant understanding of both SharePoint Server and SQL Server.

本文假設您熟知 SharePoint Server 2013 的容量管理及調整大小 所介紹的概念。This article assumes that you are familiar with the concepts that are presented in Capacity management and sizing for SharePoint Server 2013.

SharePoint Server 2016 儲存區與資料庫層的設計與設定程序Design and configuration process for SharePoint Server 2016 storage and database tier

建議您將儲存區與資料庫層設計程序分成下列步驟。這些小節分別提供每個設計步驟的詳細資訊,包括儲存區需求與最佳做法:We recommend that you break the storage and database tier design process into the following steps. These sections provide detailed information about each design step, including storage requirements and best practices:

  1. 收集儲存區以及 SQL Server 空間和 I/O 的需求Gather storage and SQL Server space and I/O requirements

  2. 選擇 SQL Server 版本與版次Choose SQL Server version and edition

  3. 根據容量與 IO 需求設計儲存架構Design storage architecture based on capacity and I/O requirements

  4. 估計記憶體需求Estimate memory requirements

  5. 了解網路拓撲需求Understand network topology requirements

  6. 設定 SQL ServerConfigure SQL Server

  7. 驗證及監視儲存區與 SQL Server 的效能Validate and monitor storage and SQL Server performance

收集儲存區以及 SQL Server 空間和 I/O 的需求Gather storage and SQL Server space and I/O requirements

有若干 SharePoint Server 架構因素會對儲存設計產生影響。主要因素包括內容量、已啟用的功能、已部署的服務應用程式、伺服器陣列數目與可用性需求。Several SharePoint Server architectural factors influence storage design. The key factors are: the amount of content, enabled features, deployed service applications, number of farms, and availability requirements.

您必須先了解 SharePoint Server 所能使用的資料庫,才能開始規劃儲存區。Before you start to plan storage, you should understand the databases that SharePoint Server can use.

本節內容:In this section:

SharePoint Server 所使用的資料庫Databases used by SharePoint Server

隨 SharePoint Server 2016 安裝的資料庫取決於環境中所使用的服務應用程式。所有 SharePoint Server 2016 環境均須依賴 SQL Server 系統資料庫。本節將摘要說明隨 SharePoint Server 2016 安裝的資料庫。如需詳細資料庫資訊,請參閱 SharePoint Server 中的資料庫類型和描述The databases that are installed with SharePoint Server 2016 depend on the service applications that are used in the environment. All SharePoint Server 2016 environments rely on the SQL Server system databases. This section provides a summary of the databases installed with SharePoint Server 2016. For detailed database information, see Database types and descriptions in SharePoint Server.

如需支援 SharePoint Server 2016 的資料庫圖形概觀,請參閱 快速參考指南:SharePoint Server 2016 資料庫。您也可以下載這個 SharePoint Server 2016 資料庫海報,作為 PDFVisio 檔案。For a graphical overview of the databases that support SharePoint Server 2016, see Quick reference guide: SharePoint Server 2016 databases. You can also download this SharePoint Server 2016 database poster, as either a PDF or Visio file.

注意

有些 SharePoint Server、SQL Server 資料庫引擎與 SQL Server Reporting Services (SSRS) 資料庫在位置方面會有特定的建議或需求。如需這些資料庫位置的相關資訊,請參閱SharePoint Server 中的資料庫類型和描述Some SharePoint Server, SQL Server Database Engine, and SQL Server Reporting Services (SSRS) databases have specific location recommendations or requirements. For information about these database locations, see Database types and descriptions in SharePoint Server.

下列資料庫是 SharePoint Server 系統資料庫,並且會自動安裝。The following databases are the SharePoint Server system databases and are installed automatically.

  • 組態Configuration

  • 管理中心內容Central Administration content

  • 內容 (一或多個)Content (one or more)

下列清單顯示具有資料庫的 SharePoint Server 服務應用程式:The following list shows the SharePoint Server service applications that have databases:

  • App Management ServiceApp Management Service

  • SharePoint 相關應用程式Apps for SharePoint

  • Business Data ConnectivityBusiness Data Connectivity

  • 受管理的中繼資料Managed Metadata

  • PerformancePoint ServicesPerformancePoint Services

  • Project Server (僅限 SharePoint Server 2013)Project Server (SharePoint Server 2013 only)

  • Search ServiceSearch Service

    • 搜尋管理Search Administration

    • 分析報告Analytics Reporting

    • 編目Crawl

    • 連結Link

  • Secure Store ServiceSecure Store Service

  • SharePoint Translation ServiceSharePoint Translation Service

  • SQL Server Power Pivot ServiceSQL Server Power Pivot Service

  • 狀態服務State Service

  • 訂閱設定服務Subscription Settings Service

  • Usage and Health Data CollectionUsage and Health data collection

  • User Profile ServiceUser Profile Service

    • 設定檔Profile

    • 社交標記Social Tagging

    • 同步處理Synchronization

  • Word Automation ServicesWord Automation Services

下列清單顯示 SharePoint Foundation 2013 資料庫:The following list shows the SharePoint Foundation 2013 databases:

  • 組態Configuration

  • 管理中心內容Central Administration content

  • 內容 (一或多個)Content (one or more)

  • App Management ServiceApp Management Service

  • 搜尋服務應用程式:Search service application:

    • 搜尋管理Search administration

    • Analytics 報告 (一或多個)Analytics Reporting (one or more)

    • 編目 (一或多個)Crawl (one or more)

    • 連結 (一或多個)Link (one or more)

  • Secure Store ServiceSecure Store Service

  • 訂閱設定服務應用程式 (若已透過 Windows PowerShell 啟用)Subscription Settings Service Application (if enabled through Windows PowerShell)

  • Usage and Health Data Collection ServiceUsage and Health Data Collection Service

  • Word Conversion ServiceWord Conversion Service

若您要進一步與 SQL Server 整合,您可以在環境中加入其他資料庫,如下列案例所示。只有在您使用 SQL Server 2016 RTM Enterprise Edition 和 SQL Server 2016 SQL Server Analysis Services (SSAS) 時,才可以將 SQL Server Power Pivot for SharePoint 用於 SharePoint Server 2016 環境中。如果正在使用,您還必須規劃支援 Power Pivot 應用程式資料庫和系統的額外負載。如需詳細資訊,請下載新在 SharePoint 2016 中部署 SQL Server 2016 PowerPivot 和 Power View(英文) 白皮書。如需在多部伺服器 SharePoint Server 2016 伺服器陣列中設定及部署商業智慧的詳細資訊,請下載 在多層 SharePoint 2016 伺服器陣列中部署 SQL Server 2016 PowerPivot 和 Power ViewIf you are integrating further with SQL Server, your environment may also include additional databases, as in the following scenario. SQL Server Power Pivot for SharePoint can be used in a SharePoint Server 2016 environment only if you use SQL Server 2016 RTM Enterprise Edition and SQL Server 2016 SQL Server Analysis Services (SSAS). If in use, you must also plan to support the Power Pivot application database, and the additional load on the system. 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 business 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.

SQL Server 2016 Reporting Services (SSRS) 增益集可以與任何 SharePoint Server 2016 環境搭配使用。若您要使用此增益集,請進行規劃以支援兩個 SQL Server Reporting Services 資料庫以及 SQL Server Reporting Services 所需的額外負載。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 support the two SQL Server Reporting Services databases and the additional load that is required for SQL Server Reporting Services.

  • SQL Server 2012 Power Pivot for SharePoint 2013 可用於包含 SQL Server 2008 R2 Enterprise Edition 與 SQL ServerAnalysis Services 的 SharePoint 2013 環境中。若以此方式使用,您必須另行規劃以支援 Power Pivot 應用程式資料庫以及系統上的額外負載。如需詳細資訊,請參閱<規劃 SharePoint 伺服器陣列中的 PowerPivot 部署>以及 SQL Server PRO 文章的<了解 Microsoft Excel 2013 中的 PowerPivot 與 Power View>。SQL Server 2012 Power Pivot for SharePoint 2013 can be used in a SharePoint 2013 environment that includes SQL Server 2008 R2 Enterprise Edition and SQL Server Analysis Services. If in use, you must also plan to support the Power Pivot application database, and the additional load on the system. For more information, see Plan a PowerPivot deployment in a SharePoint farm and the SQL Server PRO article Understanding PowerPivot and Power View in Microsoft Excel 2013.

  • SQL Server 2008 R2 Reporting Services (SSRS) 外掛程式可用於任何 SharePoint 2013 環境中。若您要使用此外掛程式,請進行規劃以支援兩個 SQL Server 2008 R2 Reporting Services 資料庫以及 SQL Server 2008 R2 Reporting Services 所需的額外負載。The SQL Server 2008 R2 Reporting Services (SSRS) plug-in can be used with any SharePoint 2013 environment. If you are using the plug-in, plan to support the two SQL Server 2008 R2 Reporting Services databases and the additional load that is required for SQL Server 2008 R2 Reporting Services.

了解 SQL Server 與 IOPSUnderstand SQL Server and IOPS

在任何主控 SQL Server 執行個體的伺服器上,伺服器能否以最快的速度從 I/O 子系統進行回應,都是非常重要的議題。On any server that hosts a SQL Server instance, it is very important that the server achieve the fastest response possible from the I/O subsystem.

磁碟或陣列愈多且愈快速,就愈能提供足夠的「每秒 I/O 作業數 (IOPS)」,同時讓所有磁碟維持低量的延遲與佇列作業。More and faster disks or arrays provide sufficient I/O operations per second (IOPS) while maintaining low latency and queuing on all disks.

您無法新增其他類型的資源 (例如 CPU 或記憶體) 來彌補 I/O 子系統緩慢的回應速度。但這可能會影響到整個伺服器陣列,並造成問題。在部署之前,請先妥善規劃以達到最低延遲,並監視您現有的系統。You cannot add other types of resources, such as CPU or memory, to compensate for slow response from the I/O subsystem. However, it can influence and cause issues throughout the farm. Plan for minimal latency before deployment, and monitor your existing systems.

在您部署新的伺服器陣列前,建議您使用 Diskspd 公用程式測定 I/O 子系統的基準。請注意,這項工具適用於所有具有各種 SQL Server 版本的 Windows Server 版本。如需詳細資訊,請參閱 Diskspd 公用程式:強固儲存測試工具Before you deploy a new farm, we recommend that you benchmark the I/O subsystem by using the Diskspd Utility . Note that this tool works on all Windows Server versions with all versions of SQL Server. For more information, see Diskspd Utility: A Robust Storage Testing Tool.

壓力測試也會提供有價值的 SQL Server 資訊。如需資訊,請參閱 DiskSpd 的儲存區基準測試Stress testing also provides valuable information for SQL Server. For information, see Storage Benchmarking with DiskSpd.

如需如何從 SQL Server 的觀點分析 IOPS 需求的詳細資訊,請參閱為 SQL Server 資料庫應用程式分析 I/O 特性及調整儲存系統大小For detailed information about how to analyze IOPS requirements from a SQL Server perspective, see Analyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database Applications.

估計核心儲存區與 IOPS 需求Estimate core storage and IOPS needs

設定與內容儲存區和 IOPS 是您在每個 SharePoint Server 部署中都必須規劃的基準層。Configuration and content storage and IOPS are the base layer that you must plan for in every SharePoint Server deployment.

設定儲存區與 IOPSConfiguration storage and IOPS

設定資料庫與 管理中心 內容資料庫的儲存區需求並不大。建議您為設定資料庫配置 2 GB,並且為 管理中心 內容資料庫配置 1 GB。經過一段時間後,設定資料庫可能會擴增到 1 GB 以上。它並不會快速擴增,而是以每 50,000 個網站集合約 40 MB 的速度擴增。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 1 GB for the Central Administration content database. Over time, the Configuration database may grow beyond 1 GB. It does not grow quickly — it grows by approximately 40 MB for each 50,000 site collections.

設定資料庫的交易記錄可能會很龐大。因此,建議您將資料庫的復原模式從完整變更為簡易。Transaction logs for the Configuration database can be large. Therefore, we recommend that you change the recovery model for the database from full to simple.

注意

若您要使用 SQL Server 資料庫鏡像提供設定資料庫的可用性,您必須使用完整復原模式。If you want to use SQL Server database mirroring to provide availability for the Configuration database, you must use the full recovery model.

設定資料庫與 管理中心 內容資料庫的 IOPS 需求都是最低需求。IOPS requirements for the Configuration database and Central Administration content database are minimal.

內容儲存區與 IOPSContent storage and IOPS

估計內容資料庫所需的儲存區與 IOPS,並無法得出精確的數據。我們期望能透過下列資訊的測試與解說協助您得出估計值,用以判斷部署的初始大小。但在您的環境執行時,我們希望您能夠根據實際環境中的資料重新檢視您的容量需求。Estimating the storage and IOPS required for content databases is not a precise activity. In testing and explaining the following information, we intend to help you derive estimates to use to determine the initial size of your deployment. However, when your environment is running, we expect that you'll revisit your capacity needs based on the data from your live environment.

若想進一步了解我們的整體容量規劃方法,請參閱 SharePoint Server 2013 的容量管理及調整大小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 estimate the storage required for content databases, without considering log files:

  1. 使用下列公式可估計內容資料庫的大小:Use the following formula to estimate the size of your content databases:

    資料庫大小 = ((D × V) × S) + (10 KB × (L + ( V × D)))Database size = ((D × V) × S) + (10 KB × (L + ( V × D)))

    注意

    在此公式中,10 KB 這個值是一個常數,用來估計 SharePoint Server 所需的中繼資料量。若您的系統需要大量使用中繼資料,您可以增加此常數。The value, 10 KB, in the formula is a constant that approximately estimates the amount of metadata required by SharePoint Server. If your system requires significant use of metadata, you may want to increase this constant.

  2. 計算預期的文件數目。此值為公式中的 D。Calculate the expected number of documents. This value is known as D in the formula.

    您對文件數目的計算方式,將取決於您所使用的功能。例如,對於 我的網站 或共同作業網站,建議您先計算每個使用者的預期文件數目,再將其乘上使用者人數。對於記錄管理或內容發佈網站,您可以計算一個程序所管理及產生的文件數目。How you calculate the number of documents will be determined by the features that you are using. For example, for My Sites or collaboration sites, we recommend that you calculate the expected number of documents per user and multiply by 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 you are creating a new system, review your existing file shares or other repositories and estimate based on that usage rate.

  3. 估計您所將儲存之文件的平均大小。此值為公式中的 S。為不同的網站類型或網站群組估計平均值,可能有其效益。我的網站、媒體存放庫與不同部門入口網站的平均檔案大小可能會有很大的差異。Estimate the average size of the documents that you'll be storing. This value is known as S in the formula. It may be 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. 估計環境中的清單項目數。此值為公式中的 L。Estimate the number of list items in the environment. This value is known as L in the formula.

    與文件相比,清單項目較難估計。我們通常會以文件數目 (D) 的三倍作為估計值,但此值會隨著網站的預期使用方式而不同。List items are more difficult to estimate than documents. We generally use an estimate of three times the number of documents (D), but this will vary based on how you expect to use your sites.

  5. 判斷版本的概數。估計文件庫中的任何文件所將擁有的平均版本數目。此值通常會遠低於允許的版本數目上限。此值為公式中的 V。Determine the approximate number of versions. Estimate the average number of versions any document in a library will have. This value will usually be much lower than the maximum allowed number of versions. This value is known as V in the formula.

    V 值必須大於零。The value of V must be above zero.

舉例來說,我們使用此公式與下表中的特性,共同作業環境的內容資料庫估計其資料檔所需的儲存空間。結果是,您大約需要 105 GB。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 environment. The result is that you need approximately 105 GB.

輸入Input Value
文件數目 (D)Number of documents (D)
200,000200,000
以 10,000 個使用者乘以 20 份文件來計算Calculated by assuming 10,000 users times 20 documents
文件的平均大小 (S)Average size of documents (S)
250 KB250 KB
清單項目 (L)List items (L)
600,000600,000
非現行版本數目 (V)Number of non-current versions (V)
22
假設允許的版本數上限為 10Assuming that the maximum versions allowed is 10

資料庫大小 = (((200,000 x 2)) × 250) + ((10 KB × ( 600,000 + ( 200,000 x 2))) = 110,000,000 KB 或 105 GBDatabase size = (((200,000 x 2)) × 250) + ((10 KB × ( 600,000 + ( 200,000 x 2))) = 110,000,000 KB or 105 GB

注意

SharePoint Server 中的高效率檔案 I/O,是一種會將檔案分割成片段而個別儲存及更新的儲存方法。當使用者要求檔案時,這些片段會一起串流傳送。如此可以提升 I/O 效能,且通常不會增加檔案大小。但小型檔案有可能會使所需的磁碟儲存區略為增加。Efficient File I/O in SharePoint Server is a storage method in which a file is split 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 normally does not increase the file size. However, small files can see a small increase in the disk storage that is required.

會對內容資料庫的大小產生影響的功能Features that influence the size of content databases

下列 SharePoint Server 功能可能會對內容資料庫的大小產生很大的影響:The following SharePoint Server features can significantly affect the size of content databases:

  • 資源回收筒 一份文件在從第一階段與第二階段的資源回收筒中刪除之前,會佔用內容資料庫中的空間。計算每個月刪除了多少文件,可推斷出資源回收筒對內容資料庫大小的影響。Recycle bins Until a document is fully deleted from both the first stage and second stage recycle bin, it occupies space in a content database. Calculate how many documents are deleted each month to determine the effect of recycle bins on the size of content databases.

  • 稽核 稽核資料可能會快速累加,並在內容資料庫中佔用大量的空間,尤其是在開啟檢視稽核功能時。建議您不應讓稽核資料無限制地增長,而應在必須符合法規需求或內部控制需求時,才啟用稽核功能。請使用下列指導方針,估計您必須為資料稽核工作保留多少空間:Auditing Audit data can 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, we recommend that you enable auditing only on the events that are important to meet regulatory needs or internal controls. Use the following guidelines to estimate the space that you must reserve for auditing data:

    • 估計一個網站有多少個新的稽核項目,然後將此數乘以 2 KB (項目通常限定在 4 KB 以內,平均大小約為 1 KB)。Estimate the number of new auditing entries for a site, and multiply this number by 2 KB (entries generally are limited to 4 KB, with an average size of about 1 KB).

    • 根據您要配置的空間量,判斷您要讓稽核記錄保留多少天。Based on the space that you want to allocate, determine the number of days of audit logs you want to keep.

注意

Office Online 伺服器 是 Office Web Apps Server 的下一個版本。搭配使用 Office Online 伺服器 與 SharePoint Server 2016 不會影響內容資料庫的大小。若要在 SharePoint Server 2016 伺服器陣列中部署 Office Online 伺服器,請參閱Deploy Office Online ServerOffice Online Server is the next version of Office Web Apps Server. Using Office Online Server with SharePoint Server 2016 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.

估計內容資料庫的 IOPS 需求Estimate content database IOPS requirements

內容資料庫的 IOPS 需求會隨著環境的使用方式、可用的磁碟空間與您所擁有的伺服器目錄,而有很大的差異。一般而言,我們會建議您將環境中的預期工作量與我們已測試的其中一個解決方案相比較。如需詳細資訊,請參閱 效能及容量測試結果與建議 (SharePoint Server 2013)IOPS requirements for content databases vary significantly based on how your environment is being used, available disk space, and the number of servers that you have. In general, we recommend that you compare the predicted workload in your environment to one of the solutions that we tested. For more information, see Performance and capacity test results and recommendations (SharePoint Server 2013).

在測試中,我們發現內容資料庫大多介於 0.05 IOPS/GB 到 0.2 IOPS/GB 左右。我們也發現,最佳做法是將上限提高為 0.5 IOPS/GB。此值高於必要值,並且可能遠高於您的環境所需的數量。請注意,若您使用鏡像,這可能會產生比原有的內容資料庫還多的 IO。請記住,鏡像的內容資料庫絕不會是輕量型。In tests, we found that the content databases tend to range from 0.05 IOPS/GB to around 0.2 IOPS/GB. We also found that a best practice is to increase the top-end to 0.5 IOPS/GB. This is more than necessary and can be much more than you'll need in your environment. Note that if you use mirroring, this results in much more IO than the primary content databases. Simply be aware that the mirrored content databases are never lightweight.

估計服務應用程式儲存區需求與 IOPSEstimate service application storage needs and IOPS

在估計內容儲存區與 IOPS 需求後,您必須判斷您的環境使用的服務應用程式所需的儲存區與 IOPS。After you estimate 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 服務應用程式儲存區與 IOPS 需求SharePoint Server service application storage and IOPS requirements

要估計系統中的服務應用程式所需的儲存區,您必須先了解服務應用程式及其使用方式。下表列出可在 SharePoint Server 2016 中使用以及具有資料庫的服務應用程式。SharePoint Server 2016 中所有服務應用程式的儲存區與 IOPs 資料皆仍與 SharePoint Server 2010 和 SharePoint Server 2013 中的相同。To estimate the storage requirements for the service applications in the system, you must first be aware of the service applications and how you'll use them. Service applications that are available in SharePoint Server 2016 and that have databases are listed in the following tables. The storage and IOPs data for all of the service applications in SharePoint Server 2016 remains the same as in SharePoint Server 2010 and SharePoint Server 2013.

搜尋服務應用程式儲存區與 IOPS 需求Search service application storage and IOPS requirements

資料庫Database 調整大小Scaling 磁碟 IOPSDisk IOPS 磁碟大小Disk size 10M 的項目10M items 100M 的項目100M items
編目Crawl
每 20M 的項目一個資料庫One DB per 20M items
SQL IOPS:10 (每 1 DPS)SQL IOPS: 10 per 1 DPS
中/高Medium/High
Medium
15GB15GB
2GB 的記錄2GB log
110GB110GB
50GB 的記錄50GB log
連結Link
每 60M 的項目一個資料庫One DB per 60M items
SQL IOPS:10 (每 1M 的項目)SQL IOPS: 10 per 1M items
Medium
Medium
10GB10GB
0.1GB 的記錄0.1GB log
80GB80GB
5GB 的記錄5GB log
分析報告Analytics Reporting
達到 100-300GB 時分割Split when reaching 100-300GB
Medium
Medium
使用相依性Usage dependent
使用相依性Usage dependent
搜尋管理Search Administration
一個資料庫One DB
Low
Low
0.4GB0.4GB
1GB 的記錄1GB log
1GB 的資料1GB data
2GB 的記錄2GB log

服務應用程式儲存區需求與 IOPS 建議Service application storage requirements and IOPS recommendations

服務應用程式Service application 大小估計建議Size estimation recommendation
User ProfileUser Profile
User Profile Service 應用程式與三個資料庫相關聯:設定檔、同步處理及社交標記。The User Profile service application is associated with three databases: Profile, Synchronization, and Social Tagging.
Note: User Profile 資料庫儲存區需求與 IOPS 建議尚未完成測試。如需相關資訊,請查看前文。 Note: The testing for the User Profile database storage requirements and IOPS recommendations is not yet complete. Check back for additional information.
如需 User Profile 資料庫資訊,請參閱<SharePoint Server 中的資料庫類型和描述>。For User Profile database information, see Database types and descriptions in SharePoint Server.
Managed Metadata ServiceManaged Metadata Service
Managed Metadata Service 應用程式具有一個資料庫。此資料庫的大小會受到系統中使用的內容類型數目與關鍵字數目的影響。許多環境都會有多個 Managed Metadata Service 應用程式執行個體。The Managed Metadata service application has one database. The size of the database is affected by the number of content types and keywords used in the system. Many environments will include multiple instances of the Managed Metadata service application.
Secure Store ServiceSecure Store Service
Secure Store 服務應用程式資料庫的大小,取決於存放區中的認證數目與稽核表格中的項目數。對於此資料庫,建議您為每 1,000 個認證配置 5 MB。其 IOPS 是最小數目。The size of the Secure Store service application database is determined by the number of credentials in the store and the number of entries in the audit table. We recommend that you allocate 5 MB for each 1,000 credentials for it. It has minimal IOPS.
狀態服務State Service
狀態服務應用程式具有一個資料庫。建議您為此資料庫配置 1 GB。其 IOPS 是最小數目。The State service application has one database. We recommend that you allocate 1 GB for it. It has minimal IOPS.
Word Automation ServicesWord Automation Services
Word Automation Service 應用程式具有一個資料庫。建議您為此資料庫配置 1 GB。其 IOPS 是最低的。The Word Automation service application has one database. We recommend that you allocate 1 GB for it. It has minimal IOPS.
PerformancePoint ServicesPerformancePoint Services
PerformancePoint Service 應用程式具有一個資料庫。建議您為此資料庫配置 1 GB。其 IOPS 是最小數目。The PerformancePoint service application has one database. We recommend that you allocate 1 GB for it. It has minimal IOPS.
Business Data Connectivity ServiceBusiness Data Connectivity service
Business Data Connectivity Service 應用程式具有一個資料庫。此資料庫很小,且不太可能大幅增長。其 IOPS 是最小數目。The Business Data Connectivity service application has one database. This database is small and significant growth is unlikely. It has minimal IOPS.
App ManagementApp Management
App Management Service 應用程式具有一個資料庫。此資料庫很小,且不太可能大幅增長。其 IOPS 是最小數目。The App Management service application has one database. This database is small and significant growth is unlikely. It has minimal IOPS.
Power PivotPower Pivot
Power Pivot 服務應用程式具有一個資料庫。此資料庫很小,且對 I/O 沒有多大的影響。建議您使用與 SharePoint 內容資料庫相同的 IOPS。請注意,內容資料庫的 I/O 需求與比 Power Pivot 服務應用程式資料庫來得高。The Power Pivot Service application has one database. This database is small and has no significant I/O impact. We recommend that you use the same IOPS as the SharePoint content database. Note that content databases have significantly higher I/O requirements than the Power Pivot service application database.

確認可用性需求Determine availability needs

可用性是指使用者對 SharePoint Server 2016 環境感受到的可用程度。可用系統是具有彈性的系統,也就是說,影響服務的事件並不常發生,即使發生,也會採取及時有效的動作加以解決。Availability is how much a SharePoint Server 2016 environment is perceived by users to be available. An available system is a system that is resilient — that is, incidents that affect service occur infrequently, and timely and effective action is taken when they do occur.

可用性需求可能會大幅增加您的儲存需求。如需詳細資訊,請參閱為 SharePoint Server 打造高可用性架構和策略。此外,也請參閱 SQL Server 2012 白皮書 AlwaysOn 架構指南:使用 AlwaysOn 可用性群組建置高可用性與災害復原解決方案Availability requirements can significantly increase your storage needs. For detailed information, see Create a high 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.

選擇 SQL Server 版本與版次Choose SQL Server version and edition

針對 SharePoint Server 2016,建議您考慮在 SQL Server 2014 with Service Pack 1 (SP1)、SQL Server 2016 或 SQL Server 2017 RTM 企業版上執行您的環境,以利用這些版本所提供的額外效能、可用性、安全性和管理功能。如需這些版本的優點詳細資訊,請參閱<SQL Server 2014 各版本所支援的功能>、<SQL Server 2016 的版本及支援功能>和<SQL Server 2017 的版本及支援功能>。We recommend that for SharePoint Server 2016 you consider running your environment on the Enterprise Edition of SQL Server 2014 with Service Pack 1 (SP1), SQL Server 2016, or SQL Server 2017 RTM to take advantage of the additional performance, availability, security, and management capabilities that these versions provide. For more information about the benefits of these versions, see Features Supported by the Editions of SQL Server 2014, Editions and supported features of SQL Server 2016, and Editions and supported features of SQL Server 2017.

針對 SharePoint Server 2013,建議您考慮在 SQL Server 2008 R2 Service Pack 1 (SP1)、SQL Server 2012 或 SQL Server 2014 企業版上執行您的環境,以利用這些版本所提供的額外效能、可用性、安全性和管理功能。若想進一步了解 SQL Server 2008 R2 SP1、SQL Server 2012、SQL Server 2014 企業版的優點,請參閱<SQL Server 2014 各版本所支援的功能>、<SQL Server 2012 版本支援的功能>、<SQL Server 2008 R2 各版本所支援的功能>。We recommend that for SharePoint Server 2013 you consider running your environment 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 additional performance, availability, security, and management capabilities that these versions provide. For more information about the benefits of SQL Server 2008 R2 with SP1, SQL Server 2012, and SQL Server 2014 Enterprise Edition, see Features Supported by the Editions of SQL Server 2014, Features Supported by the Editions of SQL Server 2012, and Features Supported by the Editions of SQL Server 2008 R2.

特別是,您應考量您是否需要下列功能:In particular, you should consider your need for the following features:

  • 備份壓縮 備份壓縮可加快任何 SharePoint 備份的速度,並且適用於 SQL Server 2008 或更新版本的每個版本中。您只要在備份指令碼中設定壓縮選項,或是將執行 SQL Server 的伺服器預設成會執行壓縮,即可讓您的資料庫備份與傳送的記錄大幅縮減其大小。如需詳細資訊,請參閱 備份壓縮 (SQL Server) (適用於 SQL Server 2014) 和 備份壓縮 (SQL Server) (適用於 SQL Server 2016 和 SQL Server 2017 RTM)。Backup compression Backup compression can speed up any SharePoint backup, and is available in every edition of SQL Server 2008 and later. By setting the compression option in your backup script, or by configuring the server that is running SQL Server to compress by default, you can significantly reduce the size of your database backups and shipped logs. For more information, see Backup Compression (SQL Server) for SQL Server 2014 and Backup Compression (SQL Server) for SQL Server 2016 and SQL Server 2017 RTM.

    注意

    SharePoint Server 不支援 SQL Server 資料壓縮 (Search Service 應用程式資料庫除外)。SQL Server data compression is not supported for SharePoint Server, except for the Search service application databases.

  • 透明資料加密 如果您的安全性需求包括必須使用透明資料加密,則必須使用 SQL Server Enterprise Edition。Transparent data encryption If your security requirements include the need for transparent data encryption, you must use SQL Server Enterprise Edition.

  • 內容部署 如果您要使用內容部署功能,請考慮使用 SQL Server Enterprise Edition,讓系統能夠充分運用資料庫快照。Content deployment If you plan to use the content deployment feature, consider SQL Server Enterprise Edition so that the system can take advantage of database snapshots.

    注意

    若您使用的是不支援資料庫快照的遠端 BLOB 儲存提供者,您就無法使用快照進行內容部署或備份。If you are using a Remote BLOB storage provider that does not support database snapshots, you can't use snapshots for content deployment or backup.

  • 遠端 BLOB 儲存 如果您要在與每個內容資料庫相關聯的檔案以外的資料庫或位置上使用遠端 BLOB 儲存,則必須使用 SQL Server 2014 (SP1)、SQL Server 2016 或 SQL Server 2017 RTM 企業版 (適用於 SharePoint Server 2016) 和 SQL Server 2008 R2 SP1 或 SQL Server 2012 企業版 (適用於 SharePoint Server 2013)。Remote BLOB storage If you want to take advantage of remote BLOB storage to a database or location outside the files associated with each content database, you must use SQL Server 2014 (SP1), SQL Server 2016, or SQL Server 2017 RTM Enterprise Edition for SharePoint Server 2016 and SQL Server 2008 R2 with SP1 or SQL Server 2012 Enterprise Edition for SharePoint Server 2013.

  • 資源管理員 資源管理員是 SQL Server 2008 中導入的技術,可讓您對傳入要求所使用的資源量指定限制,以管理 SQL Server 工作量與資源。資源管理員可讓您根據自己指定的限制對工作量進行區別,以及在受到要求時配置 CPU 與記憶體。如需如何使用資源管理員的詳細資訊,請參閱 資源管理員 (適用於 SQL Server 2014) 和 資源管理員 (適用於 SQL Server 2016)。Resource governor Resource Governor is a technology introduced in SQL Server 2008 to enable you 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 as they are requested, based on the limits that you specify. For more information about how to use Resource Governor, see Resource Governor for SQL Server 2014 and Resource Governor for SQL Server 2016.

    建議您搭配使用資源管理員與 SharePoint Server,以執行下列作業:We recommend that you use Resource Governor with SharePoint Server to:

    • 限制搜尋編目元件的目標 Web 伺服器所能使用的 SQL Server 資源量。根據最佳做法,建議您在系統負載偏低時將編目元件限定為 10% 的 CPU 使用量。Limit the amount of SQL Server resources that the web servers targeted by the search crawl component consume. As a best practice, we recommend limiting the crawl component to 10 percent CPU when the system is under load.

    • 請監視系統中的每個資料庫所使用的資源量。例如,您可以利用資源管理員來判斷資料庫在執行 SQL Server 的電腦間最適當的配置方式。Monitor how many resources are consumed by each database in the system — for example, you can use Resource Governor to help you determine the best placement of databases among computers that are running SQL Server.

  • Microsoft Power Pivot for SharePoint 可讓使用者在 Excel Online 中對他們產生的資料模型與分析進行共用與共同作業,同時自動重新整理這些分析。您必須有 Office Online 才能搭配使用 Excel Online 與 Power Pivot for SharePoint 和 SharePoint Server 2016。您可以使用 SQL Server 2014 (SP1) 或 SQL Server 2016 RTM Enterprise Edition 和 SQL Server Analysis Services,以取得與 SharePoint Server 2016 的商業智慧。不過,您只能搭配使用 Power Pivot for SharePoint 與 SQL Server 2016 RTM,而不能與 SQL Server 2014 (SP1) 搭配使用。Microsoft Power Pivot for SharePoint Enables users to share and collaborate on user-generated data models and analysis in Excel Online while automatically refreshing those analyses. You must have Office Online to use Excel Online with Power Pivot for SharePoint and SharePoint Server 2016. You can use SQL Server 2014 (SP1) or SQL Server 2016 RTM Enterprise Edition and SQL Server Analysis Services for business intelligence with SharePoint Server 2016. However, you can only use Power Pivot for SharePoint with SQL Server 2016 RTM, not with SQL Server 2014 (SP1).

  • Power Pivot for SharePoint 2013 可讓使用者在 Excel 與瀏覽器中對他們產生的資料模型與分析進行共用與共同作業,同時自動重新整理這些分析。這是 SQL Server 2008 R2 Analysis Services (SSAS)資料中心與企業版、SQL Server 2012 SP1 Analysis Services (SSAS) 企業版、SQL Server 2014 Analysis Services (SSAS) 企業版與商業智慧版的一部分。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 part 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.

根據容量與 IO 需求設計儲存架構Design storage architecture based on capacity and I/O requirements

您為環境選取的儲存架構與磁碟類型,可能會對系統效能產生影響。The storage architecture and disk types that you select for your environment can affect system performance.

本節內容:In this section:

選擇儲存架構Choose a storage architecture

SharePoint Server 支援直接連接儲存裝置 (DAS)、儲存區域網路 (SAN) 與網路連接儲存裝置 (NAS) 儲存架構,但 NAS 只能用於依設定會使用遠端 BLOB 儲存的內容資料庫。您應根據您的商務解決方案與現有基礎結構內的因素,做出適當選擇。SharePoint Server supports Direct Attached 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 BLOB storage. Your choice depends on factors within your business solution and your existing infrastructure.

任何儲存架構都必須支援您的可用性需求,並且能夠適當提供 IOPS 與延遲性。要受到支援,系統必須能穩定地在 20 毫秒 (ms) 內傳回資料的第一個位元組。Any storage architecture must support your availability needs and perform adequately in IOPS and latency. To be supported, the system must consistently return the first byte of data within 20 milliseconds (ms).

直接連接儲存裝置 (DAS)Direct Attached Storage (DAS)

DAS 是一種數位儲存系統,直接連接至伺服器或工作站,其間沒有儲存網路。DAS 實體磁碟類型包括序列連接 SCSI (SAS) 與序列連接 ATA (SATA)。DAS is a digital storage system that is directly attached to a server or workstation, without a storage network in between. DAS physical disk types include Serial Attached SCSI (SAS) and Serial Attached ATA (SATA).

一般而言,若共用儲存平台不一定能達到 20 毫秒的回應時間,也不一定有足夠的容量可供平均與尖峰 IOPS 使用時,建議您選擇 DAS 架構。In general, we recommend that you choose a DAS architecture when a shared storage platform can't guarantee a response time of 20 ms and sufficient capacity for average and peak IOPS.

儲存區域網路 (SAN)Storage Area Network (SAN)

SAN 是一種將遠端電腦儲存裝置 (例如磁碟陣列和磁帶庫) 附加至伺服器的架構,藉由這種方式,裝置將顯示為於本機端附加於作業系統之下 (例如,區塊儲存)。SAN is an architecture to attach remote computer storage devices (such as disk arrays and tape libraries) to servers in such a way that the devices appear as locally attached to the operating system (for example, block storage).

一般而言,若共用儲存區對您的組織很有助益時,我們會建議您選擇 SAN。In general, we recommend that you choose a SAN when the benefits of shared storage are important to your organization.

共用儲存區的好處包括:The benefits of shared storage include the following:

  • 較容易在伺服器之間重新配置磁碟儲存區。Easier to reallocate disk storage between servers.

  • 可為多部伺服器提供服務。Can serve multiple servers.

  • 可存取的磁碟沒有數量上的限制。No limitations on the number of disks that can be accessed.

網路連接儲存裝置 (NAS)Network Attached Storage (NAS)

NAS 裝置是一種連接至網路的自助式電腦。它唯一的目的,就是將以檔案為基礎的資料儲存服務提供給網路上的其他裝置。NAS 裝置上的作業系統與其他軟體可提供資料儲存、檔案系統、存取檔案等功能,並且有能力管理這些功能 (例如檔案儲存)。A NAS unit is a self-contained computer 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 provide the functionality of data storage, file systems, and access to files, and the management of these functionalities (for example, file storage).

注意

NAS 只能用於依設定會使用遠端 BLOB 儲存 (RBS) 的內容資料庫。任何網路儲存架構皆必須在 1 毫秒內回應 ping,並且在 20 毫秒內傳回資料的第一個位元組。此限制不適用於本機 SQL Server FILESTREAM 提供者,因為此提供者只會將資料儲存在相同伺服器上的本機位置。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 first byte of data within 20 ms. This restriction does not apply to the local SQL Server FILESTREAM provider, because it only stores data locally on the same server.

注意

當您使用Internet Small Computer System Interface (iSCSI),並將其視為 NAS 通訊協定時,可能會產生某些混淆。若您透過 Common Internet File System (CFIS) 存取 iSCSI 儲存區,則會是 NAS 通訊協定。這表示,您無法將此儲存區用於未設定成使用 RBS 的內容資料庫。但若您透過本機連接的硬碟存取此 iSCSI 儲存區,此儲存區即會被視為 SAN 架構。這表示您可以將其用於 NAS。Some confusion exists about if you use the Internet Small Computer System Interface (iSCSI) and assume that it is a NAS protocol. If you access this iSCSI storage through the Common Internet File System (CFIS), it is a NAS protocol. This means that you can't use this storage with content databases if they aren't configured to use RBS. If however, you access this iSCSI storage through a locally attached hard disk, it is considered a SAN architecture. This means that you can use it with NAS.

選擇磁碟類型Choose disk types

您在系統中使用的磁碟類型可能會對可靠性與效能產生影響。在其他因素不變的情況下,磁碟機愈大,平均搜尋時間就愈長。SharePoint Server 支援下列類型的磁碟機:The disk types that you use in the system can affect reliability and performance. All else being equal, larger drives increase mean seek time. SharePoint Server supports the following types of drives:

  • 小型電腦系統介面 (SCSI)Small Computer System Interface (SCSI)

  • 序列進階技術連接 (SATA)Serial Advanced Technology Attachment (SATA)

  • 序列連接 SCSI (SAS)Serial-attached SCSI (SAS)

  • 光纖通道 (FC)Fibre Channel (FC)

  • 整合式電子裝置 (IDE)Integrated Device Electronics (IDE)

  • 固態硬碟 (SSD) 或快閃磁碟機Solid State Drive (SSD) or Flash Disk

    如需在 SQL Server 中使用固態硬碟作為儲存區的相關資訊,請參閱 SQL Server PRO 文章:在 SQL Server 儲存解決方案中使用固態硬碟For information about using solid state drives for storage in SQL Server, see the SQL Server PRO article Using Solid State Disks in SQL Server Storage Solutions.

選擇 RAID 類型Choose RAID types

RAID (獨立磁碟容錯陣列) 常用來改善個別磁碟的效能特性 (藉由去除數個磁碟間的資料) 以及防止個別磁碟發生故障。RAID (Redundant Array of Independent Disks) is often used to both improve the performance characteristics of individual disks (by striping data across several disks) and to provide protection from individual disk failures.

SharePoint Server 支援所有 RAID 類型。但建議您使用 RAID 10 或供應商特有而具有同等效能的 RAID 解決方案。All RAID types are supported for SharePoint Server. However, we recommend that you use RAID 10 or a vendor-specific RAID solution that has equivalent performance.

在設定 RAID 陣列時,請確實根據供應商提供的偏移適當調校檔案系統。When you configure a RAID array, make sure that you align the file system to the offset that is supplied by the vendor.

如需為 SQL Server 佈建 RAID 的詳細資訊,請參閱 RAIDFor more information about provisioning RAID for SQL Server, see RAID.

估計記憶體需求Estimate memory requirements

SharePoint Server 所需的記憶體,與您在執行 SQL Server 的伺服器上主控的內容資料庫大小有直接的關聯。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 針對執行 SQL Server 的電腦所建議的 RAMRAM recommended for computer running SQL Server
小型生產部署的最低需求Minimum for small production deployments
8 GB8 GB
中型生產部署的最低需求Minimum for medium production deployments
16 GB16 GB
2 TB 以內的建議Recommendation for up to 2 terabytes
32 GB32 GB
2 TB 至 5 TB 的建議Recommendation for the range of 2 terabytes to 5 terabytes
64 GB64 GB
超過 5 TB 的建議Recommendation for more than 5 terabytes
超過 64 GB 的額外 RAM 可改善 SQL Server 快取速度Additional RAM over 64 GB can improve SQL Server caching speed

注意

由於在 SharePoint Server 環境中有配送資料的需求,因此這些值高於我們針對 SQL Server 而建議的最小值。如需 SQL Server 系統需求的詳細資訊,請參閱安裝 SQL Server 2014 的硬體與軟體需求安裝 SQL Server 2016 的硬體和軟體需求These values are higher than those recommended as the minimum values for SQL Server because of the distribution of data required for a SharePoint Server environment. For more information about SQL Server system requirements, see Hardware and Software Requirements for Installing SQL Server 2014 and Hardware and Software Requirements for Installing SQL Server 2016.

如需 SQL Server 容量限制與規格的相關資訊,請參閱 SQL Server 版本的計算容量限制SQL Server 的最大容量規格For information about SQL Server capacity limits and specifications see Compute Capacity Limits by Edition of SQL Server and Maximum Capacity Specifications for SQL Server.

其他可能影響到所需記憶體的因素包括:Other factors that may influence the memory that is required include the following:

  • 是否使用 SQL Server 鏡像。The use of SQL Server mirroring.

  • 是否經常使用大於 15 MB 的檔案。The frequent use of files larger than 15 megabytes (MB).

了解網路拓撲需求Understand network topology requirements

請規劃伺服器陣列以內和之間的網路連線。建議您使用低延遲的網路。Plan the network connections within and between farms. We recommend that you use a network that has low latency.

下表提供若干最佳做法與建議:The following list provides some best practices and recommendations:

  • 伺服器陣列中的所有伺服器對於執行 SQL Server 的伺服器均應具有 LAN 頻寬與延遲。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 1 millisecond.

  • 建議您不要使用此類廣域網路 (WAN) 拓撲:從伺服器陣列的其他元件,透過延遲大於 1 毫秒的網路從遠端部署執行 SQL Server 的伺服器。因為此類拓撲尚未經過測試。We do not recommend a wide area 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.

  • 若您要使用 SQL Server AlwaysOn 實作套件、鏡像、記錄傳送或容錯移轉叢集以保有最新的遠端網站,請規劃適當的 WAN 網路。Plan for an adequate WAN network if you plan to use SQL Server the AlwaysOn implementation suite, mirroring, log shipping, or Failover Clustering to keep a remote site up-to-date.

  • 我們建議,Web 伺服器與應用程式伺服器應具有兩張網路介面卡:一張網路介面卡用來處理使用者流量,另一張用來處理與執行 SQL Server 的伺服器之間的通訊。We recommend that web servers and application servers have two network adapters: one network adapter to handle user traffic and the other to handle communication with the servers that are running SQL Server.

    注意

    若您使用 iSCSI,請確定每張網路介面卡僅專用於網路通訊或 iSCI,而非同時用於兩者。If you use iSCSI, make sure each network adapter is dedicated to either network communication or iSCI, not both.

設定 SQL ServerConfigure SQL Server

以下幾節將說明如何妥善規劃以設定 適用於 SharePoint Server 的 SQL Server。The following sections describe how to plan to configure SQL Server for SharePoint Server.

本節內容:In this section:

估計所需的伺服器數量Estimate how many servers are required

一般而言,SharePoint Server 會以充分運用 SQL Server 的延伸模組作為其設計方向。例如,相較於僅使用幾部大型伺服器,環境中若有許多執行 SQL Server 的中型伺服器,SharePoint Server 會有較理想的執行效能。In general, SharePoint Server is designed to take 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 only several large servers.

一律將 SQL Server 放置在未執行任何其他伺服器陣列角色或裝載任何其他應用程式之資料庫的專用伺服器上。這項建議的唯一例外是您在開發或非效能導向測試環境的獨立伺服器上部署系統時。雖然 SQL Server 可以在與 SharePoint 相同的伺服器上執行,但是建議在不同的伺服器上執行 SQL Server,以獲得較佳效能。Always put SQL Server on a dedicated server that is not running any other farm roles or hosting databases for any 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 test environment. Although SQL Server can run on the same server as SharePoint, we recommend running SQL Server on a separate server for better performance.

以下提供一般指引,說明何時應另行部署將會執行 SQL Server 執行個體的伺服器:The following is general guidance for when to deploy an additional server that will run a SQL Server instance:

  • 基於容量考量而執行四部以上的 Web 伺服器時,請另行新增資料庫伺服器。Add an additional database server when you have more than four web servers that are running at capacity.

  • 當您目前的伺服器已達到 RAM、CPU、磁碟 IO 輸送量、磁碟容量或網路輸送量的有效資源上限時,請另行新增資料庫伺服器。Add an additional database server when your current server has reached its effective resource limits of RAM, CPU, disk IO throughput, disk capacity, or network throughput.

如需詳細資訊,請參閱 SQL Server 版本的計算容量限制SQL Server 的最大容量規格For more information, see Compute Capacity Limits by Edition of SQL Server and Maximum Capacity Specifications for SQL Server.

若您在執行 Secure Store 服務應用程式時想要提升安全認證儲存區,建議您將 Secure Store 資料庫放置在僅限一個管理員可存取的個別資料庫執行個體上。To promote secure credential storage when you are running the Secure Store service application, we recommend that the Secure Store database be hosted on a separate database instance where access is limited to one administrator.

設定儲存區與記憶體Configure storage and memory

在執行 SQL Server 的伺服器上,建議每個 CPU 的 L2 快取至少要有 2 MB,以提升記憶體效能。On the server that is running SQL Server, we recommend that the L2 cache per CPU have a minimum of 2 MB to improve memory.

遵循供應商的儲存設定建議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 system.

如果您沒有廠商的指引,則建議使用可從 Windows Server 2012 R2 使用的 PowerShell 儲存 Cmdlet。如需詳細資訊,請參閱 Windows PowerShell 中的儲存 CmdletIf you do not have guidance from your vendor, we recommend using the PowerShell storage cmdlets that are available for Windows Server 2012 R2. For more information, see Storage Cmdlets in Windows PowerShell.

盡可能提供最多資源Provide as many resources as possible

確定對磁碟的 SQL Server I/O 通道並未讓其他應用程式共用,例如分頁檔與 Internet Information Services (IIS) 記錄。Ensure that the SQL Server I/O channels to the disks are not shared by other applications, such as the paging file and Internet Information Services (IIS) logs.

盡可能提供較大的匯流排頻寬。匯流排頻寬愈大,可靠性與效能就愈高。請考量到磁碟並非匯流排頻寬的唯一使用者。例如,您也必須將網路存取納入考量。Provide as much bus bandwidth as possible. Greater bus bandwidth helps improve reliability and performance. Consider that the disk is not the only user of bus bandwidth — for example, you must also account for network access.

設定 SQL Server 選項Set SQL Server options

下列 SQL Server 設定與選項應在您部署 SharePoint Server 之前設定。The following SQL Server settings and options should be configured before you deploy SharePoint Server.

  • 在主控 SQL Server 及支援 SharePoint Server 的伺服器上,請不要啟用「自動建立統計資料」。SharePoint Server 會在佈建與升級期間設定必要的設定。「自動建立統計資料」可能會大幅變更從某個 SQL Server 執行個體對另一個 SQL Server 執行個體執行查詢的計劃。因此,為了讓所有客戶獲得一致的支援,SharePoint Server 會視需要提供查詢的編碼提示,以期在各種情況下都能達到最佳效能。Do not enable auto-create statistics on a server that hosts SQL Server and supports SharePoint Server. SharePoint Server configures the required settings upon provisioning and upgrade. Auto-create statistics can significantly 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.

  • 為確保最佳效能,強烈建議您將「平行處理原則的最大程度 (MAXDOP)」**** 設為 1 (主控 SharePoint Server 資料庫的 SQL Server 執行個體)。如需如何設定平行處理原則的最大程度的詳細資訊,請參閱設定平行處理原則的最大程度伺服器設定選項To ensure optimal performance, we strongly recommend that you set max degree of parallelism (MAXDOP) to 1 SQL Server instances that host SharePoint Server databases. For more information about how to set max degree of parallelism, see Configure the max degree of parallelism Server Configuration Option.

設定資料庫Configure databases

下列指引說明您在環境中設定每個資料庫時所應採行的最佳規劃方式。The following guidance describes best practices to plan for as you configure each database in your environment.

將您的資料分散到磁碟間並設定優先順序Separate and prioritize your data among disks

理想狀況下,您應將 tempdb 資料庫、內容資料庫、使用狀況資料庫、搜尋資料庫與 SQL Server 2014 (SP1)、SQL Server 2016、SQL Server 2017 RTM、SQL Server 2008 R2 SP1 及 SQL Server 2012 交易記錄放置在不同的實體硬碟上。Ideally, you should place the tempdb database, content databases, Usage database, search databases, and SQL Server 2014 (SP1), SQL Server 2016, SQL Server 2017 RTM, SQL Server 2008 R2 with SP1 and SQL Server 2012 transaction logs on separate physical hard disks.

下表提供為資料設定優先順序時的若干最佳做法與建議:The following list provides some best practices and recommendations for prioritizing data:

  • 在較快速的磁碟間設定資料的優先順序時,請使用下列排名:When you prioritize data among faster disks, use the following ranking:

    • Tempdb 資料檔案與交易記錄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 data over logs.

  • 測試與客戶資料顯示,SharePoint Server 伺服器陣列的效能可能因 tempdb 的磁碟 I/O 不足而嚴重下滑。若避免發生此問題,請為 tempdb 配置專用磁碟。若預期或已監控到高工作量 (亦即,平均讀取動作或平均寫入動作需要 20 毫秒以上才能完成),您可能必須將檔案分散到各個磁碟間,或將更換為速度較快的磁碟,以緩解瓶頸。Testing and customer data show that SharePoint Server farm performance can be significantly impeded by insufficient disk I/O for tempdb. To avoid this issue, allocate dedicated disks for tempdb. If a high workload is projected or monitored — that is, the average read action or the average write action requires more than 20 ms — you might have to ease the bottleneck by either separating the files across disks or by replacing the disks with faster disks.

  • 為達到最佳效能,請將 tempdb 放置在 RAID 10 陣列上。tempdb 資料檔的數目應與核心 CPU 的數目相等,且各個 tempdb 資料檔應設為相同大小。此時請將雙核心處理器計為兩個 CPU。各個支援超執行緒的處理器應分別計為單一 CPU。如需詳細資訊,請參閱最佳化 tempdb 效能For best performance, place the tempdb on a RAID 10 array. The number of tempdb data files should equal the number of core CPUs, and the tempdb data files should be set at an equal size. Count dual core processors as two 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 because the files are too small to warrant a whole disk or stripe, or you have a shortage of disk space, put files that have different usage patterns on the same disk to minimize 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 primary filegroup for the database.

  • 將檔案分散到不同磁碟。Distribute the files across separate disks.

  • 資料檔的數目應小於或等於核心 CPU 的數目。此時請將雙核心處理器計為兩個 CPU。各個支援超執行緒的處理器應分別計為單一 CPU。The number of data files should be less than or equal to the number of core CPUs. Count dual core processors as two CPUs for this purpose. Count each processor that supports hyper-threading as a single CPU.

  • 建立大小相同的資料檔。Create data files of equal size.

重要

雖然您可以使用 SharePoint Server 的內建備份與復原工具來備份及復原多個資料檔,但若您在相同位置上覆寫,則這些工具無法將多個資料檔還原到不同位置。因此,強烈建議您將多個資料檔用於內容資料庫時,應使用 SQL Server 備份與復原工具。如需如何備份及復原 SharePoint Server 的詳細資訊,請參閱在 SharePoint Server 中規劃備份和修復Although you can use the backup and recovery tools that are built in to SharePoint Server to back up and recover multiple data files, if you overwrite in the same location, the tools 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 use SQL Server backup and recovery tools. For more information about how to back up and recover SharePoint Server, see Plan for backup and recovery in SharePoint Server.

如需如何建立及管理檔案群組的詳細資訊,請參閱實體資料庫檔案與檔案群組For more information about how to create and manage filegroups, see Physical Database Files and Filegroups.

限制內容資料庫大小以方便管理Limit content database size to improve manageability

規劃調整資料庫大小的作業,以提升環境的可管理性、效能與升級的簡易性。Plan for database sizing that will improve manageability, performance, and ease of upgrade for your environment.

為確保系統效能,建議您將內容資料庫的大小限定為 200 GB (當特定使用案例與情況支援較大的大小時除外)。如需內容資料庫大小限制的詳細資訊,請參閱 SharePoint Server 2016 的軟體界限及限制中的<內容資料庫限制>一節。To help ensure system performance, we recommended that you limit the size of content databases to 200 GB, except when specific usage scenarios and conditions support larger sizes. For more information about content database size limits, see the "Content database limits" section in Software boundaries and limits for SharePoint Server 2016.

一般情況下,我們建議網站集合不應超過 100 GB,除非那是資料庫中唯一的網站集合,因此您可以在必要時使用 SharePoint Server 的細微備份工具將網站集合移至其他資料庫。We generally recommend that a site collection should not exceed 100 GB unless it is the only site collection in the database so that you can use the SharePoint Server granular backup tools to move a site collection to another database if you need to.

主動管理資料與記錄檔案的增長Proactively manage the growth of data and log files

建議您考量下列建議,以主動管理資料與記錄檔的增長:We recommend that you proactively manage the growth of data and log files by considering the following recommendations:

  • 盡可能將所有資料檔案與記錄檔預先擴增至預期的最終大小。As much as possible, pre-grow all data and log files to their expected final size.

  • 建議您啟用以安全為考量的自動擴增功能。請不要使用預設的自動擴增設定。設定自動擴增功能時,請考量下列準則:We recommend that you enable autogrowth for safety reasons. Do not rely on the default autogrowth settings. Consider the following guidelines when you configure autogrowth:

    • 若您所規劃的內容資料庫超過建議的大小 (200 GB),請將資料庫自動擴增值設為固定的 MB 數,而不要設為百分比。這將會降低 SQL Server 增加檔案大小的頻率。增加檔案大小是一種會以空白頁面填入新空間的阻礙性動作。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 will reduce 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.

    • 若預期內容資料庫的計算大小在明年內不會達到建議的大小上限 200 GB,請使用 ALTER DATABASE MAXSIZE 屬性,將大小上限設為該資料庫在一年內預期會達到的大小上限 (保留 20% 的誤差範圍)。請定期檢視此設定,以確定該值仍適用 (以過去的成長率為準)。If the calculated size of the content database is not expected to reach the recommended maximum size of 200 GB within the next year, set it to the maximum size the database is predicted to reach in a year — with 20 percent additional margin for error — by using the ALTER DATABASE MAXSIZE property. Periodically review this setting to make sure that it is still an appropriate value, depending on past growth rates.

  • 在磁碟間保留至少 25% 的可用空間,供擴增與尖峰使用模式之用。若您藉由在 RAID 陣列中新增磁碟或配置更多儲存區來管理擴增,請密切監視磁碟大小以避免空間不足。Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns. If you are managing growth by adding disks to a RAID array or allocating more storage, monitor disk size closely to avoid running out of space.

驗證及監視儲存區與 SQL Server 的效能Validate and monitor storage and SQL Server performance

測試您的效能與備份解決方案在硬體上是否可讓您符合服務等級協定 (SLA)。特別是,請為執行 SQL Server 的電腦測試其 I/O 子系統,以確定效能良好。Test 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.

測試您所使用的備份解決方案,以確定它可在可用的維護時程內完成系統備份。若備份解決方案無法符合您的企業所需的 SLA,請考慮使用累加備份解決方案,例如 Microsoft System Center Data Protection Manager。Test the backup solution that you are using to make sure that it can back up the system within the available maintenance window. If the backup solution can't meet the SLAs your business requires, consider using an incremental backup solution such as Microsoft System Center Data Protection Manager.

請務必對執行 SQL Server 的伺服器追蹤下列資源元件:CPU、記憶體、快取/點擊比率與 I/O 子系統。若有一或多個元件疑似變慢或負載過重,請根據目前與預測的工作量分析適當的策略。如需詳細資訊,請參閱效能的監視與調整 (適用於 SQL Server 2014 (SP1)) 與 效能的監視與調整 (適用於 SQL Server 2016 和 SQL Server 2017 RTM)。It is important to track the following resource components of a server that is running SQL Server: CPU, memory, cache/hit ratio, and I/O subsystem. When one or more of the components seems slow or overburdened, analyze the appropriate strategy based on the current and projected workload. For more information, see Monitor and Tune for Performance for SQL Server 2014 (SP1) and Monitor and Tune for Performance for SQL Server 2016 and SQL Server 2017 RTM.

以下幾節將列出我們建議您用來對執行於 SharePoint Server 環境中的 SQL Server 資料庫監視效能的效能計數器。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 environment. Also listed are approximate healthy values for each counter.

如需如何監視效能及使用效能計數器的詳細資訊,請參閱 Windows 效能監視器設定監視效能For details about how to monitor performance and use performance counters, see Windows Performance Monitor and Monitoring Performance.

要監視的 SQL Server 計數器SQL Server counters to monitor

請監視下列 SQL Server 計數器,以確定伺服器的狀況:Monitor the following SQL Server counters to ensure the health of your servers:

  • 一般統計資料 此物件會提供計數器,用以監視一般的全伺服器活動,例如目前連線數目,以及每秒從執行 SQL Server 執行個體的電腦上連線及中斷連線的使用者人數。請考慮監視下列計數器:General statistics This object provides counters to monitor general server-wide activity, such as the number of current connections and the number of users connecting and disconnecting per second from computers that are running an instance of SQL Server. Consider monitoring the following counter:

    • 使用者連線 此計數器會顯示您執行 SQL Server 之電腦上的使用者連線數目。若您發現此數比起基準已增加了 500%,就可能出現效能下降的情況。User connections This counter shows the number of user connections on your computer that is running SQL Server. If you see this number increase by 500 percent from your baseline, you may see a performance reduction.
  • 資料庫 此物件提供的計數器可監視大量複製作業、備份與還原輸送量以及交易記錄活動。監視交易與交易記錄,可判斷資料庫中發生了多少使用者活動,以及交易記錄的滿溢程度。使用者活動的數量可決定資料庫的效能,並且可能影響記錄大小、鎖定與複寫。監視低層級記錄活動以測量使用者活動與資源使用量,可協助您找出效能瓶頸。請考慮監視下列計數器:Databases This object provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine 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 you 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 for your baseline and to help you troubleshoot issues.
  • 鎖定 此物件可提供 SQL Server 對個別資源類型之鎖定的相關資訊。請考慮監視下列計數器:Locks This object provides information about SQL Server locks on individual resource types. Consider monitoring the following counters:

    • 平均等候時間 (毫秒) 此計數器會顯示每個導致等候狀況之鎖定要求的平均等候時間。Average Wait Time (ms) This counter shows the average amount of wait time for each lock request that resulted in a wait.

    • 鎖定等候時間 (毫秒) 此計數器會顯示上一秒之鎖定的等候時間。Lock Wait Time (ms) This counter shows the wait time for locks in the last second.

    • 鎖定等候/秒 此計數器會顯示每秒內無法立即獲得應允而必須等候資源的鎖定數目。Lock waits/sec This counter shows the number of locks per second that couldn't be satisfied immediately and had to wait for resources.

    • 死結數目/秒 此計數器會顯示執行 SQL Server 的電腦上每秒的死結數目。此數不應超過 0。Number of deadlocks/sec This counter shows the number of deadlocks on the computer that is running SQL Server per second. This should not increase above 0.

  • 閂鎖 此物件所提供的計數器可監視名為閂鎖的內部 SQL Server 資源鎖定。監視閂鎖以判斷使用者活動與資源使用量,可協助您找出效能瓶頸。請考慮監視下列計數器:Latches This object provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you identify performance bottlenecks. Consider monitoring the following counters:

    • 平均閂鎖等候時間 (毫秒) 此計數器會顯示必須等候之閂鎖要求的平均閂鎖等候時間。Average Latch Wait Time (ms) This counter shows the average 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 統計資料 此物件所提供的計數器可監視編譯以及傳送至 SQL Server 執行個體的要求類型。監視查詢的編譯與重新編譯數目以及 SQL Server 執行個體所接收的批次數目,可讓您了解 SQL Server 處理使用者查詢的速度以及查詢最佳化工具處理查詢的效率。請考慮監視下列計數器: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 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 編譯/秒 此計數器會指出每秒輸入編譯代碼路徑的次數。SQL Compilations/sec This counter indicates the number of times the compile code path is entered per second.

    • SQL 重新編譯/秒 此計數器會指出陳述式每秒重新編譯的次數。SQL Re-Compilations/sec This counter indicates the number statement recompiles per second.

  • 緩衝區管理員 此物件所提供的計數器可監視 SQL Server 使用記憶體來儲存資料頁面、內部資料結構與程序快取的情形,另外也有計數器可監視 SQL Server 讀取及寫入資料庫頁面時的實體 I/O。請考慮監視下列計數器:Buffer Manager This object provides counters to monitor how SQL Server uses memory to store data pages, internal data structures, and the procedure cache, and also counters to monitor the physical I/O as SQL Server reads and writes database pages. Consider monitoring the following counter:

    • 緩衝區快取點擊比率Buffer Cache Hit Ratio

    • 此計數器會顯示直接從緩衝區快取中取得而無須從磁碟讀取的頁面百分比。此比率的計算方式,是將快取點擊的總數除以快取查閱的總數 (以過去數千個頁面存取作為樣本)。由於從快取讀取的成本遠低於從磁碟讀取,因此您會希望此比率是偏高的。一般而言,只要增加 SQL Server 的可用記憶體,即可提高緩衝區快取點擊比率。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 cache hits divided by the total number of cache lookups over the last few thousand page accesses. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the memory available to SQL Server.

  • 規劃快取 此物件所提供的計數器可監視 SQL Server 使用記憶體來儲存各種物件的情形,例如預存程序、未備妥與已備妥的 Transact-SQL 陳述式與觸發程序等。請考慮監視下列計數器:Plan Cache This object provides counters to monitor how SQL Server uses memory to store objects such as stored procedures, unprepared and prepared Transact-SQL statements, and triggers. Consider monitoring the following counter:

    • 快取點擊比率Cache Hit Ratio

    • 此計數器會指出計劃的快取點擊與查閱之間的比率。This counter indicates the ratio between cache hits and lookups for plans.

要監視的實體伺服器計數器Physical server counters to monitor

請監視下列計數器,以確定您執行 SQL Server 的電腦處於何種狀況:Monitor the following counters to ensure the health of your computers that are running SQL Server:

  • 處理器:處理器時間百分比:總計 此計數器會顯示處理器在閒置時間外執行應用程式或作業系統程序的時間百分比。在執行 SQL Server 的電腦上,此計數器應維持在 50% 到 75% 之間。若出現持續超載的情況,請調查是否有異常的處理活動,或伺服器是否需要更多 CPU。Processor: % Processor Time: _Total This counter shows the percentage of time that the processor is executing application or operating system processes other than Idle. On the computer that is running SQL Server, this counter should be kept between 50 percent and 75 percent. In case of constant overloading, investigate whether there is abnormal process activity or if the server needs additional CPUs.

  • 系統:處理器佇列長度 此計數器會顯示處理器佇列中的執行緒數目。請監視此計數器,確定其值仍在核心 CPU 數目的兩倍以下。System: Processor Queue Length This counter shows the number of threads in the processor queue. Monitor this counter to make sure that it remains less than two times the number of core CPUs.

  • 記憶體:可用的 Mbytes 此計數器會顯示電腦上執行的程序所能使用的實體記憶體 (以 MB 為單位)。請監視此計數器,以確定您至少保留了可用實體 RAM 總計的 20%。Memory: Available Mbytes This counter shows the physical memory, in megabytes, available to processes running on the computer. Monitor this counter to make sure that you maintain a level of at least 20 percent of the total available physical RAM.

  • 記憶體:頁面/秒 此計數器會顯示在磁碟上讀取或寫入頁面的速率,以解決硬分頁錯誤。請監視此計數器,確定其值保持在 100 以下。Memory: 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 sure that it remains under 100.

如需詳細資訊和記憶體疑難排解方法,請參閱下列資源:For more information and memory troubleshooting methods, see the following resources:

如需詳細資訊與記憶體疑難排解方法,請參閱監視記憶體使用量 (適用於 SQL Server 2008 R2 SP1)、 監視記憶體使用量 (適用於 SQL Server 2012) 與 監視記憶體使用量 (適用於 SQL Server 2014)。For more information and memory troubleshooting methods, see Monitoring Memory Usage for SQL Server 2008 R2 with SP1, Monitoring Memory Usage for SQL Server 2012, and Monitor Memory Usage for SQL Server 2014.

要監視的磁碟計數器Disk counters to monitor

監視下列計數器可確認磁碟的狀況。請注意,下列值是以一段時間作為計算基準,而不是突發性尖峰期間內的值,也不是以單一測量為準的值。Monitor the following counters to ensure the health of disks. Note that the following values represent values measured over time — not values that occur during a sudden spike and not values that are based on a single measurement.

  • 實體磁碟:磁碟時間百分比:資料磁碟機 此計數器會顯示選取的磁碟機忙於回應讀取或寫入要求的經過時間百分比;此為常見的磁碟忙碌程度指標。若 實體磁碟:磁碟時間百分比 計數器偏高 (超過 90%),請檢查 實體磁碟:目前磁碟佇列長度 計數器,以確認有多少個系統要求正在等候磁碟存取。等候中的 I/O 要求數應維持在組成實體磁碟之主軸數的 1.5 到 2 倍以下。Physical Disk: % Disk Time: DataDrive This counter shows the percentage of elapsed time that the selected disk drive is busy servicing read or write requests-it is a general indicator of how busy the disk is. If the PhysicalDisk: % Disk Time counter is high (more than 90 percent), check the PhysicalDisk: Current Disk Queue Length counter to see how many system requests are waiting for disk access. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles that make up the physical disk.

  • 邏輯磁碟:磁碟傳輸/秒 此計數器會顯示在磁碟上執行讀取與寫入作業的速率。使用此計數器可適當監視成長趨勢與預測。Logical Disk: Disk Transfers/sec This counter shows the rate 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 Disk: Disk Write Bytes/sec These counters show the rate at which bytes are transferred from the disk during read or write operations.

  • 邏輯磁碟:平均磁碟位元組/讀取 此計數器會顯示在讀取作業期間從磁碟傳輸的平均位元組數。此值可反映磁碟延遲 較大的讀取作業可能會導致延遲略為增加。Logical Disk: Avg. Disk Bytes/Read This counter shows the average number of bytes transferred from the disk during read operations. This value can reflect disk latency — larger read operations can result in slightly increased latency.

  • 邏輯磁碟:平均磁碟位元組/寫入 此計數器會顯示在寫入作業期間傳輸至磁碟的平均位元組數。此值可反映磁碟延遲 較大的寫入作業可能會導致延遲略為增加。Logical Disk: Avg. Disk Bytes/Write This counter shows the average number of bytes transferred to the disk during write operations. This value can reflect disk latency — larger write operations can result in slightly increased latency.

  • 邏輯磁碟:目前磁碟佇列長度 此計數器會顯示在收集效能資料時磁碟上仍待處理的要求數目。此計數器的值愈低愈好。若每個磁碟的值大於 2,表示可能有瓶頸存在,應加以調查。這表示以 4 個磁碟組成的邏輯單元 (LUN) 可接受的最大值為 8。瓶頸可能會產生可延伸至目前存取磁碟之伺服器以外的積存,而導致使用者陷入長時間的等候。瓶頸的可行解決方案包括為 RAID 陣列新增更多磁碟、將現有磁碟更換為較快速的磁碟,以及將部分資料移至其他磁碟。Logical Disk: Current Disk 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 better. Values greater than 2 per disk may indicate a bottleneck and should be investigated. This means that a value of up to 8 may be acceptable for a logical unit (LUN) made up of 4 disks. Bottlenecks can create a backlog that can spread beyond the current server that is accessing the disk and result in long wait times for users. Possible solutions to a bottleneck are to add more disks to the RAID array, replace existing disks with faster disks, or move some data to other disks.

  • 邏輯磁碟:平均磁碟佇列長度 此計數器會顯示在取樣間隔期間為選取的磁碟排入佇列的讀取與寫入要求的平均數目。其規則是,每個主軸的待處理讀取與寫入要求數不應超過兩個。但由於儲存區虛擬化與不同設定之間的 RAID 層級有所差異,此值可能不易測量。您可以檢查是否有磁碟佇列長度與磁碟延遲都超出平均值的情況存在。若兩者都超出平均值,表示儲存陣列快取可能使用過度,或是與其他應用程式共用的主軸可能影響到效能。Logical Disk: Avg. Disk Queue Length This counter shows the average number of both read and write requests that were queued for the selected disk during the sample interval. The rule is that there should be two or fewer outstanding read and write requests per spindle. But this can be difficult to measure because of storage virtualization and differences in RAID levels between configurations. Look for larger than average disk queue lengths in combination with larger than average disk latencies. This combination can indicate that the storage array cache is being overused or that spindle sharing with other applications is affecting performance.

  • 邏輯磁碟:平均磁碟秒數/讀取邏輯磁碟:平均磁碟秒數/寫入 這些計數器會顯示對磁碟進行讀取或寫入作業的平均秒數。請監視這些計數器,確定其值維持在磁碟容量的 85% 以下。若讀取或寫入作業超過磁碟容量的 85%,磁碟存取時間將會遽增。若要確認您的硬體所需的容量,請參閱供應商文件,或使用 Diskspd 公用程式、儲存測試工具加以計算。如需詳細資訊,請參閱 Diskspd 公用程式:健全儲存測試工具 (將取代 SQLIO)Logical Disk: Avg. Disk sec/Read and Logical Disk: Avg. Disk sec/Write These counters show the average time, in seconds, of a read or write operation to the disk. Monitor these counters to make sure that they remain below 85 percent of the disk capacity. Disk access time 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 use the Diskspd Utility, storage testing tool to calculate it. For more information, see Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO).

    • 邏輯磁碟:平均磁碟秒數/讀取 此計數器會顯示從磁碟執行讀取作業的平均秒數。在調整得當的系統上,記錄的理想值會介於 1 到 5 毫秒之間 (快取陣列上的理想值為 1 毫秒),資料的值則介於 4 到 20 毫秒之間 (最好小於 10 毫秒)。尖峰期間可能會出現偏高的遲延。但若有規律地出現偏高值,您即應調查原因。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 system, ideal values are from 1 through 5 ms for logs (ideally 1 ms on a cached array), and from 4 through 20 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 cause.

    • 邏輯磁碟:平均磁碟秒數/寫入 此計數器會顯示對磁碟執行寫入作業的平均秒數。在調整得當的系統上,記錄的理想值會介於 1 到 5 毫秒之間 (快取陣列上的理想值為 1 毫秒),資料的值則介於 4 到 20 毫秒之間 (最好小於 10 毫秒)。尖峰期間可能會出現偏高的遲延。但若有規律地出現偏高值,您即應調查原因。Logical Disk: Avg. Disk sec/Write This counter shows the average time, in seconds, of a write operation to the disk. On a well-tuned system, ideal values are from 1 through 5 ms for logs (ideally 1 ms on a cached array), and from 4 through 20 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 cause.

      當您將 RAID 設定用於 邏輯磁碟:平均磁碟位元組/讀取邏輯磁碟:平均磁碟位元組/寫入 計數器時,請使用下表所列的公式推算磁碟上的輸入與輸出速率。When you are using RAID configurations with the Logical Disk: Avg. Disk Bytes/Read or Logical Disk: Avg. Disk Bytes/Write counters, use the formulas listed in the following table to determine the rate of input and output on the disk.

RAID 層級RAID level 公式Formula
RAID 0RAID 0
每一磁碟的 I/O = (讀取數 + 寫入數) / 磁碟數目I/Os per disk = (reads + writes) / number of disks
RAID 1RAID 1
每一磁碟的 I/O = [讀取數 + (2 × 寫入數)] / 2I/Os per disk = [reads + (2 × writes)] / 2
RAID 5RAID 5
每一磁碟的 I/O = [讀取數 + (4 × 寫入數)] / 磁碟數目I/Os per disk = [reads + (4 × writes)] / number of disks
RAID 10RAID 10
每一磁碟的 I/O = [讀取數 + (2 × 寫入數)] / 磁碟數目I/Os per disk = [reads + (2 × writes)] / number of disks

舉例來說,若您的 RAID 1 系統具有兩個實體磁碟,而您計數器的值如下表中所示。For example, if you have a RAID 1 system that has two physical disks, and your counters are at the values that are shown in the following table.

計數器Counter Value
平均磁碟秒數/讀取Avg. Disk sec/Read
808.0
邏輯磁碟:平均磁碟秒數/寫入Logical Disk: Avg. Disk sec/Write
7070 KB
平均磁碟佇列長度Avg. Disk Queue Length
55
  • I/O value per disk 可計算如下:(80 + (2 × 70))/2 = 110The I/O value per disk can be calculated as follows: (80 + (2 × 70))/2 = 110

  • disk queue length 可計算如下:5/2 = 2.5The disk queue length can be calculated as follows: 5/2 = 2.5

  • 在此情況下,您的 Borderline I/O 發生瓶頸。In this situation, you have a borderline I/O bottleneck.

其他監視工具Other monitoring tools

您也可以使用 SQL Server 2008 中的 sys.dm_io_virtual_file_stats 動態管理檢視,來監視磁碟延遲及分析趨勢。如需詳細資訊,請參閱 <sys.dm_io_virtual_file_stats (Transact-SQL)>。You can also monitor disk latency and analyze trends by using the sys.dm_io_virtual_file_stats dynamic management view in SQL Server 2008. For more information, see sys.dm_io_virtual_file_stats (Transact-SQL).

SQL Server 2012 for SharePoint Server 2013SQL Server 2012 for SharePoint Server 2013

感謝 Microsoft 資深產品行銷經理 Bill Baer 與 MicroTechPoint 的 CEO 兼創辦人 Brian Alderman 提供一系列的線上 SQL Server 2012 訓練單元。同時要特別感謝 Channel 9 Microsoft 收錄了這些線上訓練單元。下列訓練單元將提供關於 SQL Server 2012 資料庫設定的詳細資料,以協助您了解如何改善 SharePoint Server 2016 效能、可用性與安全性。Thanks to Bill Baer, Microsoft Senior Product Marketing Manager and Brian Alderman, CEO and Founder of MicroTechPoint for providing a series of online SQL Server 2012 training modules. Special thanks to Channel 9 Microsoft for hosting these online training modules. The following training modules provide details about SQL Server 2012 database settings to help you learn how to improve SharePoint Server 2016 performance, availability, and security.

另請參閱See also

概念Concepts

SharePoint Server 2016 環境中的 SQL Server 概觀Overview of SQL Server in a SharePoint Server 2016 environment

最佳化 SharePoint Server 2013 的效能Optimize performance for SharePoint Server 2013

針對 SharePoint 伺服器陣列 SQL Server 的最佳作法Best practices for SQL Server in a SharePoint Server farm

SharePoint Server 2013 的效能規劃Performance planning in SharePoint Server 2013

SharePoint Server 2013 的容量管理及調整大小Capacity management and sizing for SharePoint Server 2013

SharePoint Server 2013 的容量規劃Capacity planning for SharePoint Server 2013

其他資源Other Resources

SharePoint Server 2013 環境中的 SQL Server 概觀Overview of SQL Server in a SharePoint Server 2013 environment