How to use SP_CONFIGURE in SQL Server
sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.
How to view configured values
One can use SYS.CONFIGURATIONS else sp_configure without parameters to view current values
How to enable listing of all parameters
Run “select * from SYS.CONFIGURATIONS where name like ‘show%advanced%’” to see if show advanced options is already enabled?
sp_configure will only list 16 parameters than 68 total for SQL2008R2 with “show advanced options “ disabled. Enable “show advanced options” to get listing of all parameters
sp_configure ‘show advanced options’, 1
How to change the sql server configured value
Syntax: sp_configure ‘<<Configuration Name>>’,’<<Configuration Value>>’
sp_configure 'max server memory', 12288
Difference between SYS.CONFIGURATIONS & sp_configure
Columns of SYS.CONFIGURATIONS
configuration_id – Internal ID of the configuration setting
name – Config value name
value – Config value
value_in_use – The twin of run_value above
description – Details of a parameter
is_dynamic - If a value is dynamic or not. 1 = Dynamic, just run reconfigure after changing and it changes “on the fly”. 0 = not dynamic – need to stop and start SQL Server service
is_advanced – Like the above, ever wonder if you have to change the show advanced option to display a value? Well you can find out here. It’s a flag, 1 is yes, 0 is no, like the is_dynamic flag
Running the procedure sp_configure without parameters gives a result set which contains the column run_value. The difference between Config_Value and run_value is that config_value is the value that the configration name is configured, run_value is the value that the instance is currently using.
Columns of SP_CONFIGURE
name – The name of the value to be changed
minimum – The minimum value setting that is allowed
maximum – The maximum value that is allowed
config_value – What value is currently configured?
run_value – What value is currently running?