Optober – SQL sp_configure Options: Show Advanced Options

Option Name:  Show Advanced Options

Usage:  sp_configure ‘show advanced options’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  If you have just installed SQL Server and run sp_configure you are going to get 18 options.  If you enable(Set to 1) the ‘show advanced options’ option you get access to all 70 options.  What’s an advanced option?  To be honest the list looks pretty arbitrary to me.  Things like ‘backup compression default’ and ‘remote admin connection’ are fine as ‘simple’ options, but why would ‘clr enabled’ or ‘nested triggers be considered simple?  You can do some serious damage with just the simple options.

The only official word I can find on it describes the advanced options as ‘Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician’.  There’s a few advanced that I feel could probably be simple, and a few simple that could probably be advanced.  As we progress through the month I’ll talk about each of these and the damage(or not) they could do.

Leave a Comment

Your email address will not be published.