Sys.Configuration
A while ago, I was asked by customer how to tell if an instance parameter is dynamic or rather static (which requires me to restart the instance service).
Parameters like fill factor (%) or max degree of parallelism. Does this parameter exist in the short list or do I need the show advance option?
So how do we know if the fill factor (%) require a service restart?
Well, like everything in SQL we have a SYS table sys.configuration; that will show us if the parameter is dynamic and if the parameter is an advance setting.
Running a query on the sys.configuration will show us the parameters:
1: Select * From Sys.configurations
We can spot the fill factor on this screenshot.
We can see that in order to modify this parameter, it will require a service restart and we can also see that the parameter is hidden, so we should enable the "Show advanced option"
In this table we have the description of the sys.configuration columns.
Column name |
Data type |
Description |
configuration_id |
int |
Unique ID for the configuration value. |
name |
nvarchar(35) |
Name of the configuration option. |
value |
sql_variant |
Configured value for this option. |
minimum |
sql_variant |
Minimum value for the configuration option. |
maximum |
sql_variant |
Maximum value for the configuration option. |
value_in_use |
sql_variant |
Running value currently in effect for this option. |
description |
nvarchar(255) |
Description of the configuration option. |
is_dynamic |
bit |
1 = The variable that takes effect when the RECONFIGURE statement is executed. |
is_advanced |
bit |
1 = The variable is displayed only when the show advancedoption is set. |
That is a very useful table and can be used every time that we are required to change the configuration of the instance