SQL Server Monitoring – Performance Statistics

Updated May 1, 2022.

Purpose

To get a baseline of the server performance counter metrics.

Sources

This feature queries the DMV sys.dm_os_performance_counters which returns a row per performance counter that is in the master data table dbo.fhsmPerfmonCounters.

Reports

This feature has 3 report pages.

#1 shows information about page life expectancy and batch requests/sec.

#2 shows Always On traffic

#3 shows Resource Governor Statistics

#1 – Page life expectancy and batch requests/sec.

The report page Performance Statistics has one clustered column chart with lines showing information about page life expectancy, average as well as per NUMA node, and one line chart showing the average number of batch requests/sec.

The x-axis for the charts is at the day level.

When hovering over the charts tooltips are showing detailed insight about the Page Life Expectancy and batch requests/sec. through out a day.

Page Life Expectancy tooltip
Batch requests/sec. tooltip

#2 – Always On Traffic

The report page Always On Traffic has a clustered column chart with a line showing Always On traffic information about bytes sent to trans transport and replica as columns and bytes received from replica as the line.

The x-axis for the charts is at the day level.

When hovering over the chart a tooltip is showing detailed insight about the traffic through out a day.

Always On traffic tooltip

#3 – Resource Governor Statistics

The report page Resource Governor Statistics has 2 line charts showing statistics for resource pools and workgroups.

The x-axis for the charts is at the timestamp level.

Model

The model for the report uses the fact [Performance statistics] and the dimensions [Performance counter], [Date] and [Time].

Configuration

There is no configuration for this feature.

Scheduling

The default schedule for the feature is every 5 minutes (300 seconds).

Retention

The default retention for the table dbo.fhsmPerfmonStatistics is 60 days.