WS1 UEM DB Monitoring

Recommendations for monitoring on the WS1 UEM database

Monitor Descritpion
Data Files Monitor and alert for resizing when free space in data files drops below 10%.
Transaction Logs Monitor and resize if free space in log drops below 10%.
Waiting Tasks Waiting tasks in the SQL activity monitor must be under 10 on average. Ideally waiting tasks should be between 0 and 2 when compared to 20,000 batch requests per second.
Index Rebuild Monitor for fragmentation between 10% and 29%. Reorganize with an update of statistics. Indexes with fragmentation greater than 29% should be rebuilt.
SQL Server CPU Monitor sustained high CPU utilization (Over 90% for a 15 minute duration).
SQL Server Job History Monitor failed SQL Server Agent Jobs (in particular, AirWatch Jobs).
SQL Server Page Life Expectancy Monitor SQL Server Page Life Expectancy (dropping below 3000).
SQL Server Disk Space Monitor disk space usage on all Data and Log Drives for ‘AirWatch’ and ‘tempdb’ Databases.
SQL Server Disk Queuing Monitor Disk Queuing on all Data and Log Drives for ‘AirWatch’ and ‘tempdb’ Databases. Check Disk Queue Length via Task Manager > Performance > Resource Monitor > Dist Tab > Storage. It should average between 2 and 4. It could increase or decrease, but on average it should be between those values.
Page Life Expectancy Page Life Expectancy is an indication of whether the database server has memory pressure. The expected number is over 1,000 (seconds). If it is low, this is a first indicator of memory pressure. This may not be an issue if:
- The PLE is increasing over time. If it is increasing, but is still less than 1,000, then that is a sign of a memory pressure.
- After an index maintenance job, the PLE can be low. This needs to be monitored for a few hours to see if it goes up.
Index Fragmentation Level A high fragmentation level means data retrieval becomes less efficient and reduces database performance. Run the defragmentation job on a nightly basis. The script below shows the fragmentation level (in percent) against all the tables. The recommended fragmentation level is less than 30% when the page size is more than 1,000. 
SELECT OBJECT_NAME(object_id), index_id, index_type_desc, index_level,
avg_fragmentation_in_percent, avg_Page_space_used_in_percent, page_count
 FROM sys.dm_db_index_physical_stats(DB_ID(N'AirWatch'), null, null,
null, 'SAMPLED') ORDER BY avg_fragmentation_in_percent DESC

If the database is highly fragmented, it is recommended that you perform an index reorganize or rebuild.

Health Checks

Synthetic transactions are the strongest indicator of a healthy AirWatch environment. They can mimic end user actions (for example, enrollment) and report if there are issues. Many different use cases could be considered, and high-use scenarios should be tested with synthetic transactions. An example synthetic transaction could be:

  1. Navigate to the AirWatch Console.
  2. Log in using credentials.
  3. Navigate to Hub > Reports & Analytics > Reports > List View.
  4. Run a report.
  5. Log out.

Typically, a tool like Keynote or AlertSite would be used to generate and monitor synthetic transactions.