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