The cache stores often seen in SQL Server is God horse stuff?

The cache stores often seen in SQL Server is God horse stuff?

When we execute the following SQL statement in SSMs to empty SQL Server cache, we will see some information in SQL Errorlog

DBCC Freeproccache

You can see Cachestore, object Plans, SQL plan, bound tress and other nouns

Cachestore Flush forThe'Object Plans'Cachestore (part of Plancache) Cachestore Flush forThe'SQL Plans'Cachestore (part of Plancache) Cachestore Flush forThe'Bound Trees'Cachestore (part of Plancache)

So what do these nouns mean? What is a functional component? This article introduces you to SQL Server Buffer Pool and everyone will know

Translated from:Bufferpool performance Counters

To the original effect

There are a lot of articles, blogs, posts about memory configuration and memory in the Internet, but it still brings us too many misunderstandings.

These articles are all about SQL Server memory, virtual memory, AWE features, and other introductions. I personally think that it is not very good to SQL Server memory aspects of the various

Performance counters are well understood

In this article, I'll focus on some of the mechanisms of bufferpool, such as how your data pages and index pages are stored in SQL Server's memory.

In Performance Monitor you can use this knowledge to determine the state of the current bufferpool.

(When I talk about the page, I just talk about the data page and the index page.) For transaction logging, there is a special log manager that I will be

Another article that explains how logging is written to a log file)

First, let's look at some of the concepts associated with buffer Pool. A simple buffer Pool concept map like that.

Pools and CACHE STORES

The buffer pool will typically cache homogeneous, stateless data. All types of data are considered equal in the cache pool

Example: Connection pool or network buffer

The cache stores is often used as caching state data and provides a set of memory allocation interfaces for different customer reuse.

For example, the stored procedure cache is divided into several different cache stores

1. The first type of SQL execution plan for storing AD-HOC types

2. The second execution plan for storing stored procedures, functions, triggers, etc.

3. Third execution plan for storing extended stored procedures

The free List

SQL Server maintains a minimum number of idle page records in the free list to be able to process incoming requests as quickly as possible.

SQL Server will try to maintain the number of idle pages in the free list ("Min free" in the output of DBCC memorystatus)

is calculated based on the size of the Buffer pool and the number of requests passed in (page average life, the lifetime of the page in the cache is an indicator)

When you run DBCC memorystatus , do you pay attention to his output, in fact every section is a cache store

Understand:

Cache: Caching

Store: Store

There are many such cache storesin SQL Server buffer pool, and each store sells something different, and more interesting is every store

Sell only the same goods

Write and release operations on cached pages

SQL Server uses LRU (Least recently used least recently used algorithm) to calculate the page life in buffer pool

MYSQL and ORACLE are also using the LRU algorithm to clear the cache

Basically, when a page is referenced every time, the counter will rise, and when the lazy writer process caches the page, the counter will be lowered

Other worker threads check the memory state of the current buffer pool at a specific time (for example, when an asynchronous I/O occurs) to ensure that the new request has sufficient

The number of free pages available. If the free cache is not enough, then two things happen:

Event One: if the upper limit of the Buffer pool has been reached (this limit is based on "max server memory" and the current operating system is available)

Both will be reflected in the SQL Server memory Manager:target server memory This counter)

LazyWriter process clears part of buffer Pool cache

The lazywriter process clears several policies for the buffer Pool cache:

1. He will track pages that have not been purged since the last time the cache was cleared, this time to clear

2, according to the last time the page was referenced, if too long no reference to clear

3, whether the data page is dirty page, if it is dirty page is brushed into the disk

When cleared, the free list is modified to tell you which pages are currently idle, and if the transaction log records are not written to disk, the dirty pages will not be brushed until the transaction log records

has been written to disk

Event Two: if the Buffer pool has not reached the upper limit

Then SQL Server submits more reserved pages (reserved pages to the operating system for more memory) into the buffer pool, rather than just like that,

Clear some pages to return the purged pages back to the free List

That's why page life expectancy This counter is very high on a server with a small bottleneck (because the system has a lot of memory)

The data page does not need to be requested out of Buffer Pool.

and Process:private Bytes (sqlservr.exe) and SQL Server memory Manager:total server memory will grow, even on a single server

Less activity

Three threads to write data pages and release data pages

(i) LazyWriter thread

The lazywriter thread is a system thread that brushes a batch of dirty pages in buffer pool and modifies the free List of buffer pool

The main work of the lazywriter thread is to maintain the idle list

(ii) Checkpoint thread

The Checkpoint thread is also a system thread that wakes and checks for a certain amount of time if each database exceeds the recovery interval

His main job is to brush dirty data pages to disk to reduce transaction log undo and redo transactions when the database is restored, however, Checkpoint will not

To update the free List

(ii) Eager Write thread

The Eager write thread is a special write mechanism used as an I/O operation for non-logged, such as BULK INSERT and select INTO.

The purpose of this thread is to avoid a useless object called Buffer Pool (when large-volume data operations such as BULK insert are taken,

These data pages that are read into the buffer pool are generally less likely to be reused, so when the bulk operation is completed, the eager Write thread will

These data pages are cleared

Eager the difference between the Write thread and the lazywriter thread:

LazyWriter Thread: The lazy writer thread fires when there is not enough memory available in the operating system or the buffer pool has reached the limit, and a new request needs to use the data page, so it is called lazy

When the lazy writer clears the data page, it waits for the transaction log record to flush to disk before the data page is flush to disk

Within a NUMA system, each NUMA node will have a lazywriter thread

Eager Write Thread: After the bulk operation, the data page cleanup work will be done immediately, so call Eager (diligent)

Summarize

This article briefly introduces the origin of the cache store and the structure of the SQL Server buffer pool, hoping to understand that SQL Server buffer pool is helpful

If there is no place, welcome everyone to shoot brick O (∩_∩) o