Talking about the management of SQL Server for memory

Brief introduction

Understanding SQL Server Management of memory is fundamental to SQL Server problem handling and performance tuning, and this article describes the memory principles of SQL Server for memory management.

Second-level storage (secondary storage)

For computers, the storage system is layered. The closer you are to the CPU, the happier it is, but the smaller the capacity (as shown in Figure 1). For example: the traditional computer storage architecture from the CPU from near to far is: CPU in the register, first-level cache, level two cache, memory, hard disk. But at the same time the farther away from the CPU the storage system will be larger than the previous storage system of an order of magnitude. For example, a hard disk is usually a magnitude larger than the memory of the same age.

Fig. 1. Computer Storage System

So for SQL Server, the memory that a normal production system configures is usually not able to load all the data, so it involves level two storage, which is the disk. As the last mechanical storage part of the modern computer system, the disk reading the data requires moving the head (specifically on the disk principle, you can see an article I wrote earlier), and because the data accessed by the database is often randomly distributed in various locations of the disk, so if the frequent read disk requires frequent moving heads, This performance will be very much below.

The computer-Body storage architecture is aware that the computer needs to read memory first for all data on all hard drives, so using a buffer of memory and reducing access to disk IO will be key to improving SQL Server performance, which is one of the starting points of this article writing.

SQL Server engine, a self tuning engine

Because SQL Server used to be geared to the small and medium enterprise market, the SQL Server storage engine was designed to be a product that did not require too much configuration to reduce deployment costs, but this is also a lot of people have been criticized by Microsoft's open configuration too little. As for how SQL Server uses memory, there is little room for direct configuration, only open configurations use AWE, and the maximum or minimum memory that an instance occupies, as shown in Figure 2.

Figure 2. Options for SQL Server controllable memory control

For a specific SQL Server, how to use memory, such as how much is allocated to the execution plan cache, how much is allocated to the data buffer, which cannot be regulated by configuration. This is why many other technology developers have a superiority over developers who use Microsoft technology, and it seems to me that although SQL Server offers few places for controllable configuration, many places can be "indirectly" configured with a knowledge of the principles. This also requires understanding some of the principles of Windows.