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