−Sidebar
products:cockpit:1.0:monitorsguide:mssql:ms-sqlperformances
Add this page to your book 
Remove this page from your book 
Manage book (
Help −Table of Contents
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 monitor connections, transactions, and locks.
<p>Monitored performances include :<p> <p>● Average latch wait time (ms)</p> <p>● Average wait time (ms)(Key, Object, Page)</p> <p>● Buffer cache hit ratio</p> <p>● Compilations per second</p> <p>● Deadlocks per second (Key, Object, Page)</p> <p>● Latch waits per second</p> <p>● Lazy writes per second</p> <p>● Logins per second</p> <p>● Lock requests per second (Key, Object, Page)</p> <p>● Lock timeouts per second (Key, Object, Page)</p> <p>● Lock waits per second (Key, Object, Page)</p> <p>● Lock wait time (ms) (Key, Object, Page)</p> <p>● Logouts per second</p> <p>● Longest transaction running time</p> <p>● Page life expectancy</p> <p>● Pages read per second</p> <p>● Recompilations per second</p> <p>● Table lock escalations per second</p> <p>● Total latch wait time (ms)</p> <p>● User connections</p> <p>● Worktables from cache ratio</p>
Report Configuration
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 |
/home/clients/8c48b436badcd3a0bdaaba8c59a54bf1/wiki-web/data/pages/products/cockpit/1.0/monitorsguide/mssql/ms-sqlperformances.txt · Last modified: 2024/05/01 18:52 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
