This tab provides a performance overview of SQL queries running on the instance you selected and over the time range you selected.

Active sessions

Active Sessions (Aggregate) is a time series, sliced by wait types (CPU, memory, network), of currently active database sessions.

This time series indicates the overall level of activity and concurrency on the database within a specific timeframe. If the number of active sessions is relatively low, the server is not heavily loaded in terms of concurrent queries. A dominance of the purple bar in the graph (CPU) suggests that when sessions are active, they are often consuming CPU rather than waiting on I/O or locks.

Query table

The query table displays the top queries sorted in descending order of total duration, along with metrics like Executions, Total Duration, Avg. Duration, Total CPU time, Avg. active sessions, and a visual representation of Wait states. This table is crucial for identifying specific queries that are influencing performance.

• Total Duration: The cumulative time spent by the database executing this query across all its runs in the selected time window. High total duration indicates a query that contributes significantly to the overall database workload time.
• Avg. Duration: The average time it takes for a single execution of the query. High average duration indicates a slow query.
• Total CPU time: The cumulative CPU time consumed by the query across all its runs. High CPU time indicates a CPU-intensive query.
• Avg. active sessions: The average number of sessions that were active while this specific query was running. This can help identify queries that cause blocking or high concurrency issues, although in this view, it seems low for most queries.
• Wait states: The visual bar shows the breakdown of time spent by the query in different states (CPU, waiting on I/O, network, locks, and so on). A long bar indicates a query taking a significant amount of time, and the color breakdown shows why it's taking time.

Query details

Select a specific query in the query table to navigate to a slide-out pane which provides details about that query:

• Query statement

• Explain plans

• Metrics

• Query samples

• Traces

Query statement

Query statement displays a normalized form of the query you selected.

A normalized query is the common pattern of a group of queries which differ only in their specific parameter values. By representing a group of queries by a single normalized query, Database Monitoring reduces the cardinality of unique queries, making it easier to track performance trends, identify slow or frequently executed query patterns, and prevent sensitive data from being exposed.

Explain plans

The Explain plans tab provides a detailed sequence of operations a database will perform to execute a query, outlining how data will be accessed (for example, full table scan, index scan), the order and type of JOIN operations for multiple tables, and how filtering, sorting, and aggregation will be processed. Additionally, it includes estimated costs for each step and the overall query, representing the anticipated resource usage, along with the estimated number of rows the database expects to process at each stage.

Metrics

The Metrics tab displays metrics aggregated for top queries but without the support for displaying top 10 queries.

Query samples

The Query samples tab displays samples collected when this query was running.

Traces

The Traces tab lists the traces that contain this query. Traces come from sampled queries. When you select a trace you navigate to the Splunk APM trace analyzer. This helps you to identify the sources of problematic queries.

Tip:

Actions you can take:

Select a trace to navigate to its application in Splunk APM.

Sample scenario

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.