MS-SQL Performances

Purpose

Most of the indicators that provide the information needed are operating system counters, especially those that deal with CPU activity, memory, paging, and the network interface. In SQL Server, you should also monitor connections, transactions, and locks.

This monitor allows to select the perfomance indicators which are important to monitor

Monitored performances include :

Average latch wait time (ms)

Average wait time (ms)(Key, Object, Page)

Buffer cache hit ratio

Compilations per second

Deadlocks per second (Key, Object, Page)

Latch waits per second

Lazy writes per second

Logins per second

Lock requests per second (Key, Object, Page)

Lock timeouts per second (Key, Object, Page)

Lock waits per second (Key, Object, Page)

Lock wait time (ms) (Key, Object, Page)

Logouts per second

Longest transaction running time

Page life expectancy

Pages read per second

Recompilations per second

Table lock escalations per second

Total latch wait time (ms)

User connections

Worktables from cache ratio

Configuration hints

Surveillance table:

This is a table of rules that you can use to configure and customize the configuration.

Each line of the table will define a rule of monitoring.

You can combine multiple rules to cover different cases.

Within a rule, you can configure the SQL Server performance counter you want to monitor and the selected performance threshold

Performance feature :
Select the specific SQL server performance you want to monitor (see below)

Threshold:

Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:40 W2M:30 M2C:5 (Green To Warning, Warning To Major, etc...), expressed in the unit of measure of the performance selected

An alarm of the corresponding severity will be sent if performance counter value has been monitored being lower or higher (depending on the selected performance) threshold set.

Set 0 in the field if unused

Surveillance table

Parameter	Description
Active	Use this field to activate or deactivate a line of configuration.
Performance feature	Select the SQL server performance you want to monitor
Threshold	The threshold for the selected performance You can use the multi threshold syntax (i.e. : G2W:40 W2C:5) . Notice that values expressed in this field will vary depending on the performance.
Auto clear	If checked, the alarm will be cleared as soon as the alarm condition is not met anymore.
Alarm tag	This field allows to add custom text within the alarm message. %MSG% variable will contain the actual generated message and can be used such as: “my_prefix %MSG% my_suffix”. By default, tag will be used as prefix.
Alarm	If checked, this line of surveillance will be used for alarm generation.
Metric	If checked, this line of surveillance will be used for metric generation.
Report	If checked, this line of surveillance will used for showing threshold and severity in the daily report

Examples

Active	Performance feature	Threshold	Auto clear	Alarm tag	Alarm	Metric	Report
true	Buffer cache hit ratio	G2W:95 W2C:80	true	true	true	true

Effect : For this rule, SQL server - Buffer cache hit ratio performance counter will be monitored

A WARNING alarm is sent if Buffer cache hit ratio is 95% or less, a CRITICAL alarm if 80% or less.

Generated metrics

metricId	metricUnit	metricTarget	metricDescription
MSSQL_DATABASE_PERF_AVG_LATCH_WAIT_TIME_MS	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_AVG_WAIT_TIME_MS_KEY	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_AVG_WAIT_TIME_MS_OBJECT	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_AVG_WAIT_TIME_MS_PAGE	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_BUFFER_CACHE_HIT_RATIO	Percent	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_COMPILATIONS_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_DEADLOCKS_PER_SEC_KEY	Second	[SQL Server performance name]	fetched value of performance
SSQL_DATABASE_PERF_DEADLOCKS_PER_SEC_OBJECT	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_DEADLOCKS_PER_SEC_PAGE	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LATCH_WAITS_PER_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LAZY_WRITES_PER_SEC	second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOGINS_PER_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_REQUESTS_PER_SEC_KEY	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_REQUESTS_PER_SEC_OBJECT	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_REQUESTS_PER_SEC_PAGE	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_TIMEOUTS_PER_SEC_KEY	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_TIMEOUTS_PER_SEC_OBJECT	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_TIMEOUTS_PER_SEC_PAGE	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAITS_PER_SEC_KEY	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAITS_PER_SEC_OBJECT	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAITS_PER_SEC_PAGE	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAIT_TIME_MS_KEY	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAIT_TIME_MS_OBJECT	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOCK_WAIT_TIME_MS_PAGE	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LOGOUTS_PER_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_LONGEST_TRANS_RUNNING_TIME	Milliseconds	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_PAGE_LIFE_EXPECTANCY	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_PAGES_READ_PER_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_RECOMPILATIONS_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_TABLE_LOCK_ESCALATIONS_PER_SEC	Second	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_TOTAL_LATCH_WAIT_TIME_MS	Millisecond	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_USER_CONNECTIONS	Number	[SQL Server performance name]	fetched value of performance
MSSQL_DATABASE_PERF_WORKTABLES_FROM_CACHE_RATIO	Percent	[SQL Server performance name]	fetched value of performance