Demo scenario

This scenario illustrates how to interpret the data you see in Database Monitoring.

Screenshot of the Queries tab.

In the screenshot, the instance is mssql-server-st. This screenshot illustrates a scenario in which there are:

  • High Total CPU time or Total duration for specific queries: The Person.Person JOIN Person.BusinessEntity query shows a significant cumulative total duration and CPU time of 1 hour and 3 minutes. The WITH PerfCounters query, likely a monitoring component, also consumes 5 minutes and 45 seconds of total CPU time.
  • CPU-bound queries: The Total CPU time and Total duration columns are identical for the top three queries. This suggests these queries are heavily CPU-bound with minimal wait times, or there is a display anomaly. If accurate, they are highly CPU-intensive.
  • Discrepancy in active sessions or wait states: For the WITH PerfCounters and Person.Address queries, the Avg. active sessions column displays 0.00, yet wait state bars are visible. This inconsistency requires further investigation to understand the true nature of resource utilization.
Tip:

Actions you can take for this scenario:

  • Optimize high-impact queries: Immediately investigate the SELECT * FROM Person.Person JOIN Person.BusinessEntity query. Analyze its execution plan to identify bottlenecks, such as missing indexes or inefficient joins, and implement optimizations to enhance its performance.
  • Validate metrics: Verify the calculation methods for Total CPU time and Total duration within the monitoring tool, especially given their identical values for multiple queries. Additionally, clarify the discrepancy between the Avg. active sessions count and the presence of Wait states bars for accurate interpretation.
  • Investigate the Person.Address query: Analyze and optimize the SELECT * FROM Person.Address query, as it also exhibits significant cumulative CPU usage that could impact overall database performance.