A collection of some commonly-used options for sp_configure in MSSQL Server

sp_configure-options.sql

sp_configure 'show advanced options', 1
reconfigure;
-- show advanced options
-- backup compression default -- always compress backups.
-- blocked process threshold (s) -- only capture blocking scenarios that block this threshold.
-- clr enabled -- allows CLR user-defined stored procedures and functions.
-- Database Mail XPs
-- fill factor (%) -- useful for reducing fragmentation (for frequently updated tables).  Reduces page-splitting when a record has be inserted in the middle of the page
-- max degree of parallelism -- set the max number of CPUs that are available for a single query.  Can be overridden by MAX DOP setting on a query
-- remote access -- grant/revoke ability to allow remote users to access the SQL Server from external connections
-- remote admin connections -- leave one CPU free for remote administration connections.  Useful for busy machines; admin can still get into machine.
-- xp_cmdshell -- allows execution of command shell commands from within SQL server.  VERY RISKY - VERY INSECURE.