sp_configure

1)sp_configure is system stored procedure to set the server level configurations

  some of the values that can be set using this sp is :
     fill factor,  recovery interval  , enable /disable  xp_cmdshell etc..

2) server level configurations are stored in system catalog view called sys.configurations

3) sys.configurations  view has following columns :
    name : name of setting
    value : current value of setting
    minimum : minimum value it can accept
    maximum : maximum value it can accept
   value_in_use : value that is current set to
   description
   is_dynamic :   is dynamic flag tells whether the configuration change is applied immediately or it needs to stop & restart the server
   is_advanced : some of the options are advanced , they can only be visible  when  the show advanced options name is set to true.

4) options can be set by command  sp_configure   'optionname',  'optionvalue'

5) default  sp_configure command will return the following columns :
     name, min value, max value,   config_value ,  run_value

6) sp_configure command with out options can be executed by any role

7)  sp_configure command with options can only be executed by sysadmin & serveradmin roles.

8)  when sp_configure command is set to a value , then config_value  will be updated , how ever it will not be a run value

9) to make it as a run value,  command  reconfigure has to applied

10)  reconfigure with override command can force the server not to check for the value to fall with in the range of minvalue & maxvalue.

  so extra care has to be taken while using the command as we can set the values that are not acceptable to server.