APMIA SQL Server Database extension not working consistently

Products

• DX SaaS

Issue/Introduction

APMIA agent is reporting base metrics, but agent is not reporting SQL Server Database extension metrics. Restarting agent did not help. Also letting the agent run for many hours did not help. agent continue to show the issue of not reporting SQL Server Database extension metrics in APM UI. 

Cause

APMIA is monitoring two databases. One of the database is primary db and the other one is backup db. For every 15 mins there is a backup and restore job running from primary to secondary. Due to this the db restore/backup table has thousands of records and causing the sqlserver extension to overload by not progressing to PushMetricToEM task. The populate metrics job was running for more than 12 hours.

Resolution

Implemented the following workaround which resolved metrics not reporting issue.

The queries in the following section (<apmia-home>\extensions\mssql-********\config\schema.json and schemaExt.json)  are not able to complete.

 

 1-

         "query":"select DATEDIFF(minute,MAX(rsh.restore_date),getDate()) as minutes_elapsed,rsh.destination_database_name as dbname,DATEDIFF(minute, (select bus.backup_finish_date from msdb.dbo.backupset bus where bus.backup_set_id = rsh.backup_set_id) ,MAX(rsh.restore_date)) as restore_latency  from msdb.dbo.restorehistory rsh group by rsh.destination_database_name,rsh.backup_set_id",

 

2-

         "query":"select DATEDIFF(minute,MAX(rsh.restore_date),getDate()) as minutes_elapsed,rsh.destination_database_name as dbname  from msdb.dbo.restorehistory rsh where rsh.restore_type = 'L' group by rsh.destination_database_name,rsh.backup_set_id",

 

3-

         "query":"select rsh.destination_database_name as dbname, DATEDIFF(minute, rsh.restore_date, getDate()) as last_restore ,  DATEDIFF(minute, (select bus.backup_finish_date from msdb.dbo.backupset bus where bus.backup_set_id = rsh.backup_set_id), rsh.restore_date) as last_restore_latency from msdb.dbo.restorehistory rsh where rsh.restore_date = (select max(rsh.restore_date) from msdb.dbo.restorehistory rsh)",

       

Removed the above section from the json file and restarted the agent. Now able to see the MSSQL metrics for Agent.