SQL Server Monitoring - Monitor status

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a status of the execution of FHSQLMonitor tool.

Report

The report has a line and stacked column chart that per day shows how the services has executed, and 2 tables where the upper shows all executions regardless of type, and the lower only shows executions logged with type as non-debug and -info.

Processing details – drill-through page

The Processing details page has a line and column chart that per day shows how the selected service has executed, and a table listing the individual executions and their duration.

Sources

The FHSQLMonitor internal table dbo.fhsmLog and dbo.fhsmProcessing.

Model

The model for the report uses the facts [Log], [LogNonDebugInfo] and [Processing], and the dimensions [Date], [Time] and [Task name version].

Service setup

The number of rows shown in the Monitor status reports can be configured using the stored procedure fhsmSPControl.

Configuration

The default range for rows returned by fact [Log] is 10.000 rows and rows up to 1 day old.

The default range for rows returned by fact [LogNonDebugInfo] is 1.000 rows and rows up to 7 days old.

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'list',
	@Key = 'View.Log.';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'list',
	@Key = 'View.LogNonDebugInfo.';

Set range of rows returned to custom values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.Log.Days',
	@Value = '2';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.Log.Rows',
	@Value = '20000';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogNonDebugInfo.Days',
	@Value = '14';

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogNonDebugInfo.Rows',
	@Value = '2000';


Reset range of rows returned to default values:
EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.Log.Days',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.Log.Rows',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogNonDebugInfo.Days',
	@Value = NULL;

EXEC fhsmSPControl
	@Type = 'Configuration',
	@Command = 'set',
	@Key = 'View.LogNonDebugInfo.Rows',
	@Value = NULL;

SQL Server Monitoring – Monitor status