In this article I will explain how to configure SQL Server Instance through SSMS. Right-click on the instance via SSMS and click properties.
You will see general information on the General tab.
Memory Settings
When we switch to the Memory tab, we see “Minimum server memory (in MB):” and “Maximum server memory (in MB):“. In this section, we can set the minimum and maximum memory that SQL Server Instance will use. Some concepts must be well understood for setting memory correctly. You can find the details in the article “Numa Nodes, MAX/MIN Server Memory, Log Pages In Memory and MAXDOP“.
In Other memory options;
“Index creation memory(in KB, 0=dynamic memory);” value indicates the minimum memory value to be allocated for sort operations when creating an index. I don’t recommend changing this value unless you have a specific problem when creating an index.
“Minimum memory per query (in KB);” value indicates the minimum amount of memory to be allocated for a query, as the name implies. I do not recommend changing this default value unless you have a specific need.
Processors Settings
On the Processors tab, in the Enable processors section,
Processor Affinity in SQL Server
If you select the “Automatically set processor affinity mask for all processor” option, threads are automatically distributed across all CPUs.
I/O Affinity in SQL Server
If you select the “Automatically set I/O affinity mask for all processors” option, sql server automatically distributes I/O related operations to all CPUs.
By not selecting these two options, you can distribute workloads between CPUs specifically as follows, but I wouldn’t recommend it. By selecting the first two options, we must ensure that the workload distribution is automatic. This is the default.
I recommend not changing the Maximum worker threads setting. 0 indicates that it is set automatically.
If you select the Boost SQL Server priority option, you increase the priority of SQL Server according to other services running on the operating system. I have not chosen this option so far in the environments I manage.
If you select the Use Windows fibers option, you can reduce the workload of some processes that are seen in symmetric multiprocessing (SMP) environments. I have not chosen this option until now in the environments I manage.
Security Settings
Server Authentication
If you select Windows Authentication mode from Server authentication in Security, you can only connect to SQL Instance with windows accounts.
If you select SQL server and Windows Authentication mode, you can connect to SQL Instance with both sql logins and windows logins. You can find detailed information in the article “How To Create a Login On SQL Server(Manage Logins)“.
Login Auditing
If you select Failed logins only in login auditing, SQL Server logs the failed logins in the Error Log.
Database Settings
In Database Settings, you can set the default index fill factor, enable compress backup, and set default paths to create databases.
Recovery inteval; This is the upper limit required to recover databases after restarting SQL Server. Default value is 0 which means that an automatic checkpoint occurs every 60 seconds. Do not change this configuration if you do not know what you do. If you want to understand database recovery operations and checkpoint, you may want to read the articles “What is Database Checkpoint” and “What is SQL Server Transaction Log”
In the Advanced tab, we can make many settings such as file stream, contained database, maxdop. You can find detailed information in the following articles. You can also use the Search section on many topics you are looking for.
“What is File Stream On SQL Server“,
“What is Contained Database and How To Create“,
“Numa Nodes, MAX/MIN Server Memory, Log Pages In Memory and MAXDOP”
Finally, you can grant permission to a login at the instance level from the permissions tab.
