APMIA MSSQL Extension Metrics Requirements
Article ID: 246275
Updated On:
Products
Issue/Introduction
One of our DB team is looking for few new metrics which are listed below in MSSQL DB extension.
- Top 5/10 SQL statements on each SQL instance/s in terms of CPU
- Top 5/10 SQL statements on each SQL instance/s in terms of query response time
- Index Fragmentation Status under “Index Statistics”
- Listener information (name. host, status, etc)
- Database/s included on an availability group
Let me know if these can be enabled through properties or we need enhancement.
Environment
Release : SAAS
Component : Integration with APM
Resolution
I had a long talk with Engineering and how this case has evolved
Issue #2 Original Ask
One of our DB team is looking for few new metrics which are listed below in MSSQL DB extension.
Top 5/10 SQL statements on each SQL instance/s in terms of CPU
Top 5/10 SQL statements on each SQL instance/s in terms of query response time
Let me know if these can be enabled through properties or we need enhancement.
Response; The MSSQL Extension gives blame point tracer metrics such as response time etc. Your best bet is an enhancement or use sql monitoring in Java
Your best bet:
For the slow queries monitoring, sql server monitoring extension provides some info in traces
https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/dx-apm-agents/apmtest/infrastructure-agent/MS-SQL-Database-Monitoring/SQL-Server-Database-Traces.html#concept.dita_a31767c8-5c89-40c6-adb3-62fcbd081ca6_SlowQuerieswithadetailedEXPLAINPLAN
I will submit an enhancement for these metrics with MSSQL Extension: Enhancement: row count, blocking/waits and more importantly CPU/time intensive queries.
That is sql server monitoring extension with traces instead of metrics
So even with the top 5/10 query info enhancement request
You may want to check out the slow query trace with explanation plan to see if it would help them as an alternative in the meantime
If you is ok to use the slow query traces in map view, then no enhancement is needed to show metrics to show the top 5/10 queries
"For every time range that you select, the DX Application Performance Management Infrastructure agent displays all snapshots of Top N Slow Queries that qualify within a specified time range as traces. "
If you use the SQL Server Agent and not getting metrics, that is a new case, referring to this case including Agent logs and config Files
Additional Information
The slow query trace with explanation plan would require additional configuration on the sql server side
"To view the EXPLAIN PLAN, you must have the required privileges to insert rows into an existing output table that you specify to hold the execution plan. Also, you must have the required privileges to execute the SQL statement for which you are determining the execution plan. For more information, see SQL Server Documentation."
Feedback
