SQL Server configurations List

Was just checking sql server configuration of my sql installation via t-sql and found a way of listing which sql server configurations can be changed dynamically.

Here is the list of SQL Server configurations with their descriptions, that can be changed without needed a sql server restart.

name description
access check cache bucket count Default hash bucket count for the access check result security cache
access check cache quota Default quota for the access check result security cache
Ad Hoc Distributed Queries Enable or disable Ad Hoc Distributed Queries
affinity mask affinity mask
affinity64 mask affinity64 mask
Agent XPs Enable or disable Agent XPs
allow updates Allow updates to system tables
backup compression default Enable compression of backups by default
blocked process threshold (s) Blocked process reporting threshold
clr enabled CLR user code execution enabled in the server
cost threshold for parallelism cost threshold for parallelism
cross db ownership chaining Allow cross db ownership chaining
cursor threshold cursor threshold
Database Mail XPs Enable or disable Database Mail XPs
default full-text language default full-text language
default language default language
default trace enabled Enable or disable the default trace
disallow results from triggers Disallow returning results from triggers
filestream access level Sets the FILESTREAM access level
ft crawl bandwidth (max) Max number of full-text crawl buffers
ft crawl bandwidth (min) Number of reserved full-text crawl buffers
ft notify bandwidth (max) Max number of full-text notifications buffers
ft notify bandwidth (min) Number of reserved full-text notifications buffers
index create memory (KB) Memory for index create sorts (kBytes)
in-doubt xact resolution Recovery policy for DTC transactions with unknown outcome
max degree of parallelism maximum degree of parallelism
max full-text crawl range Maximum  crawl ranges allowed in full-text indexing
max server memory (MB) Maximum size of server memory (MB)
max text repl size (B) Maximum size of a text field in replication.
media retention Tape retention period in days
min memory per query (KB) minimum memory per query (kBytes)
min server memory (MB) Minimum size of server memory (MB)
nested triggers Allow triggers to be invoked within triggers
network packet size (B) Network packet size
Ole Automation Procedures Enable or disable Ole Automation Procedures
optimize for ad hoc workloads When this option is set, plan cache size is further reduced for single-use adhoc OLTP workload.
PH timeout (s) DB connection timeout for full-text protocol handler (s)
precompute rank Use precomputed rank for full-text query
query governor cost limit Maximum estimated cost allowed by query governor
query wait (s) maximum time to wait for query memory (s)
recovery interval (min) Maximum recovery interval in minutes
remote admin connections Dedicated Admin Connections are allowed from remote clients
remote login timeout (s) remote login timeout
remote proc trans Create DTC transaction for remote procedures
remote query timeout (s) remote query timeout
Replication XPs Enable or disable Replication XPs
server trigger recursion Allow recursion for server level triggers
show advanced options show advanced options
SMO and DMO XPs Enable or disable SMO and DMO XPs
SQL Mail XPs Enable or disable SQL Mail XPs
transform noise words Transform noise words for full-text query
two digit year cutoff two digit year cutoff
user options user options
xp_cmdshell Enable or disable command shell

Script to get this list of configurations is as below:

SELECT  name ,
FROM    sys.configurations
WHERE   is_dynamic = 1

This entry was posted in SQL Server, T-SQL Scripts, Tricks and tagged . Bookmark the permalink.

Leave a Reply