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
GO
reconfigure
GO

How to change the sql server configured value

Syntax: sp_configure ‘<<Configuration Name>>’,'<<Configuration Value>>’

sp_configure 'max server memory', 12288
RECONFIGURE
GO

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?