将具有内存优化表的数据库绑定至资源池Bind a Database with Memory-Optimized Tables to a Resource Pool

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

资源池表示可以管理的物理资源的子集。A resource pool represents a subset of physical resources that can be governed. 默认情况下, SQL ServerSQL Server 数据库绑定到默认资源池并使用其中的资源。By default, SQL ServerSQL Server databases are bound to and consume the resources of the default resource pool. 为保护 SQL ServerSQL Server ,防止一个或多个内存优化表使用其资源,以及防止其他内存使用者使用内存优化表需要的内存,建议对具有内存优化表的数据库创建单独的资源池来管理内存使用情况。To protect SQL ServerSQL Server from having its resources consumed by one or more memory-optimized tables, and to prevent other memory users from consuming memory needed by memory-optimized tables, you should create a separate resource pool to manage memory consumption for the database with memory-optimized tables.

一个数据库只能绑定至一个资源池。A database can be bound on only one resource pool. 不过您可以将多个数据库绑定至同一个资源池。However, you can bind multiple databases to the same pool. SQL ServerSQL Server 允许将没有内存优化表的数据库绑定到资源池,但它不会有任何影响。allows binding a database without memory-optimized tables to a resource pool but it has no effect. 如果在将来您想要在数据库中创建内存优化表,则可能要将数据库绑定到某一命名的资源池。You may want to bind a database to a named resource pool if, in future, you may want to create memory-optimized tables in the database.

在将数据库绑定到资源池之前,数据库和资源池必须已经存在。Before you can bind a database to a resource pool both the database and the resource pool must exist. 下次该数据库联机时,此绑定生效。The binding takes effect the next time the database is brought online. 有关详细信息,请参阅 Database StatesSee Database States for more information.

有关资源池的信息,请参阅 Resource Governor Resource PoolFor information about resource pools, see Resource Governor Resource Pool.

将数据库绑定至资源池的步骤Steps to bind a database to a resource pool

  1. 创建数据库和资源池Create the database and resource pool

    1. 创建数据库Create the database

    2. 确定 MIN_MEMORY_PERCENT 的 MAX_MEMORY_PERCENT 的最小值Determine the minimum value for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

    3. 创建一个资源池并配置内存Create a resource pool and configure memory

  2. 将数据库绑定到池Bind the database to the pool

  3. 确认绑定Confirm the binding

  4. 使绑定生效Make the binding effective

本主题中的其他内容Other content in this topic

创建数据库和资源池Create the database and resource pool

您可以按任意顺序创建数据库和资源池。You can create the database and resource pool in any order. 要注意的是,在将数据库绑定至资源池前,它们必须都已存在。What matters is that they both exist prior to binding the database to the resource pool.

创建数据库Create the database

以下 Transact-SQLTransact-SQL 创建一个名为 IMOLTP_DB 的数据库,它将包含一个或多个内存优化表。The following Transact-SQLTransact-SQL creates a database named IMOLTP_DB which will contain one or more memory-optimized tables. 路径 <driveAndPath> 必须在运行此命令前就存在。The path <driveAndPath> must exist prior to running this command.

CREATE DATABASE IMOLTP_DB  
GO  
ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_fg CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_fg' , FILENAME = 'c:\data\IMOLTP_DB_fg') TO FILEGROUP IMOLTP_DB_fg;  
GO  

确定 MIN_MEMORY_PERCENT 的 MAX_MEMORY_PERCENT 的最小值Determine the minimum value for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

在确定了内存优化表所需的内存后,确定所需的可用内存的百分比,并且将内存百分比设置为该值或更高值。Once you determine the memory needs for your memory-optimized tables, you need to determine what percentage of available memory you need, and set the memory percentages to that value or higher.

示例: Example:
对于此示例,我们将假定根据您的计算,确定了您的内存优化表和索引需要 16 GB 的内存。For this example we will assume that from your calculations you determined that your memory-optimized tables and indexes need 16 GB of memory. 假定您已提交了 32 GB 的内存供您使用。Assume that you have 32 GB of memory committed for your use.

