Monday, February 20, 2017

Server Configuration options - SQL Server



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.

Using below command you can see the basic configuration settings information 

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

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...