After a long period of time i am again starting blogging and try to keep it continue.I like to start a series of blog post about system store procedures used in SQL Server.To kick this series out i am picking up sp_configure system procedure.

Use of sp_configure 

In simple words sp_configure is a process to change or display global settings of current SQL Server.You can see the global settings and change them with the help of this procedure.

There are many settings of SQL Server which we can see and change. I will provide a details of settings and their uses in detail in later posts.First we see the columns returned by this procedure and their meanings.

When you run sp_configure you will get following columns:

Column        Description
========      ======================================
Name          Name of the configuration option. 
Minimum       Minimum allowed value for the particular 
	      configuration. 
Maximum       Maximum allowed value for the particular
	      configuration. 
config_value  Value which we set by using sp_configure. 
run_value     Value which on which the particular configuration 
	      is currently running(we will see it in detail also).

How to run sp_configure

Ok so there are many settings on SQL Server which you can change from this procedure so here is the syntax for change the setting.

USE master;
GO
EXECUTE sp_configure 'option name','value';

After running that you will get a message like

“Configuration option ‘option name’ changed from ‘old value’ to ‘new value’. Run the RECONFIGURE statement to install.”

So as message states we have to run reconfigure statement like this

USE master;
GO
RECONFIGURE;

So in this way you can change your global settings(server level settings) from sp_configure.

Now lets look in to the details of config_value and run_value. Config_value shows the value which is set by or configured by sp_configure and run_value shows the value on which the configuration is currently running. For making it further clear we look into sys.configurations system view.

System View sys.configurations:

sys.configuration is a system view which hold same info as sp_run_configure. SYS.Configurations view have following columns:

Column                  Description
================        =====================================
configuration_id        Unique ID for the configuration value.
name                    Name of the configuration option.
value                   Configured value for this option.
minimum                 Minimum allowed value for the configuration
			option.
maximum                 Maximum allowed value for the configuration
			option.
value_in_use            Running value currently in effect for this
			option.
description             Description of the configuration option.
is_dynamic              Decides whether there is need to restart
			services or not.
is_advanced             1 = The variable is displayed only when the
			show advanced option is set.

So here is_dynamic column decides whether there is need to restart SQL Server services or not. As if value of is_dynamic is 1 then you just have to run reconfigure statement and you are done there is no need to restart your SQL Server services for this but if your is_dynamic column value is 0 this means "NOT DYNAMIC" and you have to restart your services and after that your settings will take effect.

When you change any configuration setting so before running "reconfigure" or before starting SQL Server Services you can see the values of "config_value" and "run_value" is different so you can differentiate these two columns as "run_value" is showing the currently running configuration value and "config_value" is showing the value you configured by sp_configure and after restarting SQL Server Services or running "reconfigure" both values will be again same.

In later posts we will see all the configurations value one by one there uses and effects and many other uses of sp_configure.