SQL Server 構成Option

SQL Server 2017 のサーバー構成オプション
詳細設定オプションの表示クエリ

EXEC sp_configure 'show advanced options',1
GO

RECONFIGURE
GO

EXEC sp_configure
GO

Defaultでは23個であるが、詳細オプションを表示する設定にすると77個のオプションが表示される。

No	name	minimum	maximum	config_value	run_value	Option:0
1	access check cache bucket count	0	65536	0	0
2	access check cache quota	0	2147483647	0	0
3	Ad Hoc Distributed Queries	0	1	0	0
4	affinity I/O mask	-2147483648	2147483647	0	0
5	affinity mask	-2147483648	2147483647	0	0
6	affinity64 I/O mask	-2147483648	2147483647	0	0
7	affinity64 mask	-2147483648	2147483647	0	0
8	Agent XPs	0	1	0	0
9	allow polybase export	0	1	0	0	○
10	allow updates	0	1	0	0	○
11	automatic soft-NUMA disabled	0	1	0	0
12	backup checksum default	0	1	0	0	○
13	backup compression default	0	1	0	0	○
14	blocked process threshold (s)	0	86400	0	0
15	c2 audit mode	0	1	0	0
16	clr enabled	0	1	0	0	○
17	clr strict security	0	1	1	1
18	common criteria compliance enabled	0	1	0	0
19	contained database authentication	0	1	0	0	○
20	cost threshold for parallelism	0	32767	5	5
21	cross db ownership chaining	0	1	0	0	○
22	cursor threshold	-1	2147483647	-1	-1
23	Database Mail XPs	0	1	0	0
24	default full-text language	0	2147483647	1041	1041
25	default language	0	9999	3	3	○
26	default trace enabled	0	1	1	1
27	disallow results from triggers	0	1	0	0
28	EKM provider enabled	0	1	0	0
29	external scripts enabled	0	1	0	0	○
30	filestream access level	0	2	0	0	○
31	fill factor (%)	0	100	0	0
32	ft crawl bandwidth (max)	0	32767	100	100
33	ft crawl bandwidth (min)	0	32767	0	0
34	ft notify bandwidth (max)	0	32767	100	100
35	ft notify bandwidth (min)	0	32767	0	0
36	hadoop connectivity	0	7	0	0	○
37	index create memory (KB)	704	2147483647	0	0
38	in-doubt xact resolution	0	2	0	0
39	lightweight pooling	0	1	0	0
40	locks	5000	2147483647	0	0
41	max degree of parallelism	0	32767	0	0
42	max full-text crawl range	0	256	4	4
43	max server memory (MB)	128	2147483647	2147483647	2147483647
44	max text repl size (B)	-1	2147483647	65536	65536	○
45	max worker threads	128	65535	0	0
46	media retention	0	365	0	0
47	min memory per query (KB)	512	2147483647	1024	1024
48	min server memory (MB)	0	2147483647	0	16
49	nested triggers	0	1	1	1	○
50	network packet size (B)	512	32767	4096	4096
51	Ole Automation Procedures	0	1	0	0
52	open objects	0	2147483647	0	0
53	optimize for ad hoc workloads	0	1	0	0
54	PH timeout (s)	1	3600	60	60
55	polybase network encryption	0	1	1	1	○
56	precompute rank	0	1	0	0
57	priority boost	0	1	0	0
58	query governor cost limit	0	2147483647	0	0
59	query wait (s)	-1	2147483647	-1	-1
60	recovery interval (min)	0	32767	0	0
61	remote access	0	1	1	1	○
62	remote admin connections	0	1	0	0	○
63	remote data archive	0	1	0	0	○
64	remote login timeout (s)	0	2147483647	10	10	○
65	remote proc trans	0	1	0	0	○
66	remote query timeout (s)	0	2147483647	600	600	○
67	Replication XPs	0	1	0	0
68	scan for startup procs	0	1	0	0
69	server trigger recursion	0	1	1	1	○
70	set working set size	0	1	0	0
71	show advanced options	0	1	1	1	○
72	SMO and DMO XPs	0	1	1	1
73	transform noise words	0	1	0	0
74	two digit year cutoff	1753	9999	2049	2049
75	user connections	0	32767	0	0
76	user options	0	32767	0	0	○
77	xp_cmdshell	0	1	0	0

30番の「filestream access level」くらいしか操作したことないです…

まとめ

StackEditで表を作ってみたくてやってみました。
Excelの表から文字列結合で作れるかと思ってやりましたが、
行毎に空行が出来てしまい77回削除が必要になりイマイチな感じ。
勉強のために次回は、「remote admin connections」について調べてみたいと思います。