Shulou(Shulou.com)05/31 Report--

This article introduces what configuration options need to be modified immediately after SQL Server installation is completed. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

You use the installation wizard to install the new SQL Server, and finally you click 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 read it right: the default installation of SQL Server is misconfigured in many ways. In today's article, I want to show you three configuration options that you need to modify immediately after the SQL Server installation is complete for faster performance. Let's get started!

Maximum server memory (Max Server Memory) disclaimer: if you are running your SQL Server on a 32-bit system these days, please throw away your hardware, buy a 64-bit system, install 64-bit SQL Server, and continue reading from here.

Now you should have a 64-bit SQL Server in front of you. 64-bit means you can theoretically access the memory size of 2 ^ 64-that's 16 exabytes (1 billion GB)! Because of this huge amount of memory, computer vendors currently limit the address bus of 64-bit systems to "only" 48 bits-- completely 64-bits have no real meaning. With a 48-bit address space, you can access 256TB 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. The following figure shows the configuration options after SQL Server is installed by default on a 64-bit system.

As you can see from the picture just now, the default configuration of SQL Server is to consume memory up to 2147483647MB-that's 2 gigabytes! Well, with a 48-bit address bus, we can only physically access 256TB memory, and now SQL Server can consume up to 2 gigabytes of memory? There's something wrong here. The maximum server memory setting is larger than the 32-bit maximum shaping value-2147483647. Nothing else. So SQL Server can consume more memory than physical addresses? 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 (there are no other programs / processes on the server) you should have at least 10% of the physical memory on the system. This means that you need to lower the maximum server memory setting. With the physical memory of 64GB, I will configure the maximum server memory as 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 SQL Server processing parallel overhead threshold. Parallelism means that SQL Server can run the operators in the execution plan through multiple worker threads. The purpose of parallelism is to improve the throughput of your queries. The first configuration option in SQL Server that affects parallelism is the so-called parallel overhead threshold:

Here the number you configure defines the query cost, which the query optimizer uses to find cheaper parallel execution plans. If the parallel plan found is cheaper, the plan will be executed, otherwise the serial plan will be executed. As you can see from the figure just now, the default configuration of SQL Server uses a cost threshold of 5. When the cost of your serial plan query is greater than 5, then the query optimizer runs query optimization again to find the possibility of cheaper parallel execution plans.

Unfortunately, the cost of 5 is now a very small number. So SQL Server is too quick to try to parallel your execution plan. It makes sense when you are dealing with larger query parallelism-such as reports or data warehouse situations. In the pure OLTP case, parallel planning symbolizes poor index design, because when you have a missing index, SQL Server needs to scan your entire clustered index (in combination with filtering (Filter) and residual predicate (residual predicate)), so your queries become more and more expensive, they pass through the cost threshold, and finally the query optimizer gives you a parallel plan. When people see parallel plans, they always worry! But the root of 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 only parallelizes larger queries for you. Even if you have a parallel plan in front of you, you should consider whether you can make this query cheaper by adding a supported non-clustered index. In addition, CXPACKET does not mean that you have a parallel problem in your system!

Maximum parallelism (Max Degree of Parallelism (MAXDOP)) when an execution plan enters parallelism in SQL Server, the maximum parallelism defines the worker threads available for each parallel operator in the execution plan. The following figure shows the default configuration of 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 kernels assigned to SQL Server (by default all kernels are assigned to SQL Server! ). You should be able to see that such a setting doesn't make sense, especially if you have a system with a lot of CPU kernels. Parallelization brings its own burden, and the more worker threads you use, the greater the burden.

One suggestion is to set the maximum degree of parallelism to the number of cores in a NUMA node. So when the query is executed, SQL Server tries to maintain a parallel plan in a NUMA node, which also improves performance.

Sometimes you will see that it is recommended to set the maximum parallelism to 1. This is not a good suggestion because it makes your "whole" SQL Server single thread! Even if maintenance operations (such as index reconstruction) have been performed in a single thread, this can seriously hurt performance! Of course, there are some "award-winning" products that instruct you to use the maximum parallelism of 1 (MAXOP).

Set the maximum parallelism (MAXDOP) of the SQL Server instance hosting the SharePoint database to 1 to ensure that a single SQL Server process can service each request.

After you have installed SQL Server, the real work of DBA begins: you need to configure your SQL Server installation to your hardware configuration. As you have seen 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 have seen SQL Server use the default options I mentioned here in a production environment because they are configured "later" and never happen "later".

The configuration options that need to be modified immediately after the SQL Server installation is completed will be shared here. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.