In SQL serve, you're going to have to change. 3 configuration options

You installed the new SQL Server with the Installation Wizard, and finally you clicked the Finish button. Wow Oh ~ ~ ~ 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 misconfigured in many ways. In today's article, I want to show you, for faster performance, after the SQL Server installation is complete, 3 configuration options that you need to modify immediately. Here we go!

Max server memory (max server memories)

Disclaimer: If you run your SQL Server on a 32-bit system these days, throw away your hardware, buy a 64-bit system, install a 64-bit SQL Server, and read on from there.

Now in front of you you should I have a 64-bit SQL Server. 64 bits means you can theoretically access the 2^64 memory size--that's 16 bytes (1 billion GB)! Because of these massive amounts of memory, the computer vendor currently restricts the address bus for 64-bit systems "only" 48-bit--full 64 bits have no real meaning. With a 48-bit address space, you can access 256TB of memory-that's still a lot of space.

You can use the Maximum server memory configuration option to configure the amount of memory that SQL Server can consume. Displays the configuration options after SQL Server is installed by default on a 64-bit system.

As you can see from the picture just now, SQL Server default configuration can consume up to 2147483647MB of memory-that's 2000 trillion! Well, with a 48-bit address bus we can only physically access 256TB of memory, and now SQL Server can consume up to 2000 megabytes of memory? There's something wrong here ... The maximum server memory setting is--2147483647 larger than the 32-bit maximum shaping value. Nothing else. So SQL Server can consume more memory than the physical address? This is a very 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. In general (no other programs/processes on the server) you should system at least 10% of the physical memory. This means that you need to lower the maximum server memory setting. 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 for SQL Server to handle the 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 queries. The 1th configuration option in SQL Server that affects parallelism is the so-called parallel cost threshold:

Here you configure the number to define the query cost, which the query optimizer is looking for in order 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 a 5 cost threshold. When your serial plan query cost is greater than 5, then the query optimizer runs query optimizer again to find the possibility of a cheaper parallel execution plan.

Unfortunately, a 5 cost value is a small number in the present. So SQL Server tries to parallelize your execution plan too quickly. It makes sense when you're dealing with larger queries-such as reports or data Warehouse scenarios. In the case of pure OLTP, a parallel plan symbolizes a bad index design, because when you have a missing index, SQL Server needs to scan your entire clustered index (in combination with filter and residual predicate (residual predicate)). So your query costs are getting bigger, they go through the cost threshold, and the last query optimizer gives you a parallel plan. When people see parallel plans, they always worry! But the problem is the missing nonclustered index.

For parallel cost thresholds, I always recommend at least 20, or even 50. In that case, you make sure that SQL Server is only parallel to your larger query. Even if you have a parallel plan in front of you, you should also consider whether it is cheaper to add a supported nonclustered claim. 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 enters parallel in SQL Server, the maximum degree of parallelism defines the available worker threads for each parallel operator in the execution plan. 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 parallelize your execution plan through all CPU cores assigned to SQL Server (all cores are assigned to SQL server! by default) ）。 You should be able to see that this setting doesn't make sense, especially if you have a large number of CPU cores on the system. Parallelization itself is a burden, and once you use more work threads, the burden is greater.

One suggestion is to set the maximum degree of parallelism to the number of cores that are 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 will also see suggestions to set the maximum degree of parallelism to 1. This is bad advice because this makes your "whole" SQL Server single-threaded! Even if maintenance operations (such as index rebuilds) are single-threaded, this can seriously hurt performance! Of course there are some "award winning" products indicating that you use the maximum degree of parallelism (MAXOP) of 1 ...

Set the maximum degree of parallelism (MAXDOP) of 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 DBA's real work begins: You need to configure your SQL Server installation to your hardware configuration. As you've seen in this article, the default configuration for SQL Server is obviously wrong. Therefore, it is important to modify some configuration options immediately after installation. I've seen the production environment where SQL Server uses 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

Thanks for your attention!

In SQL serve, you're going to have to change. 3 configuration options