sp_configure(Server-Level Configurations in SQL Server)

You can configure SQL Server with sp_configure at instance level.

In this article I want to explain most important configurations in sp_configure.

First we run the script below to make the configurations we want.

Then, after this script, we just run sp_configure.

Below you can find what I have configured on my system from the settings available in sp_configure.

We have to make each setting one by one.

MAXDOP(Maximum Degree Of Parallelism):

Maxdop specifies the level of parallelism of the incoming requests to the server over the CPU.

The default value is 0.

According to the incoming query type, sql server decides its own level of parallelism.

For example, while a query is running on a single core, another query can run on the 8 core.

This depends entirely on the structure of the queries coming into the databases.

Generally, when DSS(Decision Support System)systems have more parallelism, the system works more efficiently.

OLTP systems generally work well when the parallelism level is 1.

In general, SQL Server works well with default settings, but the MAXDOP setting is not included.

There is no such thing as the most appropriate maxdop value.

The MaxDop level varies depending on the application.

Therefore, you should determine the most appropriate MAXDOP level for system.

You should test each system’s maximum performance by making minor changes to the MaxDop value.

In this way you can find the optimum level of parallelism.

You can set it as follows.

MAX SERVER MEMORY:

SQL Server loves Memory much.

And if you do not set a limit, it can completely consume your memory.

This can cause bottlenecks in your operating system.

To prevent the operating system from falling into the memory bottleneck, you should set max server memory to limit the memory that sql server will use.

For example, on a server with 64 GB of memory, the MAX SERVER Memory I will set will not exceed 56 GB.

Maybe if you set this parameter to 60GB you will not have any problems. but I prefer to give a little more to the operating system.

The MAX Server Memory parameter is associated with Lock Pages In Memory.

If there are more than one instance, there are also settings that need to be made here.

For details, you should read the article titled “Numa Nodes, MAX / MIN Server Memory, Log Pages In Memory and MAXDOP“.

Backup Compression Default:

It allows you to compress backups by default.

Fill Factor:

While your indexes are rebuilding, it ensures that your Fill Factor settings are set to default, even if you do not specify Fill Factor during rebuild.

CLR:

If you run your .net compiled code on sql server, you need to enable this feature in order to run these dlls on sql server.

DAC:

If the ports used by SQL Server are inaccessible, the database administrator may need to connect through a special port to resolve the problem.

You need to activate this feature to be able to connect in this way. You can find the details in our article “DAC(Dedicated Administrator Connection)“.