sql server 缓存_深入了解SQL Server缓冲区缓存

sql server 缓存

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture, and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

当我们谈论SQL Server中的内存使用时，我们通常是指缓冲区缓存。 这是SQL Server体系结构的重要组成部分，它负责以极快的速度查询频繁访问的数据。 了解缓冲区缓存的工作方式将使我们能够在SQL Server中正确分配内存，准确评估数据库如何访问数据，并确保代码中没有效率低下的问题，不会导致过多的数据被缓存。

缓冲区缓存中有什么？ (What is in the buffer cache?)

Hard disks are slow; memory is fast. This is a fact of nature for anyone that works with computers. Even SSDs are slow when compared to high-performance memory. The way in which software deals with this problem is to write data from slow storage into fast memory. Once loaded, your favorite apps can perform very fast and only need to go back to disk when new data is needed. This fact of life in computing is also an important part of SQL Server architecture.

硬盘很慢； 记忆速度很快。 对于任何使用计算机的人来说，这都是自然的事实。 与高性能内存相比，即使SSD也很慢。 软件处理此问题的方法是将数据从慢速存储写入快速内存。 加载后，您喜欢的应用程序可以非常快速地执行，并且仅在需要新数据时才需要返回磁盘。 计算中的这一事实也是SQL Server体系结构的重要组成部分。

Whenever data is written to or read from a SQL Server database, it will be copied into memory by the buffer manager. The buffer cache (also known as the buffer pool) will use as much memory as is allocated to it in order to hold as many pages of data as possible. When the buffer cache fills up, older and less used data will be purged in order to make room for newer data.

每当将数据写入SQL Server数据库或从SQL Server数据库读取数据时，缓冲区管理器就会将其复制到内存中。 缓冲区高速缓存（也称为缓冲池）将使用分配给它的尽可能多的内存，以容纳尽可能多的数据页。 当缓冲区高速缓存填满时，将清除较旧和较少使用的数据，以便为较新的数据腾出空间。

Data is stored in 8k pages within the buffer cache and can be referred to as “clean” or “dirty” pages. A dirty page is one that has been changed since last being written to disk and is the result of a write operation against that index or table data. Clean pages are those that have not changed, and the data within them still matches what is on disk. Checkpoints are automatically issued in the background by SQL Server that will write dirty pages to disk in order to create a known good restore point in the event of a crash or other unfortunate server situation.

数据存储在缓冲区高速缓存中的8k页中，可以称为“干净”或“脏”页。 脏页是自上次写入磁盘以来已更改的页，并且是针对该索引或表数据进行写操作的结果。 干净页是未更改的页，并且其中的数据仍与磁盘上的内容匹配。 SQL Server将在后台自动发出检查点，该检查点会将脏页写入磁盘，以便在发生崩溃或其他不幸的服务器情况时创建已知的良好还原点。

You can see an overview of the current state of memory usage in SQL Server by checking the sys.dm_os_sys_info DMV:

您可以通过检查sys.dm_os_sys_info DMV来查看SQL Server中内存使用的当前状态的概述：

 
SELECT
	physical_memory_kb,
	virtual_memory_kb,
	committed_kb,
	committed_target_kb
FROM sys.dm_os_sys_info;
 

The results of this query tell me a bit about memory usage on my server:

该查询的结果告诉我有关服务器上内存使用情况的一些信息：

Here are what the columns mean:
physical_memory_kb: Total physical memory installed on the server.
virtual_memory_kb: Total amount of virtual memory available to SQL Server. Ideally, we do not want to utilize this often as virtual memory (using a page file on disk or somewhere that isn’t memory) is going to be significantly slower than memory.
Committed_kb: The amount of memory currently allocated by the buffer cache for use by database pages.
Committed_target_kb: This is the amount of memory the buffer cache “wants” to use. If the amount currently in use (indicated by committed_kb) is higher than this amount, then the buffer manager will begin to remove older pages from memory. If the amount currently in use is lower, than the buffer manager will allocate more memory for our data.

以下是各列的含义：
physical_memory_kb ：服务器上安装的总物理内存。
virtual_memory_kb ：SQL Server可用的虚拟内存总量。 理想情况下，我们不希望经常使用此功能，因为虚拟内存（在磁盘上或非内存位置使用页面文件）会比内存慢得多。
Committed_kb ：缓冲区高速缓存当前分配的内存量，供数据库页面使用。
Committed_target_kb ：这是缓冲区高速缓存“想要”使用的内存量。 如果当前正在使用的数量（由commit_kb表示）高于该数量，则缓冲区管理器将开始从内存中删除较旧的页面。 如果当前使用的数量少于该数量，则缓冲区管理器将为我们的数据分配更多的内存。

