Table of Contents
MS-SQL Free List Stalls
Purpose
- To troubleshoot IO issues within SQL Server, it is necessary to determine if IO response times and throughput are within expected ranges and if they impact SQL Server performance.
- Being able to monitor those performance items become then crucial to rapid and efficiently react to troubleshoot.
Monitor features include:
- Monitoring of every SQL Server file type (Data - row -, Log …) and specific file name
- Monitors :
- IO Stall read (ms) / number of reads
- IO Stall write (ms) / number of writes
- Number of bytes read / number of reads
- Number of bytes written / number of writes
- Alert if one of the items supported by this monitor for a given file (of any type) has reached a given threshold
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 file type you want to monitor, specific file name(s) using regular expressions, a the I/O stalls , write or read performance for the file threshold
File type
Select the specific SQL server file type you want to monitor , select “Any” to monitor IO stalls for any kind of file.
File name:
You can set a file specific rule by setting its name in the File name filter field. Use * to match any file name, or use regular expressions.
Monitored item:
Select the the I/O stalls , write or read performance you want to monitor
Threshold: **
Use the multi-threshold syntax to set multiple threshold/severity associations: G2W:10 W2M:60 M2C:90 (Green To Warning, Warning To Major, etc…), expressed in MB or percentage An alarm of the corresponding severity will be sent if monitored item is **over or under threshold, depending on the item that is being monitored. Set 0 in the field if unused
Surveillance table
| Parameter | Description |
|---|---|
| Active | Use this field to activate or deactivate a line of configuration. |
| File type | Select the specific SQL Server type of file you want to monitor Select “Any” so to monitor every type of file |
| File name | A filter to match only a specific SQL Server file name, you can use regular expression, white or black list |
| Monitored item | Select the the I/O stalls , write or read performance you want to monitor |
| Threshold | Represents the threshold for the monitored item You can use the multi threshold syntax (i.e. : G2W:10 W2C:40) . Values have to be expressed depending on what is monitored, could be in milliseconds, number of bytes … |
| 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 |
Generated metrics
| metricId | metricUnit | metricTarget | metricDescription |
|---|---|---|---|
| MSSQL_DATABASE_FREE_LIST_STALLS_IO_STALL_READ_MS | Millisecond | [File type] - [File name] | Calculated IO Stall read in milliseconds / number of reads for a given SQL server file |
| MSSQL_DATABASE_FREE_LIST_STALLS_IO_STALL_WRITE_MS | Millisecond | [File type] - [File name] | Calculated IO Stall write in milliseconds / number of writes for a given SQL server file |
| MSSQL_DATABASE_FREE_LIST_STALLS_NUM_OF_BYTES_READ | Byte | [File type] - [File name] | Calculated number of bytes read / number of reads for a given SQL server file |
| MSSQL_DATABASE_FREE_LIST_STALLS_NUM_OF_BYTES_WRITTEN | Byte | [File type] - [File name] | Calculated number of bytes written / number of writes for a given SQL server file |
