Part -II – Configurations – Management Studio and sp_configure
In my previous article you should have been familiar with the query sp_configure to see the default values of all the list
of 16 options. Let us see the Advanced options here.
By default the show advanced options will have 0 in its value. To see and set the advanced options this has to be changed
to 1 and it can be changed back to 0 once done. The query for changing the option is
sp_configure ‘show advanced options’, 1
This query will change the config_value option to 1 and reconfigure will send the command to reload all of the values. Once
the value is changed to 1 and by running sp_configure again will display an extended list of 68 in SQL Server 2008 and 70
in SQL Server 2008 R2.
I am going to take you through some of the most commonly used options.