WS1 UEM DB Monitoring
Recommendations for monitoring on the WS1 UEM database
|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.
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:
- Navigate to the AirWatch Console.
- Log in using credentials.
- Navigate to Hub > Reports & Analytics > Reports > List View.
- Run a report.
- Log out.
Typically, a tool like Keynote or AlertSite would be used to generate and monitor synthetic transactions.