MS-SQL Performances

• 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

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

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.