sql 缓冲池_监视SQL Server中的内存文员和缓冲池分配

sql 缓冲池

The following article applies to SQL Server versions 2008 +

以下文章适用于SQL Server 2008 +版本

Adequate memory is one of the most important factors for a well-functioning instance of SQL Server. By design SQL Server manages its own memory allocations via the SQLOS rather than having the servers Operating System perform this task.

充足的内存是运行良好SQL Server实例的最重要因素之一。 通过设计，SQL Server通过SQLOS管理自己的内存分配，而不是由服务器操作系统执行此任务。

Therefore it’s safe to say that monitoring SQL Servers memory use is a very important administrative task and in this post I am going to show you how to use Dynamic Management Views to take a closer look at how SQL is using memory and how these benefit troubleshooting activities.

因此，可以肯定地说，监视SQL Server的内存使用是一项非常重要的管理任务，在本文中，我将向您展示如何使用动态管理视图来进一步了解SQL如何使用内存以及它们如何使故障排除活动受益。

Before we do that we need to see how much memory is on our server and how much is allocated to be used by SQL itself. This relates to the very first paragraph in this post, the difference between total memory and SQL memory is allocated to the operating system and how much that should be is really dependent on the total memory in the server. I have always started with a setting of 4Gb or 10% of the total memory, whichever is more and tested regularly.

在执行此操作之前，我们需要查看服务器上有多少内存以及分配给SQL本身使用的内存量。 这与本文的第一段有关，总内存和SQL内存之间的差异是分配给操作系统的，实际分配的内存量实际取决于服务器中的总内存。 我总是从4Gb或总内存的10％设置开始，以较高者为准，并定期进行测试。

To view the total server memory use the following query against the sys.dm_os_sys_memory DMV:

要查看服务器总内存，请对sys.dm_os_sys_memory DMV使用以下查询：

 
SELECT total_physical_memory_kb / 1024 AS MemoryMb 
FROM sys.dm_os_sys_memory
 

To view SQLs allocation we can query the sys.configurations table to see how SQL has been configured:

要查看SQL的分配，我们可以查询sys.configurations表以查看如何配置SQL：

 
SELECT name, value_in_use FROM sys.configurations 
WHERE name LIKE 'max server memory%'
 

This is an incredibly important setting for SQL Server because its default value at installation can cause performance problems. The reason is SQLOS and by default it will be allocated all of the RAM in the server and will dynamically release memory back to the Operating System by monitoring a memory thread. Whilst that is all well and good we can avoid this release of memory entirely be sensibly capping SQL Servers memory.

这对于SQL Server来说是非常重要的设置，因为安装时的默认值可能会导致性能问题。 原因是SQLOS，默认情况下将为服务器分配所有RAM，并通过监视内存线程将内存动态释放回操作系统。 尽管这很好，但我们可以完全避免这种释放内存的方式来合理地限制SQL Server的内存。

It’s also worth noting what else is running on your server. I’m a huge advocate of having dedicated SQL instances without anything else running on them and that applies to items like Analysis, Integration or Reporting services too. Whilst that is perfectly good advice it isn’t always possible for a number of reasons but again just make sure you have adequate resource.

还需要注意服务器上还运行着什么。 我极力主张拥有专用SQL实例，而不在它们之上运行任何其他实例，并且该实例也适用于诸如Analysis，Integration或Reporting服务之类的项目。 尽管这是一个非常好的建议，但由于多种原因，并非总是可能的，但是再次确保您有足够的资源。

A common misconception is that the maximum server memory setting applies to all of SQL Server, it doesn’t and its quite common to see, at the server level, SQL Server using more memory than this setting allows. The reason for this is that the configuration item only applies to the SQL Buffer Pool and various other components within SQL can consume more memory but it must be said that the Buffer Pool is mainly the biggest item of SQL memory allocation.

一个常见的误解是，最大服务器内存设置适用于所有SQL Server，但不适用于服务器级别，并且在服务器级别看到SQL Server使用的内存超过了此设置所允许的内存，这是很普遍的。 这样做的原因是该配置项仅适用于SQL缓冲池，并且SQL中的各种其他组件可能消耗更多的内存，但是必须说缓冲池主要是SQL内存分配的最大项。

To see how SQL is using memory internally we can query the sys.dm_os_memory_clerks DMV to view currently active memory clerks within SQL Server. A memory clerk sits between memory nodes and the memory components within SQL Server. Each component has its own memory clerk that interfaces with the memory nodes to allocate memory; these clerks can then be used to track resource consumption. This architecture also means that threads cannot directly interface with the low level memory allocators but must go to the clerks for memory requests.

若要查看SQL内部如何使用内存，我们可以查询sys.dm_os_memory_clerks DMV来查看SQL Server中当前活动的内存文员。 内存文员位于SQL Server中的内存节点和内存组件之间。 每个组件都有自己的存储服务员，该存储服务员与存储节点进行接口以分配内存。 这些职员然后可以用来跟踪资源消耗。 这种体系结构还意味着线程不能直接与低级内存分配器进行接口，而必须向管理员提出内存请求。

The test instance that will use has16Gb of RAM in the Server and I have allocated 8Gb to SQL Server, by running the following query I can see the top 5 memory consumers by clerk type and see how much they are using.