乍看起来,可能需要将 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 设置为 50(16 是 32 的 50%)。At first glance it could seem that you need to set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to 50 (16 is 50% of 32). 但是,这不会向您的内存优化表提供足够的内存。However, that would not give your memory-optimized tables sufficient memory. 通过查看下表(可用于内存优化表和索引的内存百分比),我们可以看到,如果有 32 GB 的已提交内存,则只有该内存量的 80% 可用于内存优化表和索引。Looking at the table below (Percent of memory available for memory-optimized tables and indexes) we see that if there is 32 GB of committed memory, only 80% of that is available for memory-optimized tables and indexes. 因此,我们基于可用内存量而不是已提交内存来计算最小和最大百分比。Therefore, we calculate the min and max percentages based upon the available memory, not the committed memory.

memoryNeedeed = 16
memoryCommitted = 32
availablePercent = 0.8
memoryAvailable = memoryCommitted * availablePercent
percentNeeded = memoryNeeded / memoryAvailable

插入实际数量:Plugging in real numbers:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

因此,您需要至少 62.5% 的可用内存量来满足您的内存优化表和索引的 16 GB 要求。Thus you need at least 62.5% of the available memory to meet the 16 GB requirement of your memory-optimized tables and indexes. 因为 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的值必须是整数,所以,我们将它们设置为至少 63%。Since the values for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT must be integers, we set them to at least 63%.

创建一个资源池并配置内存Create a resource pool and configure memory

为内存优化表配置内存时,应基于 MIN_MEMORY_PERCENT 而非 MAX_MEMORY_PERCENT 完成容量规划。When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. 有关 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的信息,请参阅 ALTER RESOURCE POOL (Transact-SQL)See ALTER RESOURCE POOL (Transact-SQL) for information on MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. 对于内存优化表,这将提高内存可用性的可预测性,因为 MIN_MEMORY_PERCENT 会对其他资源池带来内存压力以确保其得到遵守。This provides more predictable memory availability for memory-optimized tables as MIN_MEMORY_PERCENT causes memory pressure to other resource pools to make sure it is honored. 为确保内存可用并帮助避免内存不足情况,MIN_MEMORY_PERCENT 与 MAX_MEMORY_PERCENT 的值应相同。To ensure that memory is available and help avoid out-of-memory conditions, the values for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT should be the same. 请参阅下面的 可用于内存优化表和索引的内存百分比 ,以了解基于已提交内存量的可用于内存优化表的内存百分比。See Percent of memory available for memory-optimized tables and indexes below for the percent of memory available for memory-optimized tables based on the amount of committed memory.

有关在虚拟机环境下工作时的详细信息,请参阅最佳做法:在虚拟机环境中使用内存中 OLTPSee Best Practices: Using In-Memory OLTP in a VM environment for more information when working in a VM environment.

下面的 Transact-SQLTransact-SQL 代码创建一个名为 Pool_IMOLTP 且可使用一半内存的资源池。The following Transact-SQLTransact-SQL code creates a resource pool named Pool_IMOLTP with half of the memory available for its use. 创建该池后,资源调控器重新配置为包括 Pool_IMOLTP。After the pool is created Resource Governor is reconfigured to include Pool_IMOLTP.

-- set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to the same value  
CREATE RESOURCE POOL Pool_IMOLTP   
  WITH   
    ( MIN_MEMORY_PERCENT = 63,   
    MAX_MEMORY_PERCENT = 63 );  
GO  
  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO  

将数据库绑定到池Bind the database to the pool

使用系统函数 sp_xtp_bind_db_resource_pool 将数据库绑定到资源池。Use the system function sp_xtp_bind_db_resource_pool to bind the database to the resource pool. 该函数使用两个参数:数据库名称和资源池名称。The function takes two parameters: the database name and the resource pool name.

以下 Transact-SQLTransact-SQL 定义数据库 IMOLTP_DB 与资源池 Pool_IMOLTP 的绑定。The following Transact-SQLTransact-SQL defines a binding of the database IMOLTP_DB to the resource pool Pool_IMOLTP. 该绑定要到数据库联机后才生效。The binding does not become effective until you bring the database online.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO  

系统函数 sp_xtp_bind_db_resourece_pool 使用两个字符串参数:database_name 和 pool_name。The system function sp_xtp_bind_db_resourece_pool takes two string parameters: database_name and pool_name.

确认绑定Confirm the binding

确认绑定,记录 IMOLTP_DB 的资源池 ID。Confirm the binding, noting the resource pool id for IMOLTP_DB. 它不应为 NULL。It should not be NULL.

