-
Notifications
You must be signed in to change notification settings - Fork 936
/
site-size-performance-faq.yml
265 lines (191 loc) · 25.1 KB
/
site-size-performance-faq.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
### YamlMime:FAQ
metadata:
title: Site size and performance FAQ
titleSuffix: Configuration Manager
description: Answers to common Configuration Manager questions about site sizing and performance.
author: banreet
ms.author: banreetkaur
ms.service: configuration-manager
ms.topic: faq
ms.date: 02/15/2022
title: Configuration Manager site sizing and performance FAQ
summary: |
*Applies to: Configuration Manager (current branch)*
This document addresses frequently asked questions about Configuration Manager site sizing guidance and common performance issues.
sections:
- name: Machine and disk configuration FAQs and examples
questions:
- question: |
How should I format the disks on my site server and SQL Server?
answer: |
Separate the Configuration Manager inboxes and SQL Server files on at least two different volumes. This separation lets you optimize cluster allocation sizes for the different kinds of I/O they perform.
For the volume hosting your sites server inboxes, use NTFS with 4K or 8K allocation units. ReFS writes 64k even for small files. Configuration Manager has many small files, so ReFS can produce unnecessary disk overhead.
For disks containing SQL Server database files, use either NTFS or ReFS formatting, with 64K allocation units.
- question: |
How and where should I lay out my SQL Server database files?
answer: |
Modern arrays of solid-state drives (SSD) and Azure Premium Storage can provide high IOPS on a single volume, with few disks. You typically add more drives to an array for additional storage, not additional throughput. If you're using physical spindle-based disks, you may need more IOPS than you can generate on a single volume. You should allocate 60% of the total recommended IOPS and disk space for the *.mdf* file, 20% for the *.ldf* file, and 20% for the log and data temp files. The *.ldf* and temp files can all reside on a single volume with 40% (20% + 20%) of your allocated IOPS.
SQL Server versions earlier than SQL Server 2016 created by default only one temp data file. You should create more, to avoid SQL Server locks and waiting for access to a single file. Community opinions vary on the best number of temp data files to create, from four to eight. Testing reveals little difference between four to eight, so you can create four *equally sized* temp data files. Your tempdb data files should be up to 20-25% the size of your full database.
- question: |
Are there any other recommendations for disk setup?
answer: |
When configurable, set RAID controller memory to 70% allocation for write operations and 30% for read operations. In general, use a RAID 10 array configuration for the site database. RAID 1 is also acceptable for small-scale sites with low I/O requirements, or if you use fast SSDs. With larger disk arrays, configure spare disks to automatically replace failing disks.
**Example: Physical machine with physical disks**
[Sizing guidelines](../plan-design/configs/site-size-performance-guidelines.md#general-sizing-guidelines) for a colocated site server and SQL Server with **100,000** clients are 1200 IOPS for site server inboxes and 5000 IOPS for SQL Server files.
Your resulting disk configuration might look like:
| Drives<sup>1</sup> | RAID | Format | Volume contents | Minimum IOPS needed| Approx. IOPS supplied<sup>2</sup> |
|----------------|-----------|-------------|----------------------|---------------------|------------------|
| 2x10k | 1 | - | Windows | | - |
| 6x15k | 10 | NTFS 8k | ConfigMgr inboxes | 1700 | 1751 |
| 12x15k | 10 | 64k ReFS | SQL .mdf | 60%*5000 = 3000 | 3476 |
| 8x15k | 10 | 64k ReFS | SQL .ldf, temp files | 40%*5000 = 2000 | 2322 |
1. Doesn't include recommended spare disks.
2. This value is from [Example disk configurations](../plan-design/configs/site-size-performance-guidelines.md#example-disk-configurations).
- question: |
I use Hyper-V on Windows Server. How should I configure the disks for my Configuration Manager VMs for best performance?
answer: |
Hyper-V delivers similar performance to a physical server, if hardware resources (CPU cores and pass-through storage) are 100% dedicated to the virtual machine (VM). Using fixed-size *.vhd* or *.vhdx* disk files causes a minimal 1-5% I/O performance impact. Using dynamically expanding *.vhd* or *.vhdx* disk files causes up to 25% I/O performance impact for the Configuration Manager workload. If you need dynamically expanding disks, compensate by adding an additional 25% IOPS performance to the array.
When running your Configuration Manager site server or SQL Server inside a VM, isolate the Hyper-V host OS drives from the VM OS and data drives.
For more information about optimizing VMs, see [Performance Tuning Hyper-V Servers](/windows-server/administration/performance-tuning/role/hyper-v-server/).
**Example: Hyper-V VM-based site server**
[Sizing guidelines](../plan-design/configs/site-size-performance-guidelines.md#general-sizing-guidelines) for a colocated site server and SQL Server with **150,000** clients are 1800 IOPS for site server inboxes and 7400 IOPS for SQL Server files.
Your resulting disk configuration might look like:
| Drives<sup>1</sup> | RAID | Format<sup>2</sup> | Volume contents | Minimum IOPS needed| Approx. IOPS supplied<sup>3</sup> |
|----------------|-----------|----------------|---------------------------|----------------------|------------------|
| 2x10k | 1 | - | Hyper-V host OS | - | - |
| 2x10k | 1 | - | (VM) site server OS | - | - |
| 2xSSD SAS | 1 | NTFS 8k | (VM) ConfigMgr inboxes | 1800 | 7539 |
| 4xSSD SAS | 10 | 64k ReFS | (VM) Host SQL Server (all files) | 7400 | 14346 |
1. Doesn't include recommended spare disks.
2. Fixed-size, pass-through *.vhdx* for the VM drive dedicated to the underlying volume.
3. This value is from [Example disk configurations](../plan-design/configs/site-size-performance-guidelines.md#example-disk-configurations).
- question: |
Are there any suggestions for Configuration Manager environments in Microsoft Azure?
answer: |
Start by reading the [Configuration Manager on Azure frequently asked questions](configuration-manager-on-azure.yml).
Azure infrastructure as a service (IaaS) VMs that leverage Premium Storage-based disks can have high IOPS. On these VMs, configure additional disks for anticipated disk space needs, rather than for additional IOPS.
Azure storage is inherently redundant and doesn't require multiple disks for availability. You can stripe disks in Disk Manager or Storage Spaces to provide additional space and performance.
For more information and recommendations on how to maximize Premium Storage performance and run SQL Servers in Azure IaaS VMs, see:
- [Optimize application performance](/azure/storage/storage-premium-storage-performance#optimize-application-performance)
- [Disks guidance](/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist#storage)
**Example: Azure-based site server**
[Sizing guidelines](../plan-design/configs/site-size-performance-guidelines.md#general-sizing-guidelines) for a colocated site server and SQL Server with **50,000** clients are eight cores, 32 GB, and 1200 IOPS for site server inboxes, and 2800 IOPS for SQL Server files.
Your resulting Azure machine might be a DS13v2 (eight cores, 56 GB) with the following disk configuration:
| Drives | Format | Contains | Minimum IOPS needed| Approx. IOPS supplied<sup>1</sup> |
|------------------|---------------|--------------------|----------------------|------------------|
| <standard> | - | Site server OS | - | - |
| 1xP20 (512 GB) | NTFS 8k | ConfigMgr inboxes | 1200 | 2334 |
| 1xP30 (1024 GB) | 64k ReFS | SQL Server (all files<sup>2</sup>) | 2800 | 3112 |
1. This value is from [Example disk configurations](../plan-design/configs/site-size-performance-guidelines.md#example-disk-configurations).
2. [Azure guidance](/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist#storage) allows for placing the TempDB on the local, SSD-based *D:* drive, given it won't exceed available space and allows for additional disk I/O distribution.
**Example: Azure-based site server (for instant performance increase)**
Azure disk throughput is limited by the size of the VM. The configuration in the preceding Azure example may limit future expansion or additional performance. If you add additional disks during initial deployment of your Azure VM, you can upsize your Azure VM for increased processing power in the future, with minimal upfront investment. It's much simpler to plan ahead to increase site performance as requirements change, instead of later needing to do a more complicated migration.
Change the disks in the preceding Azure example to see how the IOPS change.
**DS13v2**
| Drives<sup>1</sup> | Format | Contains | Minimum IOPS needed| Approx. IOPS supplied<sup>2</sup> |
|------------------|---------------|--------------------|----------------------|------------------|
| <standard> | - | Site server OS | - | - |
| 2xP20 (1024 GB) | NTFS 8k | ConfigMgr inboxes | 1200 | 3984 |
| 2xP30 (2048 GB) | 64k ReFS | SQL Server (all files<sup>3</sup>) | 2800 | 3984 |
1. Disks are striped using Storage Spaces.
2. This value is from [Example disk configurations](../plan-design/configs/site-size-performance-guidelines.md#example-disk-configurations). VM size limits performance.
3. [Azure guidance](/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist#storage) allows for placing the TempDB on the local, SSD-based *D:* drive, given it won't exceed available space and allows for additional disk I/O distribution.
If you need more performance in future, you can upsize your VM to a DS14v2, which will double CPU and memory. The additional disk bandwidth allowed by that VM size will also instantly boost the available disk IOPS on your previously configured disks.
**DS14v2**
| Drives<sup>1</sup> | RAID | Format | Contains | Minimum IOPS needed| Approx. IOPS supplied<sup>2</sup> |
|------------------|---------------|--------------------|----------------------|------------------|
| <standard> | - | Site server OS | - | - |
| 2xP20 (1024 GB) | NTFS 8k | ConfigMgr inboxes | 1200 | 4639 |
| 2xP30 (2048 GB) | 64k ReFS | SQL Server (all files<sup>3</sup>) | 2800 | 6182 |
1. Disks are striped using Storage Spaces.
2. This value is from [Example disk configurations](../plan-design/configs/site-size-performance-guidelines.md#example-disk-configurations). VM size limits performance.
3. [Azure guidance](/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist#storage) allows for placing the TempDB on the local, SSD-based *D:* drive, given it won't exceed available space and allows for additional disk I/O distribution.
- name: Other common SQL Server-related performance questions
questions:
- question: |
Is it better to run with SQL Server colocated with the site server, or run it on a remote server?
answer: |
Both can perform adequately, assuming the single server is appropriately sized, or network connectivity is sufficient between the two servers.
Remote SQL Server requires the upfront and operational cost of an additional server, but is typical among the majority of large-scale customers. Benefits of this configuration include:
- Increased site availability options, such as SQL Server Always On
- Ability to run heavy reporting with less overheard to site processing
- Simpler disaster recovery in some situations
- Easier security management
- Role separation for SQL Server management, such as with a separate DBA team
Colocated SQL Server requires a single server, and is typical for most small-scale customers. Benefits of this configuration include:
- Lower costs for machines, licenses, and maintenance
- Fewer points of failure in the site
- Better control for planning downtime
- question: |
How much RAM should I allocate for SQL?
answer: |
By default, SQL Server uses all available memory on your server, potentially starving the OS and other processes on the machine. To avoid potential performance issues, it's important to allocate memory to SQL Server explicitly. On site servers colocated with SQL Server, make sure the OS has enough RAM for file caching and other operations. Make sure there's enough RAM remaining for SMSExec and other Configuration Manager processes. When running SQL Server on a remote server, you can allocate the *majority* of the memory to SQL, but not all. Review the [sizing guidelines](../plan-design/configs/site-size-performance-guidelines.md#general-sizing-guidelines) for initial guidance.
SQL Server memory allocation should be rounded to whole GB. Also, as RAM increases to large amounts, you can let SQL Server have a higher percentage. For example, when 256 GB or more of RAM is available, you can configure SQL Server for up to 95%, as that still preserves plenty of memory for the OS. Monitoring the page file is a good way to ensure there is enough memory for the OS and any Configuration Manager processes.
- question: |
Cores are cheap these days. Should I just add a bunch of them to my SQL Server?
answer: |
You may run into memory contention issues if there are more than 16 physical cores and not enough RAM on your SQL Server. The Configuration Manager workload performs better when at least 3-4 GB of RAM per core is available for SQL. When adding cores to your SQL Server, be sure to increase RAM in proportional amounts.
- question: |
Will a SQL Server Always On availability group impact my performance?
answer: |
In general, availability groups have negligible effect on performance of the system when sufficient networking is available between the replica servers. You can have rapid database log *.ldf* file growth in a busy availability group environment. However, log file space is automatically released after a successful database backup. Add a SQL Server job for the Configuration Manager database to perform a backup, for example every 24 hours, and an *.ldf* backup every six hours. For more information about availability groups and Configuration Manager, including more about SQL Server backup strategies, see [Prepare to use a SQL Server Always On availability group](../servers/deploy/configure/sql-server-alwayson-for-a-highly-available-site-database.md).
- question: |
Should I enable SQL Server compression on my database?
answer: |
SQL Server compression isn't recommended for the Configuration Manager database. While there are no functional issues with enabling compression on a Configuration Manager database, test results don't show much size savings compared to the potential sizable performance impact to the system.
- question: |
Should I enable SQL Server encryption on my database?
answer: |
Any secrets in the Configuration Manager database are already stored securely, but adding SQL Server encryption can add yet another layer of security. There are no functional issues with enabling encryption on your database, but there can be up to a 25% performance degradation. Therefore, encrypt with caution, especially in large-scale environments. Also remember to update your backup and recovery plans to ensure you can successfully recover the encrypted data.
- question: |
What version of SQL Server should I run?
answer: |
For supported versions of SQL, see [Support for SQL Server versions](../plan-design/configs/support-for-sql-server-versions.md). From a performance standpoint, all supported versions of SQL Server meet required performance criteria. However, SQL Server 2016 or newer tends to outperform SQL Server 2014 in some aspects of the Configuration Manager workload. Also, running SQL Server 2014 at SQL Server 2012 compatibility level (110) improves performance in general. At installation time, Configuration Manager databases running on SQL Server 2014 are set to compatibility level 110. SQL Server 2016 or newer is set to that SQL Server version's default compatibility level, such as 130 for SQL Server 2016. Upgrading SQL Server in place doesn't update compatibility levels until you install the next major Configuration Manager current branch version.
If you see unusual timeouts or slowness on certain SQL queries on SQL Server 2016 or later, such as when using RBAC in the Admin Console, try changing the SQL Server compatibility level on the Configuration Manager database to 110. Running at SQL Server compatibility level 110 on SQL Server 2014 and newer versions of SQL Server is fully supported. For more information, see [SQL query times out or console slow on certain Configuration Manager database queries](https://support.microsoft.com/help/3196320/sql-query-times-out-or-console-slow-on-certain-configuration-manager-d).
As of January 2018, you should *avoid* the following SQL Server versions, because of various known performance-related or other potential issues:
- SQL Server 2012 SP3 CU1 to CU5
- SQL Server 2014 SP1 CU6 to SP2 CU2
- SQL Server 2016 RTM to CU3, SP1 CU3 to CU5
- question: |
Should I implement any additional SQL Server indexing tasks?
answer: |
Yes, update indexes as often as once a week and statistics as often as once a day to improve SQL Server performance. Third-party scripts and additional information available from the Configuration Manager and SQL Server communities can help optimize these tasks.
In large sites, some SQL Server tables, such as CI\_CurrentComplianceStatusDetails, HinvChangeLog, might be large, depending on your usage patterns. You may need to reduce or alter your maintenance approach for them one by one.
- question: |
When should I use full SQL Server instead of SQL Server Express on my secondary sites?
answer: |
SQL Server Express doesn't have any significant performance implications on secondary sites, and it's adequate for most customers. It's also easy to deploy and manage, and is the recommended configuration for nearly all customers at any size.
There's one situation where a full SQL Server installation might be needed. If you have a large number of distribution points and packages or sources in your environment, it's possible to exceed the 10-GB size limit of SQL Server Express. If the number of packages times the number of distribution points is more than 4,000,000, such as 2,000 DPs with 2,000 pieces of content, consider using full SQL Server at your secondary sites.
- question: |
Should I change MaxDOP settings on my database?
answer: |
Leaving your setting at 0 (use all available processors) is optimal for overall processing performance in most circumstances.
Many Configuration Manager administrators follow the guidance at [Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server](https://support.microsoft.com/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi). On most modern large hardware, this guidance leads to a suggested maximum setting of eight. However, if you run many smaller queries compared to your number of processors, it may help to set it to a higher number. Limiting yourself to eight isn't necessarily the best setting on larger sites when more cores are available.
On SQL Servers with greater than eight cores, start with a setting of 0, and only make changes if you experience performance issues or excessive locking. If you need to change MaxDOP because you are encountering performance issues at 0, start with a new value at least greater than or equal to the minimum recommended number of cores for that site's SQL Server sizing. Going lower than this value nearly always has negative performance implications. For example, a remote SQL Server for a 100,000 client site needs at least 12 cores. If your SQL Server has 16 cores, start testing your MaxDOP setting with a value of 12.
- name: Other common performance-related questions
questions:
- question: |
Which folders on the site server (or other roles) should I exclude for antivirus software?
answer: |
Take care when disabling antivirus protection on any system. In high volume and secure environments, we recommend disabling *active monitoring* for optimum performance.
For more information about recommended antivirus exclusions, see [Recommended antivirus exclusions for Configuration Manager 2012 and Current Branch Site Servers, Site Systems, and Clients](https://support.microsoft.com/help/327453/recommended-antivirus-exclusions-for-configuration-manager-2012-and-cu).
- question: |
What can I do to make WSUS perform better when it's used with Configuration Manager?
answer: |
Changing a few key IIS settings, such as WsusPool Queue Length and WsusPool Private Memory limit, can improve WSUS performance, even on smaller installations. For more information, see [Recommended hardware](../plan-design/configs/recommended-hardware.md).
Also make sure you have the latest updates installed for the operating system running WSUS:
- Windows Server 2012: Any non "Security only" cumulative update released October 2017 or later. ([KB4041690](https://support.microsoft.com/help/4041690/windows-server-2012-update-kb4041690))
- Windows Server 2012 R2: Any non "Security only" cumulative update released August 2017 or later. ([KB4039871](https://support.microsoft.com/help/4039871/windows-8-1-windows-server-2012-r2-update-kb4039871))
- Window Server 2016: any non "Security only" cumulative update released August 2017 or later. ([KB4039396](https://support.microsoft.com/help/4039396/windows-10-update-kb4039396))
- question: |
What type of maintenance should I run on my WSUS servers?
answer: |
See [The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance](https://support.microsoft.com/help/4490644/complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maint).
- question: |
I want to set up basic performance monitoring for my site. What should I watch?
answer: |
Traditional server performance monitoring works effectively for general Configuration Manager. You can also leverage the various System Center Operations Manager management packs for Configuration Manager, SQL Server, and Windows Server to monitor basic health of your servers. You can also directly monitor the Windows Performance Monitor (PerfMon) counters Configuration Manager provides. Monitor the backlogs in the various inboxes for early warning signs of potential site performance issues or backlogs.
additionalContent: |
## See also
- [Site sizing and performance guidelines](../plan-design/configs/site-size-performance-guidelines.md)
- [Configuration Manager on Azure frequently asked questions](configuration-manager-on-azure.yml)