NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。

Part.13 : Getting Started with SQL Server for Beginners (Memory Management)


スポンサーリンク

In Part 12, I summarized how to use the consistency check command (DBCC CHECKDB) to repair a database that has encountered inconsistencies.

In this article, I will try to summarize the contents related to memory management of SQL Server.

 

 

 About SQL Server Memory Management

The SQL Server process has dynamic memory enabled by default, which means that it allocates as much memory as it can use to maximize performance and releases it as needed.

In a server with SQL Server installed, the SQL Server process generally uses a lot of memory, but this behavior is normal.

The memory available to SQL Server can be controlled by the server memory configuration options 'max server memory (MB)' and 'min server memory (MB)'.

 

 

The 'max server memory (MB)' is set to '2,147,483,647 (MB)' by default, which means that all the memory installed in the server is available for use.

Note that if a large amount of memory is requested by a process other than the SQL Server process (including the OS), the memory allocated by the SQL Server process may be paged out by the paging function of the OS, causing the paging in/out process to become a bottleneck, which may reduce the performance of query processing.

For this reason, it's a good idea to collect the baseline of the memory area used by processes other than SQL Server using a performance counter, etc., and set the value of 'max server memory (MB)' to the value below.

[the amount of physical memory installed] - [the amount of memory used by processes other than SQL Server + α]

 

The 'min server memory (MB)' is set to '0 (MB)' by default.

If other software/middleware is installed on the same server as SQL Server, and if a large amount of memory is consumed by processes other than SQL Server and then the free memory space of the entire server becomes insufficient, the SQL Server process will detect that the free memory space of the entire server is insufficient, and will act to reduce the memory space allocated by itself to the value specified in 'min server memory (MB)'.

Therefore, if a large amount of memory is consumed by a process other than SQL Server, the memory of SQL Server available will be lack and then some errors related to out-of-memory below may occur.
Error 701 : 'Insufficient system memory in resource pool '%1! '
Error 802 : 'Insufficient memory available in buffer pool.'

For this reason, I think it would be a good idea to explicitly specify 'min server memory (MB)'. For example, set it to 1024 MB, which listed in the SQL Server hardware requirements to reserve the minimum amount of memory for SQL Server process.

 

About the memory that can be controlled by max server memory 

Since SQL Server 2012, the architecture of memory management has changed drastically, increasing the memory area that can be controlled by 'max server memory (MB)'. 

f:id:nobtak:20210116192010p:plain

Memory that can be controlled by max server memory (details)

Cache included in Single Page (excerpt) 

  • SQL buffer pool (cache of page data containing the actual data)
  • Procedure cache (cache containing execution plan, etc.)
  • Log pool (cache of transaction logs)
  • Storage engine cache (cache containing database structure metadata, TempDB version information, file control blocks (FCBs), etc.)
  • SQL Optimizer Cache (cache used in the process of generating execution plans)
  • Query Workspace (cache used for sorting and joining data during query execution)
  • SQL connection pool (cache used for client connections)
  • SQL global access cache (cache used for locks, session information, etc.)

 

Cache included in Multi Page (excerpt) 

  • Extended Stored Procedures
  • XML Documents
  • Linked Server
  • Execution plan information over 8KB
  • Network packets over 8KB

 

Cache included in SQL CLR (excerpt) 

  • Memory used by the SQL CLR (e.g., assembly information)

 

Memory that cannot be controlled by max server memory (details)
  • Memory for the stack of SQL Server processes
  • Memory that has been directly allocated by the OS (Direct Windows Allocations (DWA)) - Memory allocation from executable modules (DLLs, etc.) loaded into the memory space of SQL Server processes and from objects created by automation procedures (sp_OA) cannot be allocated via the SQL Server Memory Clerk, and therefore cannot be controlled by max server memory.

 

Summary 

In this article, I have summarized the contents related to memory management of SQL Server. 

In the case of SQL Server, in general, it can be installed only with the default settings and run with good performance, but it is a good idea to consider the setting values of server memory configuration options both 'max server memory (MB)' and 'min server memory (MB)' before installation.

Note that these settings can be changed to take effect immediately without restarting a SQL Server service after installation.