Use the following AirWatch database performance recommendations, which are based on scalability tests performed by AirWatch.

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 will need 8 tempDBs, 12 cores will need 12 tempDBs, 13 cores will need 12 tempDBs, 16 cores will need 16 tempDBs.) File size, growth rate, and the location need to be the same for all tempDB files.

Memory Allocation

Eighty percent 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 in case of 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/)

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 will not 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 in order 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 GCM, 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.

For device deployments above 300,000 devices , ensure that the Database is partitioned. To enable this please 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 an AirWatch Database, you must run the installer with the flag for each upgrade from then on. If you do not, an error displays.