MS-SQL Configurations

Purpose

The default setting for min server memory is 0, and the default setting for max server memory is 2,147,483,647 MB. The minimum memory amount allowable for max server memory is 128 MB.

By default, SQL Server can change its memory requirements dynamically based on available system resources.

Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. However, setting this value too low could cause significant memory pressure and performance problems. Setting max server memory to the minimum value can even prevent SQL Server from starting

It becomes then crutial being able to monitor some server configurations, like, as illustrated, memory settings.

<p>Monitored configurations include :<p> <p>&#x25CF; Max server memory in MB</p> <p>&#x25CF; Min server memory in MB</p>

Report Configurations

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 configuration counter you want to monitor and the selected configuration threshold

Configuration feature : Select the specific SQL server configuration you want to monitor

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 configuration counter value has been monitored being lower or higher (depending on the selected configuration) 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.
Configuration feature	Select the SQL server configuration you want to monitor
Threshold	The threshold for the selected configuration 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 configuration.
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	Configuration feature	Threshold	Auto clear	Alarm tag	Alarm	Metric	Report
true	Max server memory in MB	G2W:50000 W2C:100000	true	true	true	true

Effect : For this rule, SQL server - Max server memory in MB configuration counter will be monitored

A WARNING alarm is sent if Max server memory in MB is 50000 MB or more, a CRITICAL alarm if 100000 MB or more.

Generated metrics

metricId	metricUnit	metricTarget	metricDescription
MSSQL_DATABASE_CONFIG_MAX_SERVER_MEMORY_MB	MB	[SQL Server configuration name]	fetched value of configuration
MSSQL_DATABASE_CONFIG_MIN_SERVER_MEMORY_MB	MB	[SQL Server configuration name]	fetched value of configuration