SELECT d.database_id, d.name, d.resource_pool_id  
FROM sys.databases d  
GO  

使绑定生效Make the binding effective

将数据库绑定至资源池后,必须使数据库脱机后再恢复联机以使绑定生效。You must take the database offline and back online after binding it to the resource pool for binding to take effect. 如果在先前数据库已绑定至另一资源池,此操作将移除之前的资源池中分配的内存,内存优化表和索引的内存分配现在来自与数据库新绑定的资源池。If your database was bound to an a different pool earlier, this removes the allocated memory from the previous resource pool and memory allocations for your memory-optimized table and indexes will now come from the resource pool newly bound with the database.

USE master  
GO  
  
ALTER DATABASE IMOLTP_DB SET OFFLINE  
GO  
ALTER DATABASE IMOLTP_DB SET ONLINE  
GO  
  
USE IMOLTP_DB  
GO  

现在数据库已绑定至资源池。And now, the database is bound to the resource pool.

更改现有池的 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENTChange MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT on an existing pool

如果您将附加内存添加到服务器,或者添加内存优化表更改所需的内存量,则可能需要更改 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的值。If you add additional memory to the server or the amount of memory needed for your memory-optimized tables changes, you may need to alter the value of MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. 以下步骤演示了如何更改资源池的 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的值。The following steps show you how to alter the value of MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT on a resource pool. 有关要用于 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的值的指导信息,请参阅下面的部分。See the section below, for guidance on what values to use for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. 有关详细信息,请参阅主题最佳做法:在虚拟机环境下使用内存中 OLTPSee the topic Best Practices: Using In-Memory OLTP in a VM environment for more information.

  1. 使用 ALTER RESOURCE POOL 可更改 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT 的值。Use ALTER RESOURCE POOL to change the value of both MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT.

  2. 使用 ALTER RESURCE GOVERNOR 可用新值重新配置资源调控器。Use ALTER RESURCE GOVERNOR to reconfigure the Resource Governor with the new values.

示例代码Sample Code

ALTER RESOURCE POOL Pool_IMOLTP  
WITH  
     ( MIN_MEMORY_PERCENT = 70,  
       MAX_MEMORY_PERCENT = 70 )   
GO  
  
-- reconfigure the Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO  

可用于内存优化表和索引的内存百分比Percent of memory available for memory-optimized tables and indexes

如果将包含内存优化表的数据库和 SQL ServerSQL Server 工作负荷映射到同一资源池,资源调控器会为 内存中 OLTPIn-Memory OLTP 设置一个内部使用阈值,使资源池用户不产生资源使用冲突。If you map a database with memory-optimized tables and a SQL ServerSQL Server workload to the same resource pool, the Resource Governor sets an internal threshold for 内存中 OLTPIn-Memory OLTP use so that the users of the pool do not have conflicts over pool usage. 一般来说, 内存中 OLTPIn-Memory OLTP 使用阈值约为资源池的 80%。Generally speaking, the threshold for 内存中 OLTPIn-Memory OLTP use is about 80% of the pool. 下表列出了不同内存大小的实际阈值。The following table shows actual thresholds for various memory sizes.

在为 内存中 OLTPIn-Memory OLTP 数据库创建专用资源池时,您需要在考虑行版本和数据增长后估计内存中表所需的物理内存量。When you create a dedicated resource pool for the 内存中 OLTPIn-Memory OLTP database, you need to estimate how much physical memory you need for the in-memory tables after accounting for row versions and data growth. 在估计所需的内存后,你将使用 DMV sys.dm_os_sys_info 中“committed_target_kb”列所反映的针对 SQL 实例的提交目标内存所占用的百分比,创建一个资源池。Once you estimate the memory needed, you create a resource pool with a percent of the commit target memory for SQL Instance as reflected by column 'committed_target_kb' in the DMV sys.dm_os_sys_info. 例如,您可以将可供实例使用的总内存的 40% 用来创建资源池 P1。For example, you can create a resource pool P1 with 40% of the total memory available to the instance. 在此 40% 之外, 内存中 OLTPIn-Memory OLTP 引擎将获取一个较小的百分比来存储 内存中 OLTPIn-Memory OLTP 数据。Out of this 40%, the 内存中 OLTPIn-Memory OLTP engine gets a smaller percent to store 内存中 OLTPIn-Memory OLTP data. 这样做是为了确保 内存中 OLTPIn-Memory OLTP 不会占用来自该池的所有内存。This is done to make sure 内存中 OLTPIn-Memory OLTP does not consume all the memory from this pool. 这个较小的百分比值依赖于目标提交内存。This value of the smaller percent depends upon the Target committed Memory. 下表描述在引发 OOM 错误之前在资源池(命名资源池或默认资源池)中可用于 内存中 OLTPIn-Memory OLTP 数据库的内存。The following table describes memory available to 内存中 OLTPIn-Memory OLTP database in a resource pool (named or default) before an OOM error is raised.

