Workspace ONE UEM provides a database of performance recommendations based on scalability tests performed by the Workspace ONE UEM team.
| Recommendation | Description |
|---|---|
| TempDB Configuration | The number of tempDB files must match the number of CPU cores when the core is less than or equal to 8 cores. Beyond 8 cores, the number of files must be the closest multiple of 4 that is less than or equal to the number of cores (e.g. 10 cores need 8 tempDBs, 12 cores need 12 tempDBs, 13 cores need 12 tempDBs, 16 cores need 16 tempDBs.) File size, growth rate, and the location must be the same for all tempDB files. |
| Memory Allocation | 80% of the server memory should be allocated to SQL. Minimum should be set to 40% and maximum to 80%. The remaining 20% must be freed up to run the OS. |
| Cost Threshold for Parallelism and Maximum Degree of Parallelism |
Cost Threshold for Parallelism is the cost needed for a query to be qualified to use more than a single CPU thread. Maximum Degree of Parallelism is the maximum number of threads that can be used per query. The following are recommended values for these parameters:
|
| Trace Flag | The following trace flags must be set to 1 at Global. 1117 (https://msdn.microsoft.com/en-us/library/ms188396.aspx) - Not applicable to SQL 2016 and greater 1118 (https://msdn.microsoft.com/en-us/library/ms188396.aspx) - Not applicable to SQL 2016 and greater 1236 (https://support.microsoft.com/en-us/kb/2926217) - Not applicable to SQL 2016 and greater 8048 (https://blogs.msdn.microsoft.com/psssql/2015/03/02/running-sql-server-on-machines-with-more-than-8-cpus-per-numa-node-may-need-trace-flag-8048/) T174 (https://support.microsoft.com/en-us/help/3026083/fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache) T834 (https://support.microsoft.com/en-us/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloads) T3427 (https://support.microsoft.com/en-us/help/3216543/workloads-that-utilize-many-frequent-short-transactions-in-sql-server) - Applicable only to Cumulative Update 2 for SQL Server 2016 SP1 and SP2 |
| Trace Flag - SQL Server 2016 | See https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceontrace- flags-transact-sql/view=sql-server-2017 |
| Hyperthreading | To ensure best performance, hyperthreading must be deactivated on the database f the database is running on a physical server. If it is on a VM, then having hypertherading enabled on the ESX host doesn't have any performance impact, but hyperthreading must be deactivated on the Windows host level. |
| Optimize for Ad hoc Workloads | Enable Optimize for Ad hoc Workloads under SQL server properties. This is recommended to free memory from the server. Refer to the following article for more information: https://msdn.microsoft.com/en-us/library/cc645587(v=sql.120).aspx. |
| Lock Escalation | Deactivate Lock Escalation for “interrogator.scheduler” table by running the “alter table interrogator.scheduler set (lock_escalation = {Disable})” command. Deactivate lock escalation because the scheduler table has a high rate of updates/inserts. There is a high contention on this table with the use of FCM, and deactivating lock escalation helps improve performance. However, the drawback is that more memory is consumed. Refer to the following article for more information: https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx. |
| Autogrowth | For Production and Temp DBs, set Autogrowth to 128MB and max size to Unlimited. |
| Maximum Worker Threads | Increase maximum worker threads to 7500 |
| Modify Delayed Durability | Set this to Forced - This will reduce WRITELOG waits |
| ESXI Recommendations | Enable received side scaling on the network adapter of the SQL server Set power management to High Performance Change CPU.Quantum on host from 200 to 16 |
For device deployments above 150,000 devices, ensure that the Database is partitioned. You can run the installer from an elevated command prompt with the following flag: Name_Of_Database_installer.exe /V"AWINSTALLPARTITIONEDDATABASE=1".
For example: AirWatch_DB_9.1_GA_Setup.exe /V"AWINSTALLPARTITIONEDDATABASE=1".
For latency information, see Cross-Datacenter Latency.
Important:
This command requires SQL Enterprise. If you are running this command on a Workspace ONE UEM Database, you must run the installer with the flag for each upgrade from then on. If you do not, an error displays.
Was this page helpful?