SQL Server configuration option displayed and changed using SP_Configure system stored procedure. Using this system stored procedure you can view server setting or you can make configuration changes quickly using SSMS. Even you change the couple of configuration changes using GUI but not all.
sp_configure
Some configuration options are designated as advanced options. By default, these options are not available for viewing and changing. To make them available, set the "ShowAdvancedOptions" configuration option to 1.
sp_configure 'advanced options', 1
To see the advance option use below statements
reconfigure
exec sp_configure
RECONFIGURE
After changing SQL Configuration value using system stored procedure sp_configure RECONFIGURE used to update the currently configured value for a changed configured value
Below are the list of configuration options
name
|
minimum
|
maximum
|
access check cache bucket count
|
0
|
65536
|
access check cache quota
|
0
|
2147483647
|
Ad Hoc Distributed Queries
|
0
|
1
|
affinity I/O mask
|
-2147483648
|
2147483647
|
affinity mask
|
-2147483648
|
2147483647
|
affinity64 I/O mask
|
-2147483648
|
2147483647
|
affinity64 mask
|
-2147483648
|
2147483647
|
Agent XPs
|
0
|
1
|
allow updates
|
0
|
1
|
backup compression default
|
0
|
1
|
blocked process threshold (s)
|
0
|
86400
|
c2 audit mode
|
0
|
1
|
clr enabled
|
0
|
1
|
common criteria compliance enabled
|
0
|
1
|
contained database authentication
|
0
|
1
|
cost threshold for parallelism
|
0
|
32767
|
cross db ownership chaining
|
0
|
1
|
cursor threshold
|
-1
|
2147483647
|
Database Mail XPs
|
0
|
1
|
default full-text language
|
0
|
2147483647
|
default language
|
0
|
9999
|
default trace enabled
|
0
|
1
|
disallow results from triggers
|
0
|
1
|
EKM provider enabled
|
0
|
1
|
filestream access level
|
0
|
2
|
fill factor (%)
|
0
|
100
|
ft crawl bandwidth (max)
|
0
|
32767
|
ft crawl bandwidth (min)
|
0
|
32767
|
ft notify bandwidth (max)
|
0
|
32767
|
ft notify bandwidth (min)
|
0
|
32767
|
index create memory (KB)
|
704
|
2147483647
|
in-doubt xact resolution
|
0
|
2
|
lightweight pooling
|
0
|
1
|
locks
|
5000
|
2147483647
|
max degree of parallelism
|
0
|
32767
|
max full-text crawl range
|
0
|
256
|
max server memory (MB)
|
128
|
2147483647
|
max text repl size (B)
|
-1
|
2147483647
|
max worker threads
|
128
|
65535
|
media retention
|
0
|
365
|
min memory per query (KB)
|
512
|
2147483647
|
min server memory (MB)
|
0
|
2147483647
|
nested triggers
|
0
|
1
|
network packet size (B)
|
512
|
32767
|
Ole Automation Procedures
|
0
|
1
|
open objects
|
0
|
2147483647
|
optimize for ad hoc workloads
|
0
|
1
|
PH timeout (s)
|
1
|
3600
|
precompute rank
|
0
|
1
|
priority boost
|
0
|
1
|
query governor cost limit
|
0
|
2147483647
|
query wait (s)
|
-1
|
2147483647
|
recovery interval (min)
|
0
|
32767
|
remote access
|
0
|
1
|
remote admin connections
|
0
|
1
|
remote login timeout (s)
|
0
|
2147483647
|
remote proc trans
|
0
|
1
|
remote query timeout (s)
|
0
|
2147483647
|
Replication XPs
|
0
|
1
|
scan for startup procs
|
0
|
1
|
server trigger recursion
|
0
|
1
|
set working set size
|
0
|
1
|
show advanced options
|
0
|
1
|
SMO and DMO XPs
|
0
|
1
|
transform noise words
|
0
|
1
|
two digit year cutoff
|
1753
|
9999
|
user connections
|
0
|
32767
|
user options
|
0
|
32767
|
xp_cmdshell
|
0
|
1
|
Note : Be careful.. Changing settings affects your instance, the results could affect your instance, performance, availability, initially change the configuration settings development environment, after testing implement in production.
No comments:
Post a Comment