Поделиться через

SQL Server 2017 Improved Resource Usage On Smaller Machines

  • Статья

What does improving resource usage on smaller machines really mean? https://support.microsoft.com/en-us/help/4078095

SQL Server is designed to scale across the spectrum of small machines to enterprise class servers.  The changes made in SQL Server 2017 CU4 improve resource usage on smaller machines, such as a VM or Container used for testing and development.  SQL Server always strives to provide you with the best experience, “out of the box” and will continue improvements in future releases.  For example, we are always reviewing the Linux package files in order to reduce the size of the package.

Default Max Worker Threads

In testing on smaller machines I uncovered unexpected worker count levels.   Studying the code I found that the ‘default max worker threads’ calculation did not take into account the RAM on the system.  The calculation was based on architecture only Default-Worker-Count = (starting count x86=256 : x64=512) + (## of workers per CPU on the system [x86=8 : x64=16]).  The change I made in SQL Server 2017 CU4 was to consider RAM as another factor in the default calculation.  On systems with limited memory the starting count for x64 is divided by 2, lowering the default max workers target (~2MB per worker.)  

  • The sp_configure ‘max workers’ setting can be used to override the value on your system. 
  • It is a best practice keep the setting above 128.
  • select * from sys.dm_os_sys_info outputs the active max worker setting value.

CLR Garbage Collection

There are two modes of CLR garbage collection, client and server (the default for SQL Server is Server mode.)  Server mode garbage collection reserves space for the CLR heap(s) based on number of CPUs on the system.  When SQLCLR is starting you may observe large memory reservations occurring during server mode garbage collection initialization.  These large reservations can trigger the operating system(s) to engage in swapping or other activities, anticipating the user of the new reservations.

Client mode garbage collection reduces parallelization and per CPU activities, reserving less memory and resources.  The change I made in SQL Server 2017 CU4 is to use Client garbage collection mode on smaller machines, removing the additional overhead of Server mode garbage collection.

What Is A Small System?

For SQL Server 2017 CU4 the small environment threshold is 2GB.

Bob Dorr - Principal Software Engineer SQL Server