目标提交内存Target Committed Memory 可用于内存中表的百分比Percent available for in-memory tables
<= 8 GB<= 8 GB 70%70%
<= 16 GB<= 16 GB 75%75%
<= 32 GB<= 32 GB 80%80%
<= 96 GB<= 96 GB 85%85%
>96 GB>96 GB 90%90%

例如,如果“目标提交内存”为 100 GB,并且估计内存优化表和索引需要 60GB 的内存,则可以使用 MAX_MEMORY_PERCENT = 67(需要的 60GB / 0.90 = 66.667GB – 化整为 67GB;67GB / 安装的 100GB = 67%)创建一个资源池,以便确保 内存中 OLTPIn-Memory OLTP 对象具有其需要的 60GB。For example, if your 'target committed memory' is 100 GB, and you estimate your memory-optimized tables and indexes need 60GBof memory, then you can create a resource pool with MAX_MEMORY_PERCENT = 67 (60GB needed / 0.90 = 66.667GB - round up to 67GB; 67GB / 100GB installed = 67%) to ensure that your 内存中 OLTPIn-Memory OLTP objects have the 60GB they need.

在数据库已绑定到某一命名资源池后,使用以下查询可查看跨不同资源池分配的内存。Once a database has been bound to a named resource pool, use the following query to see memory allocations across different resource pools.

SELECT pool_id  
     , Name  
     , min_memory_percent  
     , max_memory_percent  
     , max_memory_kb/1024 AS max_memory_mb  
     , used_memory_kb/1024 AS used_memory_mb   
     , target_memory_kb/1024 AS target_memory_mb  
   FROM sys.dm_resource_governor_resource_pools  

此示例输出显示,内存优化对象使用的内存在资源池 PoolIMOLTP 中为 1356 MB,上限为 2307 MB。This sample output shows that the memory taken by memory-optimized objects is 1356 MB in resource pool, PoolIMOLTP, with an upper bound of 2307 MB. 此上限控制可由映射到此池的用户和系统内存优化对象使用的总内存。This upper bound controls the total memory that can be taken by user and system memory-optimized objects mapped to this pool.

示例输出 Sample Output
此输出来自我们在上面创建的数据库和表。This output is from the database and tables we created above.

pool_id     Name        min_memory_percent max_memory_percent max_memory_mb used_memory_mb target_memory_mb  
----------- ----------- ------------------ ------------------ ------------- -------------- ----------------   
1           internal    0                  100                3845          125            3845  
2           default     0                  100                3845          32             3845  
259         PoolIMOLTP 0                  100                3845          1356           2307  

有关详细信息,请参阅 sys.dm_resource_governor_resource_pools (Transact-SQL)For more information, see sys.dm_resource_governor_resource_pools (Transact-SQL).

如果未将数据库绑定到某一命名资源池,则它将绑定到“默认”池。If you do not bind your database to a named resource pool, it is bound to the 'default' pool. 由于对于大多数其他分配 SQL ServerSQL Server 使用默认资源池,因此,对于感兴趣的数据库,您将不能使用 DMV sys.dm_resource_governor_resource_pools 精确监视内存优化表使用的内存。Since default resource pool is used by SQL ServerSQL Server for most other allocations, you will not be able to monitor memory consumed by memory-optimized tables using the DMV sys.dm_resource_governor_resource_pools accurately for the database of interest.

另请参阅See Also

sys.sp_xtp_bind_db_resource_pool (Transact-SQL) sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL) sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)
资源调控器 Resource Governor
资源调控器资源池 Resource Governor Resource Pool
创建资源池 Create a Resource Pool
更改资源池设置 Change Resource Pool Settings
删除资源池Delete a Resource Pool