SQL SERVER – Server Configuration Options

The server configuration options can be managed using sp_configure system stored procedure and SQL Server Management Studio. Some options needs to be configured with SQL Server Surface Area Configuration Tool. The most frequent options can be configured through SQL Server Management Studio. The entire configuration options can be managed and modified using sp_configure.


Is essential for DBA to know the meaning and understand each server configuration options. We know that is hard to accomplish this task, but we can do it, if we find out one by one each of this options. Hence, we are going to try to understand one by one (day by day) these options on this blog.


First we need to know what are the all the server configuration options. To know what are the entire server configurations, we can look into sys.configuration system view or use sp_configure system stored procedure.


select * from sys.configurations;

or


sp_configure 'show advanced options',1

GO

reconfigure

Go

sp_configure

go


In the following table we can the see all the server configuration options, the minimum / maximum value for each option, default value. Each value has a letter A ( advanced option, to change this options the setting show advanced options to 1 is required), RR ( this options require restart the db engine) and SC ( automatic configuration, restart is not required).


Configuration option

Minimum value

Maximum value

Default

access check cache bucket count (A)

0

16384

0

access check cache quota (A)

0

2147483647

0

ad hoc distributed queries (A)

0

1

0

affinity I/O mask (A, RR)

-2147483648

2147483647

0

affinity64 I/O mask (A, only available on 64-bit version of SQL Server)

-2147483648

2147483647

0

affinity mask (A)

-2147483648

2147483647

0

affinity64 mask (A, RR), only available on 64-bit version of SQL Server

-2147483648

2147483647

0

Agent XPs (A)

0

1

0

(Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)

allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)

0

1

0

awe enabled (A, RR)

0

1

0

backup compression default

0

1

0

blocked process threshold (A)

0

86400

0

c2 audit mode (A, RR)

0

1

0

clr enabled

0

1

0

common criteria compliance enabled (A, RR)

0

1

0

cost threshold for parallelism (A)

0

32767

5

cross db ownership chaining

0

1

0

cursor threshold (A)

-1

2147483647

-1

Database Mail XPs (A)

0

1

0

default full-text language (A)

0

2147483647

1033

default language

0

9999

0

default trace enabled (A)

0

1

1

disallow results from triggers (A)

0

1

0

EKM provider enabled

0

1

0

filestream_access_level

0

2

0

fill factor (A, RR)

0

100

0

ft crawl bandwidth (max) , see ft crawl bandwidth(A)

0

32767

100

ft crawl bandwidth (min) , see ft crawl bandwidth(A)

0

32767

0

ft notify bandwidth (max) , see ft notify bandwidth(A)

0

32767

100

ft notify bandwidth (min) , see ft notify bandwidth(A)

0

32767

0

index create memory (A, SC)

704

2147483647

0

in-doubt xact resolution (A)

0

2

0

lightweight pooling (A, RR)

0

1

0

locks (A, RR, SC)

5000

2147483647

0

max degree of parallelism (A)

0

64

0

max full-text crawl range (A)

0

256

4

max server memory (A, SC)

16

2147483647

2147483647

max text repl size

0

2147483647

65536

max worker threads (A, RR)

128

32767

(1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)

0

Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+( -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.

media retention (A, RR)

0

365

0

min memory per query (A)

512

2147483647

1024

min server memory (A, SC)

0

2147483647

0

nested triggers

0

1

1

network packet size (A)

512

32767

4096

Ole Automation Procedures (A)

0

1

0

open objects (A, RR, obsolete)

0

2147483647

0

optimize for ad hoc workloads (A)

0

1

0

PH_timeout (A)

1

3600

60

precompute rank (A)

0

1

0

priority boost (A, RR)

0

1

0

query governor cost limit (A)

0

2147483647

0

query wait (A)

-1

2147483647

-1

recovery interval (A, SC)

0

32767

0

remote access (RR)

0

1

1

remote admin connections

0

1

0

remote login timeout

0

2147483647

20

remote proc trans

0

1

0

remote query timeout

0

2147483647

600

Replication XPs Option (A)

0

1

0

scan for startup procs (A, RR)

0

1

0

server trigger recursion

0

1

1

set working set size (A, RR, obsolete)

0

1

0

show advanced options

0

1

0

SMO and DMO XPs (A)

0

1

1

SQL Mail XPs (A)

0

1

0

transform noise words (A)

0

1

0

two digit year cutoff (A)

1753

9999

2049

user connections (A, RR, SC)

0

32767

0

User Instance Timeout (A, only appears in SQL Server 2008 Express.)

5

65535

60

user instances enabled (A, only appears in SQL Server 2008 Express.)

0

1

0

user options

0

32767

0

xp_cmdshell (A)

0

1

0


so, we are going to try day by day see some details for each of the server configuration options.


References:

SQL Server BOL - Server configuration options.

2 comentarios:

narayana p dijo...

It was so nice article. I was really satisfied by seeing this article. SQL server dba Online Training Bangalore

narayana p dijo...

Thanks for sharing this post. Your post is really very helpful its students.
SQL server dba Online Training Hyderabad

Publicar un comentario