To check the available advanced options configured at instance level in SQL Server, you need to use the sp_configure system stored procedure.
The default behavior, for security and stability purposes, is to show only the "least" advanced parameters, so, if you execute the stored procedure, it will show something like this:
EXEC sp_configure
 |
| It doesn't show all the configuration options |
To display all of the available options, you have to run the "show advanced options" command:
EXEC sp_configure 'show advanced option', '1';
CAUTION: Be extremely careful enabling this command, and remember to disable right after using it, because any user have access to the sp_configure command, and you will expose advanced configuration parameters to an untrusted user.
After this, you have to execute a RECONFIGURE command for the changes to take effect.
Note: RECONFIGURE and RECONFIGURE WITH OVERRIDE have the same effects for displaying the parameters, the only difference is when you change any parameter, the OVERRIDE option allows you to set a value outside allowed boundaries (yes, you should never do it, but the engine give us the option of shooting ourselves in the foot).
To show all the advanced options we proceed to run the following command:
USE master;
GO
EXEC sp_configure 'show advanced option', '1'; --Enable advanced options
RECONFIGURE;
EXEC sp_configure --Show all the options
EXEC sp_configure 'show advanced option', '0'; --Always disable advanced options
RECONFIGURE;
 |
| Note that not all options are displayed on the image |
