SQL Server performance counters

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and report on the presence and quantity of specific SQL Server performance counters, such as ‘Page lookups/sec’, ‘Checkpoint pages/sec’, and ‘Lazy writes/sec’.
• Why: This job is important to ensure that these critical performance counters are available and functioning correctly in SQL Server environments. The absence or malfunction of these counters can affect the ability to diagnose and track performance issues effectively.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

declare @counter_check  int
select @counter_check = count(*) from master..sysperfinfo where  counter_name in ( 'Page lookups/sec', 'Checkpoint pages/sec', 'Lazy writes/sec')   
if @counter_check != 3 
begin 
select @counter_check = count(*) from master..sysperfinfo 
select 'Missing SQL Server performance counters (number of counters: ' + cast(@counter_check as varchar(4))+').' 
end 
else 
begin 
select @counter_check = count(*) from master..sysperfinfo 
select cast(@counter_check as varchar(10))+' SQL Server performance counters checked. ' "Info" 
end

Dependencies

This monitoring job depends on the following objects:

• “master.dbo.sysperfinfo” – this is a key dependency table for pulling the necessary performance counter information.
• “dbw_performance_counters_proc” – this is the main procedure responsible for checking the availability and count of specified performance counters and updating the check values.

Job Configuration and Reporting

• The monitoring job checks the specified SQL Server performance counters hourly.
• It relies on a stored procedure to evaluate the existence and counts of the targets performance counters.
• On finding missing counters, the job uses the defined parameter to return a status value, which governs whether an alarm or warning should be raised.

Presentation of Job Results

The results of the monitoring are structured to provide information on:

• How many counters are verified and active.
• Whether there are missing counters, with details of how many and which ones.

Alarm Handling Configuration

Upon detecting discrepancies, the status value defined in the parameters (OK – 0, WARNING – 1, ALARM – 2) is applied based on the current state of the performance counters. This ensures timely notification and resolution strategies are actionable depending on the severity of the issue detected.

Procedural Implementation

The procedure involved in this monitoring job does the following:

• Checks the number of specific counters available.
• Sets the status and execution details based on the results from the check.
• Updates monitoring check values in dbWatch to reflect the new state and information.

Upgrade Path

The procedure `dbw_performance_counters_proc` has undergone updates in versions from 1.2 and 1.3 to an enhanced version provided in the detailed implementation code, reinforcing the reliability and details captured during each monitoring session.

Overall, this SQL Server performance counters monitoring job plays a crucial role in maintaining the health and performance observability of the SQL Server instances aligned with the best practices and operational requirements.