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

This article will explain in detail the example analysis of memory in sql server. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

one。 Preface

For sql server, memory is the most important resource. When we create a new session, the second query time of the same sql statement is often faster than the first query, especially in sql statistics or a large number of query data output. In addition to compiling and generating the execution plan for the first time, in addition to the impact of CPU,I/O, the most important thing is that the second query is read from the memory cache, why, what is stored in sql server memory, and what is the difference between windows memory and windows memory?

two。 Memory and hard disk

Why memory is precious, on every system is limited, like you see 1 tb hard disk, but what you usually see is 50-200G memory, physical memory access speed is very fast, can not exceed a certain limit. In the case of limited memory, if all processes use limited memory, and the new process will not be able to find any memory for them, this gives rise to the concept of virtual address space (also known as VAS).

Virtual Address Space (virtual address space)

Refers to the maximum address space that an application can request access to. The maximum 32-bit addressing space is 4G, and the maximum 64-bit addressing space is 8TB.

VAS as the middle abstraction layer, not all requests are mapped directly to physical memory, it first maps to VAS and then to physical memory. As a result, it can manage requests to memory in a more coordinated manner rather than letting the process do it, and if not, it can quickly lead to memory corruption.

In the Windows operating system, the division between VAS kernel processes and user processes is the same. For 32-bit systems, the largest VAS is the 4 G kernel / 2 G into the application, where SQL Server is the application process, and when I use the word process, it means that the SQL Server process is pretty much the same and will get 2 G VAS. So, in theory, this means that any application process running on 32-bit will have a maximum of 2G.

Three sql server memory architecture

Sql server memory management, which has undergone a major change in sql server 2012, has been reimplemented for memory. Let's first take a look at the differences in memory management diagrams between versions.

Nomenclature

3.1 BufferPool

SQL Server uses the BufferPool buffer pool to effectively manage memory requests from SQL Server processes. It is the largest memory consumer of SQL Server. A buffer is an 8-KB page in memory, the same size as a data or index page, and you can think of the buffer as a frame that holds data and index pages from disk to memory.

The SQL Server buffer manager manages the task of reading data pages into the buffer pool and writing them to disk. It is the reserved memory store for SQL Server, and if you don't set a value for it, it will consume as much memory as possible. Therefore, setting the optimal value for max server memory in spconfigure is always recommended as a good practice. The buffer pool allocates memory only to requests that require less than 8 KB pages.

All requests larger than 8 KB of memory are allocated directly by windows API. All cache storage plans, data, and index pages are stored in this buffer pool. When the user requests row/rows, if it is not in the buffer pool, the page is made to enter memory from disk. This input / output can be particularly expensive on busy systems, so minimize the size of the SQL server cache, which may be seen by users as a memory leak or SQL Server takes up a lot of memory, but it actually improves performance, which is actually implemented by design.

The following memory does not come from the buffer pool:

SQL LCR

Extended stored procedure

Memory allocated by the linked server

Large page allocation completed by the memory manager (large pages are any page > 8 KB)

COM object

3.2 single-page

This memory is 8kb storage, suitable for sql server 2008 and before, does not belong to the buffer pool buffer pool to allocate, there are storage Consumer functional components, third-party code, Threads threads.

3.4 any size page

This applies to sql server 2012 and above and integrates single-page,multi-page collectively known as pages.

four。 Sql server 2008 memory

From the memory chart, we can see that there is memory that page reservation needs to apply in advance, memory that momory objects requests from windows api, and memory that clr third parties apply for.

There are many ways to classify memory. Here are three ways:

1. Classify by use

1.1 Database Cache (data page buffer)

When a user modifies data on a page, sql server modifies the page in the page memory. But will not immediately write this page back to the hard drive, but wait for the later checkpoint or lazy write centralized processing.

1.2 all kinds of Consumer functional components

Connection connection: includes input buffer pool and output buffer pool to store user instructions and return results.

General: a hodgepodge of statements, statement compilation, normalization, lock data structures, transaction context, tables, index metadata, etc.

Query paln: the execution plan for statements and stored procedures.

The memory consumed by Optimizer:sql server in the process of generating the execution plan.

Utilities: like BCP, Log Manager,Parallel Queries,Backup

1.3 Thread memory

Allocate 0.5MB memory for each thread

1.4 memory requested by third-party codes

For example, user-defined CLR,Linked Server distributed queries retrieve large amounts of data from remote databases.

two。 Classified by way of application

The application method is to Reserve a large piece of memory in advance, and then a small piece of commit. For Database Cache, you will first Reserve, then commit.

All other memory usage is basically direct commit, which is called Stolen.

3. Classified by application size (this is the classification of the memory chart above)

There are two kinds of memory request units: one is less than or equal to 8KB, called Buffer Pool, and the allocation of one page at a time is called single page allocation.

One is larger than 8kb, called Multi-page (formerly MemToLeave), and this distribution is called Multiple Page Allocation.

Note that a large part of the memory here is not controlled by sql server itself. Because the memory requested by the third party code is stored in Multi-page.

The relationship between memory classification methods

Types

Database cache

Data page buffer

Consumer

Functional component

3 Party code

Third party code

Threads

Thread

Reserved/Commit

Yes

Usually not.

Usually not.

No

Stolen

No

Yes

Yes

Yes

Buffer Pool

(single- page)

All

most

No

No

MemToLeave

(Multi-page)

No

A small part

All

All

V. Sql server 2012 memory

In sql server 2012, single page allocator and multi page allocator are unified, called any size page allocator. Max server memory no longer controls only the size of buffer pool as in previous versions, but also includes memory requests that are larger than 8kb. That is, max server memory can more accurately control the memory usage of SQL Server.

As shown in the following figure:

Use dmv to view the total memory space and occupied memory space of the current instance

-- Target Server Memory (KB) the maximum amount of memory that can be applied for

-- how much memory is currently used by Total Server Memory (KB)

You can also see from the space footprint below that as much memory is allocated to sql server, it will consume as much memory as it can to achieve optimal performance.

Select counter_name, ltrim (cntr_value*1.0/1024.0/1024.0) +'G' as memoryGB from master.sys.dm_os_performance_counters where counter_name like'% target%server%memory%'or counter_name like'% total%memory%'

Six summaries

When you start Microsoft SQL Server, SQL Server memory usage may continue to grow steadily rather than decrease, even if activity on the server is low. In addition, the Task Manager and performance Monitor may show that the physical memory available on the computer gradually decreases until the available memory is between 4 MB and 10 MB. This behavior itself does not represent a memory leak. This behavior is typical and is expected from the SQL Server buffer pool.

By default, SQL Server dynamically grows and reduces the size of the buffer pool (cache) based on the physical memory load reported by the operating system. As long as there is enough memory (4 MB and 10 MB) to prevent paging, the SQL Server buffer pool will continue to grow. When memory is allocated on the same computer as SQL Server, the SQL Server buffer manager frees memory as needed. SQL Server can free up several megabytes of memory per second. This allows SQL Server to quickly adapt to memory allocation changes.

You can set the upper and lower limits of memory (buffer pool) for the minimum server memory and maximum server memory configuration options for the SQL Server database engine

Note that setting the memory maximum max from the figure above only limits the size of the SQL Server buffer pool. Does not limit the remaining areas of unreserved memory that SQL Server allocates to other components, such as extended stored procedures, COM objects, unshared dll, EXEs, and MAPI components. The number of SQL Server private bytes exceeds the maximum server memory configuration due to previous allocations.

This is the end of this article on "sample Analysis of memory in sql server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please 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.