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. 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:

  • Cost Threshold of Parallelism: 50

  • Max Degree of Parallelism: 2 and reduce to 1 if there is high server utilization.

Trace Flag

The following trace flags must be set to 1 at Global.

1117 (https://msdn.microsoft.com/en-us/library/ms188396.aspx)

1118 (https://msdn.microsoft.com/en-us/library/ms188396.aspx)

1236 (https://support.microsoft.com/en-us/kb/2926217)

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/)

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

If the database is running on a physical server, hyperthreading must be disabled on the database to ensure best performance. If it is on a VM, then having hypertherading enabled on the ESX host doesn't have any performance impact, but hyperthreading must be disabled 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

Disable Lock Escalation for “interrogator.scheduler” table by running the “alter table interrogator.scheduler set (lock_escalation = {Disable})” command. This is recommended as the scheduler table has very high rate of updates/inserts. There is a high contention on this table with the use of FCM, and disabling 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.

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".

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.