Memory use is critical to SQL Server performance—if there isn’t enough memory available to service our common queries, then we’ll spend far more resources reading data from disk, only to have it thrown away and read again later.

内存使用对SQL Server性能至关重要-如果没有足够的内存来服务于我们的常见查询，那么我们将花费更多的资源从磁盘读取数据，只是丢弃并稍后再读取。

我们如何使用缓冲区缓存指标？ (How can we use buffer cache metrics?)

We can access information about the buffer cache using the dynamic management view sys.dm_os_buffer_descriptors, which provides everything you’ve ever wanted to know about the data stored in memory by SQL Server, but were afraid to ask. Within this view, you’ll find a single row per buffer descriptor, which uniquely identifies, and provides some information about each page in memory. Note that on a server with large databases, it may take a bit of time to query this view.

我们可以使用动态管理视图sys.dm_os_buffer_descriptors来访问有关缓冲区缓存的信息，该视图提供了您曾经想知道的有关SQL Server存储在内存中的数据的所有信息，但又不敢问。 在此视图中，每个缓冲区描述符将找到一行，它唯一地标识并提供有关内存中每个页面的一些信息。 请注意，在具有大型数据库的服务器上，查询该视图可能会花费一些时间。

A useful metric that is easy to get is a measure of buffer cache usage by database on the server:

易于获取的有用指标是服务器上数据库对缓冲区高速缓存使用情况的度量：

 
SELECT
    databases.name AS database_name,
    COUNT(*) * 8 / 1024 AS mb_used
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.databases
ON databases.database_id = dm_os_buffer_descriptors.database_id
GROUP BY databases.name
ORDER BY COUNT(*) DESC;
 

This query returns, in order from most pages in memory to fewest, the amount of memory consumed by each database in the buffer cache:

该查询按从内存中的大多数页面到最少页面的顺序返回缓冲区高速缓存中每个数据库消耗的内存量：

My local server isn’t terribly exciting right now…but if I were to jump in and run an assortment of queries against AdventureWorks2014, we could run our query from above again to verify the impact it had on the buffer cache:

我的本地服务器现在并不十分令人兴奋……但是，如果我要跳入并针对AdventureWorks2014运行各种查询，我们可以再次从上方运行查询以验证其对缓冲区高速缓存的影响：

While I didn’t go too crazy here, my random querying did increase the amount of data in the buffer cache for AdventureWorks2014 by 27MB. This query can be a useful way to quickly determine which database accounts for the most memory usage in the buffer cache. On a multi-tenant architecture, or a server in which there are many key databases sharing resources, this can be a quick method to find a database that is performing poorly or hogging memory at any given time.

虽然我在这里并没有太疯狂，但我的随机查询确实使AdventureWorks2014的缓冲区高速缓存中的数据量增加了27MB。 该查询是一种快速确定缓冲区高速缓存中哪个数据库占用最多内存的有用方法。 在多租户架构或服务器上，其中有许多共享资源的关键数据库，这可能是查找在任何给定时间性能不佳或占用内存的数据库的快速方法。

Similarly, we can view overall totals as a page or byte count:

同样，我们可以将总总数查看为页数或字节数：

 
SELECT
	COUNT(*) AS buffer_cache_pages,
	COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors;
 

This returns a single row containing the number of pages in the buffer cache, as well as the memory consumed by them:

这将返回一行，其中包含缓冲区高速缓存中的页面数以及它们消耗的内存：

Since a page is 8KB, we can convert the number of pages into megabytes by multiplying by 8 to get KB, and then divide by 1024 to arrive at MB.

由于页面大小为8KB，因此我们可以通过乘以8以获得KB，然后除以1024得出MB，从而将页面数转换为兆字节。

We can subdivide this further and look at how the buffer cache is used by specific objects. This can provide much more insight into memory usage as we can determine what tables are memory hogs. In addition, we can verify some interesting metrics, such as what percentage of a table is in memory currently, or what tables are infrequently (or not) used. The following query will return buffer pages and size by table:

我们可以进一步细分，并查看特定对象如何使用缓冲区缓存。 当我们可以确定哪些表是内存猪时，这可以提供有关内存使用情况的更多信息。 此外，我们可以验证一些有趣的指标，例如，当前表在内存中所占的百分比，或者不经常（或不经常）使用哪些表。 以下查询将按表返回缓冲区页面和大小：

 
SELECT
	objects.name AS object_nam