What is CACHESTORES often seen in SQL Server?

What is CACHESTORES often seen in SQL Server? When we execute the following SQL statement in SSMS to clear the SQL Server cache, we will see some information in SQLERRORLOG DBCCFREEPROCCACHE. You can see the terms cachestore, objectplans, sqlplan, boundtress, and other terms cachest.

I often see cache stores in SQL Server. Why? When we execute the following SQL statement in SSMS to clear the 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 terms cachest.

Li Regular SeeIs the cache stores of Shenma Dongdong?

When we execute the following SQL statement in SSMS to clear the SQL Server cache, we willSeeSome information

DBCC FREEPROCCACHE

You canSeeTerms such as cachestore, object plans, SQL plan, and bound tress

cachestore flush for the 'Object Plans' cachestore (part of plan cache)cachestore flush for the 'SQL Plans' cachestore (part of plan cache)cachestore flush for the 'Bound Trees' cachestore (part of plan cache) 

So what do these terms mean? What is a functional component? This article introduces SQLSERVER Buffer Pool, and you will know it.

Translated from:Bufferpool Performance Counters

Original content

There are many articles, blogs, and posts on the Internet about memory configuration and memory, but there are still many misunderstandings.

These articles cover SQLSERVER memory, virtual memory, AWE functions, and other introductions. I personally think it is not good for memory.

Good understanding of performance counters

In this article, I will focus on some BufferPool mechanisms, such as how your data pages and index pages are stored in the memory of SQLSERVER.

In performance monitor, you can use this knowledge to determine the status of the current BufferPool.

(When talking about pages, I only talk about data pages and index pages. For Transaction log records, there is a special log manager I will

Another article describes how log records are written to log files)

First, let's take a look at some related concepts of the Buffer Pool. A simple Buffer Pool concept is like that.

POOLS AND CACHE STORES

The buffer pool usually caches similar stateless data. All types of data are considered equal in the cache pool.

Example: connection pool or network Buffer

Cache storesIt is usually used as cache status data and provides a set of memory allocation interfaces for reuse by different customers.

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

1. The first method is used to store ad-hoc SQL Execution plans.

2. The second method is used to store execution plans for stored procedures, functions, and triggers.

3. The third method is used to store the execution plan of the extended stored procedure.

The Free List

SQLSERVER maintains a minimum number of idle pages in the idle list to process incoming requests as quickly as possible.

SQLSERVER will try to maintain the number of idle pages in the idle list ("Min Free" is in the output of dbcc memorystatus)

The calculation is based on the Buffer Pool size and the number of incoming requests (average page life, the page life in the cache is an indicator)

Do you pay attention to the output results when running dbcc memorystatus? In fact, each section is a cache store

Understanding:

Cache:Cache

Store:Store

In the SQLSERVER Buffer Pool, there are many suchCache store)And each store sells different things. What's more interesting is that

Only sell the same item

Write and release cache pages

SQLSERVER uses LRU (Least Recently Used Least Recently uses algorithms) to calculate the page life in the Buffer Pool.

MYSQLAndORACLEThe LRU algorithm is also used to clear the cache.

Basically, when a page is referenced every time, the counter will rise. When the lazy writer process caches the page, the counter will also fall.

Other working threads will check the memory status of the Current Buffer Pool at a specific time (for example, when an asynchronous I/O occurs) to ensure that there is sufficient for new requests.

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

Event 1:If the upper limit of the Buffer Pool has been reached (the upper limit is based on "max server memory" and available memory of the current operating system)

Both are reflected in the SQL Server Memory Manager: Target Server Memory counter)

The lazywriter process clears some Buffer Pool caches.

Several Policies for the lazywriter process to clear the Buffer Pool cache:

1. He will track the pages that were not cleared when the cache was last cleared.

2. Based on the time when the page was last referenced, if the page was not referenced for too long, it will be cleared.

3. Check whether the data page is a dirty page. If it is a dirty page, it is flushed to the disk.

After cleaning, the Free List will be modified to tell Free List which idle pages are currently available. If the transaction log records have not been written to the disk, the dirty page will not be flushed into the transaction log record.

Written to disk

Event 2:If the Buffer Pool has not reached the upper limit

Then SQLSERVER submits more reserved pages (reserved pages apply for more memory from the operating system) to the Buffer Pool, instead,

Clear some pages and restore these cleared pages to the Free List again.

This is why the Page Life Expectancy counter is very high on a server with a small bottleneck (because the system still has a lot of memory)

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

And Process: Private Bytes (sqlservr.exe) and SQL Server Memory Manager: Total Server Memory will continue to grow, even on a Server

Fewer activities

Three threads for flushing data pages and releasing data pages

(1) Lazywriter thread

The Lazywriter thread is a system thread that will fl a batch of dirty pages in the buffer pool and modify the Free List of the buffer pool.

The main task of the Lazywriter thread is to maintain the idle list.

(2) Checkpoint thread

The Checkpoint thread is also a system thread that will wake up for a certain period of time and check whether each database has exceeded the Recovery interval

His main task is to fl dirty data pages to the disk to reduce the number of transaction logs undo and redo transactions during database restoration. However, Checkpoint does not

Update Free List

(2) Eager Write thread

The Eager Write thread is a special Write mechanism used for non-logged I/O operations, such as BULK INSERT and SELECT.

The purpose of this thread is to avoid useless things called Buffer Pool (when large data operations such as bulk insert occur,

The data pages read into the Buffer Pool are unlikely to be reused.

Clear these data pages

Difference between the Eager Write thread and Lazywriter thread:

Lazywriter thread: when the available memory in the operating system is insufficient or the buffer pool has reached the upper limit, and a new request needs to use the data page, the Lazy writer thread will be triggered. This is called Lazy.

When Lazy writer clears the data page, it will flush the data page to the disk only after the transaction log is flushed to the disk.

In the NUMA system, each NUMA node has a Lazywriter thread.

Eager Write thread: after a large volume operation is completed, the data page is cleared immediately, so it is called Eager (DILIGENCE)

Summary

This article briefly introduces the origins of the Cache Store and the structure of the SQLSERVER Buffer Pool. It is helpful for everyone to understand the SQLSERVER Buffer Pool.

If anything is wrong, you are welcome to make a brick o

Supplement

Use the latest LRU-K algorithm to clear pages in cache from SQLSERVER2005