Important Parameters of sp_configure
Starting
SQL Server 2008, Microsoft has introduced a new catalog view which can
be used to see various server-wide configuration option value in the
system.
sys.configurations (Transact-SQL)
It has a little-detailed output as compared to sp_configure.
Here are the interesting columns.
Is_dynamic:
This column is used to know if the option is dynamic or not. If the
value is 1 (one) then the parameter change takes effect when the
RECONFIGURE statement is executed. If the value is 0 (zero) the value
takes effect when the SQL Server service is restarted.
Is_advanced:
This column is used to know if the option is an advanced option or not.
If the value for a parameter is 1 then it’s an advanced option and
would is displayed or can be changed only when “show advanced options”
is set to 1 through sp_configure.
Below is the query which given an answer to our question!
-- these configuration values which need restart
SELECT name ,description
FROM sys.configurations
WHERE is_dynamic = 0
Max Server Memory
The Max Server Memory configuration option to configure how much memory SQL Server can consume.
SQL Server is configured by default to consume up to 2147483647 MB – that is 2 Petabytes
With 64 GB of physical memory I would configure the Max Server Memory setting with around 56 GB, so that the OS can consume and work with the remaining 8 GB.
Cost Threshold for Parallelism
The next configuration option that you have to change has to do with how SQL Server handles parallelism. Parallelism means that SQL Server is able to run operators in an execution plan across multiple worker threads. The goal of parallelism is to improve the throughput of your queries.
Max Degree of Parallelism (MAXDOP)
When an execution plan goes parallel in SQL Server, the Max Degree of Parallelism defines how many worker threads each parallel operator in the execution plan can use. The following picture shows the default configuration of this option.
Link for all parameter:
https://docs.google.com/spreadsheets/d/1BPtfElZ4ovNBK5vNWOIksDB3YYVKGczS6N5x-_eY818/edit?usp=drivesdk
