SQL Server Advanced Options

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

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.

AWE