The configuration optimization of SQL Server needs to be adjusted according to system resources and business needs. Key points include memory allocation, maximum parallelism (MAXDOP), recovery mode and log management, and fill factor settings. 1. 2~4GB should be reserved for the operating system and other programs in memory allocation, and the rest can be allocated to SQL Server, and pay attention to memory balance under the NUMA architecture; 2. MAXDOP settings recommend that the number of cores of a single NUMA node is ≤ 8, otherwise it is set to 4 or 8. The OLTP system is suitable for lower values such as 1 or 2; 3. In recovery mode, the simple mode is suitable for development environment, the complete mode is suitable for production environment, and the log backup is required regularly. The initial size of the log file is recommended to be 25% of the data file; 4. The filling factor is defaulted to 0 (i.e. 100%), and in most cases, the default is best. Only consider lowering to 80~90 in frequently updated tables, and after modification, the index needs to be reconstructed. Each setting should be adjusted based on the actual load to avoid blindly copying parameters.

SQL Server configuration optimization is actually not mysterious. The key is to understand your system resources and business needs. Many people think about increasing memory or changing concurrent numbers as soon as they start, but the real optimization is based on a certain understanding of the mechanism of various settings.

Memory allocation: Don't let SQL Server be hungry, don't let it hold on

By default, SQL Server allows itself to use as much memory as possible, which is fine on most servers, but if the machine is running other services (such as IIS, report services, etc.), you have to manually limit the maximum memory.

• Recommended practice : Leave 2~4GB of memory for the operating system and other programs, and the rest can be allocated to SQL Server.
• How to call : Use SSMS to enter "Server Properties" → "Memory" and adjust "Maximum Server Memory".
• Note : If you use NUMA architecture, you should also consider whether the memory distribution of each node is balanced.

Sometimes you find that the server CPU is not high, but the query is slow. It may be because of insufficient memory, which causes frequent paging. At this time, appropriately increasing the memory limit can significantly improve performance.

Maximum parallelism (MAXDOP): Controls the rhythm of parallel query

This setting determines that a query can be executed with up to several threads. Too high may cause the CPU to compete for resources, and too low will waste the multi-core advantage.

• General suggestions :
  • The number of cores of a single NUMA node ≤ 8, set to 0 (no limit)
  • Otherwise, it is recommended to set to 4 or 8
• Special case : OLTP systems are usually more suitable for lower MAXDOPs (such as 1 or 2) to avoid long concurrent queries affecting other operations.

You can check the number of logical CPUs in the current system by querying sys.dm_os_sys_info , and then determine the specific value based on the actual load test.

Recovery mode and log management: Don't let transaction logs drag on you

Many users are not aware of the impact of recovery mode on performance. Simple recovery mode is suitable for development or testing environments, which can save the hassle of log backup; full recovery mode is suitable for production environments, but logs need to be maintained regularly.

• Common misunderstandings : The log file is too large because there is no log backup, rather than the problem of automatic file growth.
• Solution :
  • Regular log backup (even once a day)
  • Do not set the log file size too small, otherwise frequent growth will slow down the writing speed
  • The initial size is recommended to be set to at least 25% of the data file.

If the log file has been bloated very much, you can first do a log backup, then shrink the log file, and then adjust the initial size and growth strategy.

Fill Factor: Don't adjust randomly, the default value is often the most reasonable

The fill factor controls the degree of filling of the index page, which is 0 (i.e. 100%) by default. Some people think that lowering it can reduce page splitting, but in fact, the impact in most scenarios is not big, and instead wastes storage space and memory cache.

• When should I lower it?
  • If a table is updated very frequently and page splits often occur
  • You can try to set it to 80~90
• When will the default be maintained?
  • Most read-only or occasionally updated tables
  • OLAP type data warehouse

Remember, after modifying the fill factor, you must rebuild the index before it takes effect, and each reconstruction will bring certain performance overhead.

Basically these common configuration items. Not complicated, but each one is easily overlooked or mismatched. The key is to make adjustments based on the actual load situation, rather than copying other people's parameters.

The above is the detailed content of Optimizing SQL Server Configuration Settings. For more information, please follow other related articles on the PHP Chinese website!