Monday, February 11, 2008

SQL Server 2005 Default Configuration check list

SQL Server 2005 Configuration check list

http://technet.microsoft.com/en-us/library/ms189631.aspx

http://www.sql-server-performance.com/articles/audit/2005_server_configuration_pt1_p1.aspx

S.No Configuration option Minimum Value Maximum Value Default Value
1 Ad Hoc Distributed Queries (A) 0 1 0
2 affinity I/O mask -2147483648 2147483647 0
3 affinity64 I/O mask (A, only available on 64-bit version of SQL Server) -2147483648 2147483647
4 affinity mask (A) -2147483648 2147483647 0
5 affinity64 mask (A, only available on 64-bit version of SQL Server) -2147483648 2147483647 0
6 Agent XPs (A) 0 1 0
7 allow updates 0 1 0
8 awe enabled (A, RR) 0 1 0
9 blocked process threshold (A) 0 1 0
10 c2 audit mode (A, RR) 0 1 0
11 clr enabled 0 1 0
12 common criteria compliance enabled (A, RR) 0 1 0
13 cost threshold for parallelism (A) 0 32767 5
14 cross db ownership chaining 0 1 0
15 cursor threshold (A) -1 2147483647 -1
16 Database Mail XPs (A) 0 1 0
17 default full-text language (A) 0 2147483647 1033
18 default language 0 9999 0
19 default trace enabled (A) 0 1 1
20 disallow results from triggers (A) 0 1 0
21 fill factor (A, RR) 0 100 0
22 ft crawl bandwidth (max), see ft crawl bandwidth(A) 0 32767 100
23 ft crawl bandwidth (min), see ft crawl bandwidth(A) 0 32767 0
24 ft notify bandwidth (max), see ft notify bandwidth(A) 0 32767 100
25 ft notify bandwidth (min), see ft notify bandwidth(A) 0 32767 0
26 index create memory (A, SC) 704 2147483647 0
27 in-doubt xact resolution (A) 0 2 0
28 lightweight pooling (A, RR) 0 1 0
29 locks (A, RR, SC) 5000 2147483647 0
30 max degree of parallelism (A) 0 64 0
31 max full-text crawl range (A) 0 256 4
32 max server memory (A, SC) 16 2147483647 2147483647
33 max text repl size 0 2147483647 65536
34 max worker threads (A, RR) 128 32767 0
35 media retention (A, RR) 0 365 0
36 min memory per query (A) 512 2147483647 1024
37 min server memory (A, SC) 2147483647 8
38 nested triggers 0 1
39 network packet size (A) 512 32767 4096
40 Ole Automation Procedures (A) 0 1 0
41 open objects (A, RR, obsolete) 0 2147483647 0
42 PH_timeout (A) 1 3600 60
43 precompute rank (A) 0 1 0
44 priority boost (A, RR) 0 1 0
45 query governor cost limit (A) 0 2147483647 0
46 query wait (A) -1 2147483647 -1
47 recovery interval (A, SC) 0 32767 0
48 remote access (RR) 0 1 1
49 remote admin connections 0 1 0
50 remote login timeout 0 2147483647 20
51 remote proc trans 0 1 0
52 remote query timeout 0 2147483647 600
53 Replication XPs Option (A) 0 1 0
54 scan for startup procs (A, RR) 0 1 0
55 server trigger recursion 0 1 1
56 set working set size (A, RR, obsolete) 0 1 0
57 show advanced options 0 1 0
58 SMO and DMO XPs (A) 0 1 1
59 SQL Mail XPs (A) 0 1 0
60 transform noise words (A) 0 1 0
61 two digit year cutoff (A) 1753 9999 2049
62 user connections (A, RR, SC) 0 32767 0
63 User Instance Timeout (A, only appears in SQL Server 2005 Express Edition) 5 65535 60
64 user options 0 32767 0
65 Web Assistant Procedures (A) 0 1 0
66 xp_cmdshell (A) 0 1 0