SQL code server configuration options
--Start AWE
sp_configure'show advanced options',1
reconfigure
go
sp_configure'awe enable',1--Activate the AWE option to support more than 4G of memory See note 3 for specific usage
go
sp_configure'show advanced options',0
reconfigure
go
--Specifies the number of rows in the cursor set
sp_configure'show advanced options',1
reconfigure
go
sp_configure'cursor threshold'--Specify the number of rows in the cursor set, beyond which the cursor key set will be generated asynchronously
go
sp_configure'show advanced options',0
reconfigure
go
/*The configuration option 'show advanced options' has been changed from 0 to 1. Run the RECONFIGURE statement to install it.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
cursor threshold -1 2147483647 -1 -1
The configuration option 'show advanced options' has been changed from 1 to 0. Run the RECONFIGURE statement to install it.*/
--Specifies the default language value for full-text index columns
sp_configure'show advanced options',1
reconfigure
go
sp_configure'default full-text language'--2052 stands for Chinese Simplified, specifically query online books
go
sp_configure'show advanced options',0
reconfigure
go
/*name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
default full-text language 0 2147483647 2052 2052*/
--Controls whether to let the trigger return the result set
sp_configure'show advanced options',1
reconfigure
go
sp_configure'disallow results from triggers',1--1 stands for on
go
sp_configure'disallow results from triggers',0--0 is off
go
sp_configure'show advanced options',0
reconfigure
go
/*The configuration option 'disallow results from triggers' has been changed from 1 to 1. Run the RECONFIGURE statement to install it.
The configuration option 'disallow results from triggers' has been changed from 1 to 0. Run the RECONFIGURE statement to install it.*/
--Controls the maximum amount of memory initially allocated for creating indexes
sp_configure'index create memory', 4096
GO
--Set the maximum number of locks available
sp_configure'show advanced options',1
reconfigure
go
sp_configure'locks'--- Add 'number' after if you want to set it
go
sp_configure'show advanced options',0
reconfigure
go
/*name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
locks 5000 2147483647 0 0
*/
--Sets the number of worker threads that can be used by the SQL process
sp_configure'show advanced options',1
reconfigure
go
sp_configure'max worker threads'--If you want to set it, add 'number' after it
go
sp_configure'show advanced options',0
reconfigure
go
/*name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max worker threads 128 32767 0 0
*/
--Specifies how long a query waits for the required resource before it times out
sp_configure'query wait', number
go
--Specifies how long the remote operation can last before SQL SERVER times out
sp_configure'remote query timeout', number
go
--Whether to allow system stored procedures to run xp_cmdshell
sp_configure'show advanced options',1
reconfigure
go
sp_configure'xp_cmdshell',1
reconfigure
go
sp_configure'show advanced options',0
reconfigure
go
--Control the execution of stored procedures from a local or remote server running a SQL SERVER instance
sp_configure'show advanced options',1
reconfigure
go
sp_configure'remote access',1 --1 means allowed
reconfigure
go
sp_configure'remote access',0 --0 means prohibited
reconfigure
go
sp_configure'show advanced options',0
reconfigure
go
--- More online book views
--Starts, pauses, and stops on-premises SQL SERVER services
net start MSSQLSERVER --initiate
net pause MSSQLSERVER --Time out
netcontinueMSSQLSERVER --- Continued suspended services
net stop MSSQLSERVER--Stop it
--Query server configuration option information
select * fromsys.configurations
go
--In the results obtained
configuration_id--Configure the unique ID of the option
name --The name of the configuration option
value --Configure the value of the option
minimum --Configure the minimum value of the option
maximum --The maximum value of the configuration option
value_in_use --Configure the running value that the option is currently using
description --Description of configuration options
is_dynamic --If it is equal to 1, it indicates that the variable needs to execute the reconfiguration statement to take effect
is_anvanced --If it is equal to 1, it indicates that the show advanced statement needs to be executed to take effect
--You can also use sp_configure to query the server configuration option information, but the parameters are different
|
Posted on 1/28/2015 1:11:49 PM
|
|
|
