3 configuration options that need to be modified immediately after SQL Server Setup is complete _mssql

You installed the new SQL Server with the Setup Wizard, and finally you clicked the Finish button. Wow ~ ~ ~ Now we can put our server into production! Sorry, that's not true, because your new SQL Server default configuration is wrong.

Yes, you're right: The default installation of SQL Server is incorrectly configured in many ways. In today's article, I want to show you, for faster performance, the 3 configuration options you need to modify immediately after the SQL Server installation is complete. Here we go!

max server memory (max server Memory)
Disclaimer: If you run your SQL Server on a 32-bit system these days, throw away your hardware, buy a 64-bit system, install 64-bit SQL Server, and read on from here.

Now in front of you you should I have a 64-bit SQL Server. 64-bit means you can theoretically access the 2^64 memory size--that's 16 bytes (1 billion GB)! Because of these huge amounts of memory, the computer vendor currently limits 64-bit systems to the address bus "only" 48-bit-full 64-bit doesn't really make sense. With 48-bit address space, you can access 256TB of memory--that's a lot of space.

You can use the Maximum server memory configuration option to configure the amount of memory that SQL Server can consume. The following figure shows the configuration options after the default installation of SQL Server on a 64-bit system.

As you can see from the picture just now, the SQL Server default configuration can consume up to 2147483647MB of memory-that's 2000 trillion! Well, with 48-bit address bus we can only physically access 256TB of memory, now SQL Server can consume up to 2000 megabytes of memory? There's something wrong here ... The maximum server memory setting is larger than the 32-bit maximum--2147483647 value. Nothing else. So SQL Server can consume more memory than the physical address? This is a bad default configuration. SQL Server can eat up your entire physical memory by default!

You should always change this configuration option so that you can give the system some memory so that it can breathe alive. Generally speaking (no other programs/processes on the server) you should have at least 10% of the physical memory. That means you need to reduce the maximum server memory settings . With 64GB of physical memory I will configure the maximum server memory to 56GB, so that the system can use the remaining 8G to consume and work.

Parallel overhead threshold (cost Threshold for Parallelism)
The next configuration option you need to modify is the threshold at which SQL Server handles parallel overhead. Parallelism means that SQL Server can run operators in the execution plan through multiple worker threads. The purpose of parallelism is to increase the throughput of your query. The 1th configuration option in SQL Server that affects parallelism is the so-called parallel overhead threshold :

Here you configure the number to define the query cost and query optimizer to find a cheaper parallel execution plan. If a parallel plan is found to be cheaper, the plan will be executed, or the serial plan will be executed. As you can see from the diagram just now, the SQL Server default configuration uses the cost threshold of 5. When your serial plan query costs more than 5, then the query optimizer runs query optimization again to find the possibility of a cheaper parallel execution plan.

Unfortunately, the cost value of 5 is now a very small number. So SQL Server is too fast trying to parallel your execution plan. It makes sense when you're dealing with larger queries--such as reports or data warehouse situations. In a pure OLTP scenario, a parallel plan is a bad index design, because when you have a missing index, SQL Server needs to scan your entire clustered index (in the combination of filtering (filter) and remaining predicate (residual predicate). So your query costs are getting bigger, they go through the cost threshold, and the final query optimizer gives you a parallel plan. When people see a parallel plan, they always worry! The source of the problem, however, is the absence of nonclustered indexes.

For concurrent cost thresholds, I always recommend at least 20, or even 50. In that case, you make sure that SQL Server is only running parallel to the larger query for you. Even if you have a parallel plan in front of you, you should also consider the possibility of a lower cost of the query by adding a supported nonclustered cable. In addition, Cxpacket does not symbolize that you have parallel problems in your system!

Maximum degree of parallelism (max Degree of Parallelism (MAXDOP))
When an execution plan in SQL Server goes into parallel, the maximum degree of parallelism defines the available worker threads for each parallel operator in the execution plan. The following figure shows the default configuration for this option.

As you can see, SQL Server uses the default value of 0. This value means that SQL Server attempts to parallel your execution plan through all the CPU cores that are assigned to SQL Server (by default, all kernels are assigned to SQL server! ）。 You should be able to see that this setting doesn't make sense, especially if you have a large number of CPU kernel systems. Parallelization itself brings burdens, and once you use more work threads, the burden is greater.

One suggestion is to set the maximum degree of parallelism for the number of cores owned in a NUMA node. Therefore, when the query executes, SQL Server attempts to maintain a parallel plan in a NUMA node, which also improves performance.

Sometimes you'll also see suggestions to set the maximum parallelism to 1. This is bad advice because this makes your "whole" SQL Server single-threaded! Even if a maintenance operation, such as an index rebuild, is executed on a single thread, this can severely damage performance! There are, of course, some "award-winning" products that instruct you to use the maximum degree of parallelism (MAXOP) of 1.

Set the maximum degree of parallelism (MAXDOP) for the SQL Server instance that hosts the SharePoint database to 1 to ensure that a single SQL Server procedure can serve each request.

Summary
After you install SQL Server, the real work of the DBA begins: You need to configure your SQL Server to install to your hardware configuration. As you can see in this article, the default configuration of SQL Server is obviously wrong. Therefore, it is important to modify some configuration options immediately after installation. I've seen SQL Server use the default options I mentioned here because they are "later" Configured and "later" never happened ...

So please do yourself a favor today, configure your SQL server! for maximum performance and throughput

Wonderful topic Sharing: SQL Server Installation Tutorial manual

After reading this article is not everyone has already acted, quickly to improve your SQL Server database!