将在服务器中使用具有16Gb RAM的测试实例，并且我已将8Gb分配给SQL Server，通过运行以下查询，我可以看到按业务员类型划分的前5名内存使用者，并查看他们正在使用多少内存。

 
SELECT TOP(5) [type] AS [ClerkType],
SUM(pages_kb) / 1024 AS [SizeMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC
 

As I would expect the Buffer Pool is the largest consumer of memory within the instance with just over 4.5Gb allocated. The lock manager is next with just over 1Gb allocated for lock resources and the remaining clerks relate to allocations for the query plan. The CACHESTORE_OBJCP allocation refers to plans for stored procedures and functions. The CACHESTORE_SQLCP are plans not within those object types and refer to statements executed directly against SQL Server whilst the CACHESTORE_PHDR row shows algebrized trees for various objects.

如我所料，缓冲池是实例中最大的内存消耗者，分配的内存刚好超过4.5Gb。 接下来，锁管理器为锁资源分配了刚好超过1Gb的空间，其余文员与查询计划的分配有关。 CACHESTORE_OBJCP分配是指存储过程和功能的计划。 CACHESTORE_SQLCP不在这些对象类型之内的计划，而是指直接针对SQL Server执行的语句，而CACHESTORE_PHDR行则显示了各种对象的代用树。

On a busy SQL Server this information is really useful for us to capture at regular intervals so we can closely monitor memory allocation under normal workloads. If we were to experience performance problems where we suspect memory pressure we can repeat the query to see if memory is being allocated differently.

在繁忙SQL Server上，此信息对于我们定期捕获非常有用，因此我们可以在正常工作负载下密切监视内存分配。 如果我们在怀疑内存压力的情况下遇到性能问题，则可以重复查询以查看内存分配方式是否不同。

As an example here’s the same query taken when a full database consistency check is being ran against one of my test databases. We can see here that there’s a new memory clerk that is now in our top 5 allocations list, this particular clerk, SQLQERESERVATIONS is related to Memory Grant allocations within SQL Server.

作为示例，下面是对我的一个测试数据库运行完整数据库一致性检查时所采用的相同查询。 我们可以在这里看到，在我们的前5个分配列表中，有一个新的内存职员，这个特殊的职员SQLQERESERVATIONS与SQL Server中的内存授权分配有关。

Upon seeing the SQLQERESERVATIONS we can query the current memory grants using the sys.dm_exec_query_memory_grants DMV and by using the CROSS APPLY function to sys.dm_exec_sql_text we can return the query text that is associated with the process.

看到SQLQERESERVATIONS后，我们可以使用sys.dm_exec_query_memory_grants DMV查询当前的内存授予，并通过对sys.dm_exec_sql_text使用CROSS APPLY函数来返回与该进程关联的查询文本。

 
SELECT session_id, requested_memory_kb / 1024 as RequestedMemMb, 
granted_memory_kb / 1024 as GrantedMemMb, text
FROM sys.dm_exec_query_memory_grants qmg
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
 

The query returns the following single result and with only one process running we know the consistency check has had a direct effect on our memory allocations.

该查询返回以下单个结果，并且只有一个进程运行，我们知道一致性检查已对我们的内存分配产生了直接影响。

Here’s the query text:

这是查询文本：

 
DECLARE @BlobEater VARBINARY(8000) 
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB) 
FROM { IRowset 0xF022EAB907000000 } 
GROUP BY ROWSET_COLUMN_FACT_KEY 
>> WITH ORDER BY 
              ROWSET_COLUMN_FACT_KEY, 
              ROWSET_COLUMN_SLOT_ID, 
              ROWSET_COLUMN_COMBINED_ID, 
              ROWSET_COLUMN_FACT_BLOB 
OPTION (ORDER GROUP)
 

This is one example of how a resource intensive process can affect the internal memory allocations within SQL Server but what about monitoring the allocations within the Buffer Pool itself?

这是一个资源密集型过程如何影响SQL Server内部内存分配的示例，但是如何监视缓冲池本身的分配呢？

For that we use the sys.dm_os_buffer_descriptors DMV to see memory allocation broken down by database. Similar to the memory clerk view it is incredibly useful to capture and record this information at regular intervals and observe significant changes from what you have observed as “the norm”.

为此，我们使用sys.dm_os_buffer_descriptors DMV来查看按数据库细分的内存分配。 与记忆库视图类似，定期捕获和记录此信息并观察与您所观察到的“规范”相比有显着变化的信息非常有用。

 
SELECT TOP 5 DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);
 

Here are the results on a test instance:

以下是测试实例的结果：

I like to record the results of this query at regular intervals taking note of database memory allocations at various points during the working day or when intensive activities or maintenance is being undertaken. The key is understanding how SQL is working so that once these baseline values have been captured we can compare back to them during troubleshooting to see if any databases are utilising (or have been allocated) memory differently.

我想定期记录此查询的结果，并注意在工作日或进行大量活动或维护时在各个时间点的数据库内存分配。 关键是要了解SQL的工作方式，以便一旦捕获了这些基准值，我们便可以在故障排除期间将它们与它们进行比较，以查看是否有任何数据库在以不同方式利用（或分配）了内存。

By capturing memory clerk and buffer descriptor usage we can build a picture of how SQL is working under normal workloads. It also means we have this information readily available to us should we need to highlight issues and the effect that they are having on the system.

通过捕获内存职员和缓冲区描述符的使用情况，我们可以了解SQL在正常工作负载下的工作方式。 这也意味着，如果我们需要重点说明问题及其对系统的影响，那么我们便可以立即获得这些信息。

翻译自: https://www.sqlshack.com/monitoring-memory-clerk-and-buffer-pool-allocations-in-sql-server/

sql 缓冲池