SQL Server Monitoring - Connections

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

Purpose

To show the number of connections over time.

Report

The report has a line chart in the upper half showing the number of connections over time, and a table in the lower half showing the number of connections per database and with the possibility to drill down to program and host level.

Sources

This feature queries the DMV sys.dm_exec_sessions which returns information for all active connections.

Model

The model for the report uses the fact [Connections] and the dimensions [Date], [Time] and [Connection info].

Service setup

The feature uses the service ‘fhsmSPConnections’ which can be configured using the stored procedure fhsmSPControl.

Parameter

There is no parameter to be configured for this service.

Schedule

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

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPConnections',
	@Name = 'Connections',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPConnections',
	@Name = 'Connections',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 300,
	@FromTime = '00:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmConnections is 30 days.

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmConnections ',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmConnections ',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 30;

SQL Server Monitoring – Connections