====== 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. **Monitored configurations include :** * Max server memory in MB * Min server memory in MB ===== 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|