What are the MSSQL configuration settings
SELECT configuration_id,
name AS 'Name',
CAST(VALUE AS DECIMAL(10,0)) AS 'Value',
CAST(minimum AS DECIMAL(10,0)) AS 'Min',
CAST(maximum AS DECIMAL(10,0)) AS 'Max',
CAST(value_in_use AS DECIMAL(10,0)) AS 'In use',
CAST(description AS VARCHAR(60)) AS 'Description',
CASE is_dynamic WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Dynamic',
CASE is_advanced WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Advanced'
FROM sys.configurations
ORDER BY name ;
GO
name AS 'Name',
CAST(VALUE AS DECIMAL(10,0)) AS 'Value',
CAST(minimum AS DECIMAL(10,0)) AS 'Min',
CAST(maximum AS DECIMAL(10,0)) AS 'Max',
CAST(value_in_use AS DECIMAL(10,0)) AS 'In use',
CAST(description AS VARCHAR(60)) AS 'Description',
CASE is_dynamic WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Dynamic',
CASE is_advanced WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Advanced'
FROM sys.configurations
ORDER BY name ;
GO
configuration_id Name Value Min Max In use Description Dynamic Advanced ---------------- ----------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------------------------------ ------- -------- 16391 Ad Hoc Distributed Queries 0 0 1 0 Enable or disable Ad Hoc Distributed Queries Yes Yes 1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask No Yes 1535 affinity mask 0 -2147483648 2147483647 0 affinity mask Yes Yes 16384 Agent XPs 0 0 1 0 Enable or disable Agent XPs Yes Yes 102 allow updates 0 0 1 0 Allow updates to system tables Yes No 1548 awe enabled 0 0 1 0 AWE enabled in the server No Yes 1569 blocked process threshold 0 0 86400 0 Blocked process reporting threshold Yes Yes 544 c2 audit mode 0 0 1 0 c2 audit mode No Yes 1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server Yes No 1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism Yes Yes 400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining Yes No 1531 cursor threshold -1 -1 2147483647 -1 cursor threshold Yes Yes 16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs Yes Yes 1126 default full-text language 1033 0 2147483647 1033 default full-text language Yes Yes 124 default language 0 0 9999 0 default language Yes No 1568 default trace enabled 1 0 1 1 Enable or disable the default trace Yes Yes 114 disallow results from triggers 0 0 1 0 Disallow returning results from triggers Yes Yes 109 fill factor (%) 0 0 100 0 Default fill factor percentage No Yes 1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl buffers Yes Yes 1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl buffers Yes Yes 1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text notifications buffers Yes Yes 1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text notifications buffers Yes Yes 1505 index create memory (KB) 0 704 2147483647 0 Memory for index create sorts (kBytes) Yes Yes 1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions with unknown outcome Yes Yes 1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight pooling No Yes 106 locks 0 5000 2147483647 0 Number of locks for all users No Yes 1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism Yes Yes 1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in full-text indexing Yes Yes 1544 max server memory (MB) 2147483647 16 2147483647 2147483647 Maximum size of server memory (MB) Yes Yes 1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text field in replication. Yes No 503 max worker threads 0 128 32767 0 Maximum worker threads No Yes 1537 media retention 0 0 365 0 Tape retention period in days No Yes 1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per query (kBytes) Yes Yes 1543 min server memory (MB) 0 0 2147483647 8 Minimum size of server memory (MB) Yes Yes 115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers Yes No 505 network packet size (B) 4096 512 32767 4096 Network packet size Yes Yes 16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation Procedures Yes Yes 107 open objects 0 0 2147483647 0 Number of open database objects No Yes 1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text protocol handler (s) Yes Yes 1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query Yes Yes 1517 priority boost 0 0 1 0 Priority boost No Yes 1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost allowed by query governor Yes Yes 1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query memory (s) Yes Yes 101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in minutes Yes Yes 117 remote access 1 0 1 1 Allow remote access No No 1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are allowed from remote clients Yes No 1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout Yes No 542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures Yes No 1520 remote query timeout (s) 600 0 2147483647 600 remote query timeout Yes No 16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs Yes Yes 1547 scan for startup procs 0 0 1 0 scan for startup stored procedures No Yes 116 server trigger recursion 1 0 1 1 Allow recursion for server level triggers Yes No 1532 set working set size 0 0 1 0 set working set size No Yes 518 show advanced options 0 0 1 0 show advanced options Yes No 16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs Yes Yes 16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs Yes Yes 1555 transform noise words 0 0 1 0 Transform noise words for full-text query Yes Yes 1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff Yes Yes 103 user connections 0 0 32767 0 Number of user connections allowed No Yes 1534 user options 0 0 32767 0 user options Yes No 16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant Procedures Yes Yes 16390 xp_cmdshell 0 0 1 0 Enable or disable command shell Yes Yes (62 row(s) affected)
How to change the configuration settings
SP_CONFIGURE 'show advanced options', 1;
GO
RECONFIGURE;
GO
GO
RECONFIGURE;
GO
- Use the sp_configure followed by the name exactly as shown in the name of the sys.configurations.
- Be very, VERY, VERY careful when changing the settings.
- The wrong configuration can kill your server, and make it come to a halt.
- You must run RECONFIGURE after setting a configuration option.