Für die Konfiguration bestimmter Parameter im SQL-Server (Version SQL Server 2016) müssen die Advanced Options aktiviert werden.
Aktivierung
Für die Aktivierung der weiteren Optionen führt man im Management Studio folgendes Script aus:
EXEC sp_configure 'show advanced options',1 GO RECONFIGURE WITH OVERRIDE GO
Um die Aktivierung der Advanced Options direkt freizuschalten ist die Zeile
RECONFIGURE WITH OVERRIDE GO
unbedingt auszuführen.
Options
Nach der Installation des MS SQL-Server sind nur einige Parameter über
EXEC sp_configure GO
zu sehen:
| name | minimum | maximum | config_value | run_value |
|---|---|---|---|---|
| allow polybase export | 0 | 1 | 0 | 0 |
| allow updates | 0 | 1 | 0 | 0 |
| backup checksum default | 0 | 1 | 0 | 0 |
| backup compression default | 0 | 1 | 0 | 0 |
| clr enabled | 0 | 1 | 0 | 0 |
| contained database authentication | 0 | 1 | 0 | 0 |
| cross db ownership chaining | 0 | 1 | 0 | 0 |
| default language | 0 | 9999 | 0 | 0 |
| external scripts enabled | 0 | 1 | 0 | 0 |
| filestream access level | 0 | 2 | 0 | 0 |
| hadoop connectivity | 0 | 7 | 7 | 7 |
| max text repl size (B) | -1 | 2147483647 | 65536 | 65536 |
| nested triggers | 0 | 1 | 1 | 1 |
| polybase network encryption | 0 | 1 | 1 | 1 |
| remote access | 0 | 1 | 1 | 1 |
| remote admin connections | 0 | 1 | 0 | 0 |
| remote data archive | 0 | 1 | 0 | 0 |
| remote login timeout (s) | 0 | 2147483647 | 10 | 10 |
| remote proc trans | 0 | 1 | 0 | 0 |
| remote query timeout (s) | 0 | 2147483647 | 600 | 600 |
| server trigger recursion | 0 | 1 | 1 | 1 |
| show advanced options | 0 | 1 | 0 | 0 |
| user options | 0 | 32767 | 0 | 0 |
Aufstellung der Optionen nach Aktivierung:
| name | minimum | maximum | config_value | run_value |
|---|---|---|---|---|
| access check cache bucket count | 0 | 65536 | 0 | 0 |
| access check cache quota | 0 | 2147483647 | 0 | 0 |
| Ad Hoc Distributed Queries | 0 | 1 | 0 | 0 |
| affinity I/O mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity64 I/O mask | -2147483648 | 2147483647 | 0 | 0 |
| affinity64 mask | -2147483648 | 2147483647 | 0 | 0 |
| Agent XPs | 0 | 1 | 1 | 1 |
| allow polybase export | 0 | 1 | 0 | 0 |
| allow updates | 0 | 1 | 0 | 0 |
| automatic soft-NUMA disabled | 0 | 1 | 0 | 0 |
| backup checksum default | 0 | 1 | 0 | 0 |
| backup compression default | 0 | 1 | 0 | 0 |
| blocked process threshold (s) | 0 | 86400 | 0 | 0 |
| c2 audit mode | 0 | 1 | 0 | 0 |
| clr enabled | 0 | 1 | 0 | 0 |
| common criteria compliance enabled | 0 | 1 | 0 | 0 |
| contained database authentication | 0 | 1 | 0 | 0 |
| cost threshold for parallelism | 0 | 32767 | 5 | 5 |
| cross db ownership chaining | 0 | 1 | 0 | 0 |
| cursor threshold | -1 | 2147483647 | -1 | -1 |
| Database Mail XPs | 0 | 1 | 0 | 0 |
| default full-text language | 0 | 2147483647 | 1033 | 1033 |
| default language | 0 | 9999 | 0 | 0 |
| default trace enabled | 0 | 1 | 1 | 1 |
| disallow results from triggers | 0 | 1 | 0 | 0 |
| EKM provider enabled | 0 | 1 | 0 | 0 |
| external scripts enabled | 0 | 1 | 0 | 0 |
| filestream access level | 0 | 2 | 0 | 0 |
| fill factor (%) | 0 | 100 | 0 | 0 |
| ft crawl bandwidth (max) | 0 | 32767 | 100 | 100 |
| ft crawl bandwidth (min) | 0 | 32767 | 0 | 0 |
| ft notify bandwidth (max) | 0 | 32767 | 100 | 100 |
| ft notify bandwidth (min) | 0 | 32767 | 0 | 0 |
| hadoop connectivity | 0 | 7 | 7 | 7 |
| index create memory (KB) | 704 | 2147483647 | 0 | 0 |
| in-doubt xact resolution | 0 | 2 | 0 | 0 |
| lightweight pooling | 0 | 1 | 0 | 0 |
| locks | 5000 | 2147483647 | 0 | 0 |
| max degree of parallelism | 0 | 32767 | 0 | 0 |
| max full-text crawl range | 0 | 256 | 4 | 4 |
| max server memory (MB) | 128 | 2147483647 | 10240 | 10240 |
| max text repl size (B) | -1 | 2147483647 | 65536 | 65536 |
| max worker threads | 128 | 65535 | 0 | 0 |
| media retention | 0 | 365 | 0 | 0 |
| min memory per query (KB) | 512 | 2147483647 | 2048 | 2048 |
| min server memory (MB) | 0 | 2147483647 | 0 | 16 |
| nested triggers | 0 | 1 | 1 | 1 |
| network packet size (B) | 512 | 32767 | 4096 | 4096 |
| Ole Automation Procedures | 0 | 1 | 0 | 0 |
| open objects | 0 | 2147483647 | 0 | 0 |
| optimize for ad hoc workloads | 0 | 1 | 0 | 0 |
| PH timeout (s) | 1 | 3600 | 60 | 60 |
| polybase network encryption | 0 | 1 | 1 | 1 |
| precompute rank | 0 | 1 | 0 | 0 |
| priority boost | 0 | 1 | 0 | 0 |
| query governor cost limit | 0 | 2147483647 | 0 | 0 |
| query wait (s) | -1 | 2147483647 | -1 | -1 |
| recovery interval (min) | 0 | 32767 | 0 | 0 |
| remote access | 0 | 1 | 1 | 1 |
| remote admin connections | 0 | 1 | 0 | 0 |
| remote data archive | 0 | 1 | 0 | 0 |
| remote login timeout (s) | 0 | 2147483647 | 10 | 10 |
| remote proc trans | 0 | 1 | 0 | 0 |
| remote query timeout (s) | 0 | 2147483647 | 600 | 600 |
| Replication XPs | 0 | 1 | 0 | 0 |
| scan for startup procs | 0 | 1 | 0 | 0 |
| server trigger recursion | 0 | 1 | 1 | 1 |
| set working set size | 0 | 1 | 0 | 0 |
| show advanced options | 0 | 1 | 1 | 1 |
| SMO and DMO XPs | 0 | 1 | 1 | 1 |
| transform noise words | 0 | 1 | 0 | 0 |
| two digit year cutoff | 1753 | 9999 | 2049 | 2049 |
| user connections | 0 | 32767 | 0 | 0 |
| user options | 0 | 32767 | 0 | 0 |
| xp_cmdshell | 0 | 1 | 0 | 0 |
Bei deaktivierten Optionen (vor der Version SQL Server 2016 Werkseinstellung) erhält man 23 Zeilen.
Bei aktivierten Optionen (vor der Version SQL Server 2016 Werkseinstellung) erhält man 76 Zeilen.
Deaktivierung
Mit dem Befehl
EXEC sp_configure 'show advanced options',0 GO RECONFIGURE WITH OVERRIDE GO
werden die Optionen wieder deaktivieren.
Sowie die Optionen aktiviert sind können wichtige Einstellungen per Script vorgenommen werden:
- max server memory (MB)
- min memory per query (KB)
- min server memory (MB)
- fill factor (%)
Es können natürlich auch die restlichen Optionen per Script konfiguriert werden. Die Syntax ändert sich nur innerhalb der gewünschten Option die geändert werden soll.
Rechte
Zum aktivieren der Optionen müssen dem User eines der folgenden Rechte zugewiesen sein:
- sysadmin
- serveradmin
Im SQL-Server 2016 aus müssen die Advanced Options nicht mehr manuell eingestellt werden. Hier hat Microsoft den Wert direkt auf 1 gesetzt.