Associar um banco de dados com tabelas com otimização de memória a um pool de recursosBind a Database with Memory-Optimized Tables to a Resource Pool

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Um pool de recursos representa um subconjunto de recursos físicos que podem ser controlados.A resource pool represents a subset of physical resources that can be governed. Por padrão, os bancos de dados do SQL ServerSQL Server estão associados a e consomem recursos do pool de recursos padrão.By default, SQL ServerSQL Server databases are bound to and consume the resources of the default resource pool. Para proteger o SQL ServerSQL Server de ter todos os recursos consumidos por uma ou mais tabelas com otimização de memória, e evitar que outros usuários da memória consumam a memória necessária para as tabelas com otimização de memória, você deve criar um pool de recursos separado para gerenciar o consumo de memória para o banco de dados com tabelas com otimização de memória.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.

Um banco de dados pode estar associado em apenas um pool de recursos.A database can be bound on only one resource pool. No entanto, você pode associar vários bancos de dados ao mesmo pool.However, you can bind multiple databases to the same pool. SQL ServerSQL Server permite associar um banco de dados sem tabelas com otimização de memória a um pool de recursos, mas isso não tem efeito.allows binding a database without memory-optimized tables to a resource pool but it has no effect. Talvez você queira associar um banco de dados a um pool de recursos denominado se, no futuro, talvez você quiser criar tabelas com otimização de memória no banco de dados.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.

Antes de associar um banco de dados a um pool de recursos, o banco de dados e o pool de recursos devem existir.Before you can bind a database to a resource pool both the database and the resource pool must exist. A associação entra em vigor da próxima vez que o banco de dados é colocado online.The binding takes effect the next time the database is brought online. Veja Estados de banco de dados para obter mais informações.See Database States for more information.

Para obter informações sobre pools de recursos, veja Pool de recursos do Administrador de Recursos.For information about resource pools, see Resource Governor Resource Pool.

Etapas para associar um banco de dados a um pool de recursosSteps to bind a database to a resource pool

  1. Criar o banco de dados e o pool de recursosCreate the database and resource pool

    1. Criar o banco de dadosCreate the database

    2. Determine o valor mínimo de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENTDetermine the minimum value for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

    3. Criar um pool de recursos e configurar a memóriaCreate a resource pool and configure memory

  2. Associar o banco de dados ao poolBind the database to the pool

  3. Confirmar a associaçãoConfirm the binding

  4. Tornar a associação efetivaMake the binding effective

Outros conteúdos neste tópicoOther content in this topic

Criar o banco de dados e o pool de recursosCreate the database and resource pool

Você pode criar o banco de dados e o pool de recursos em qualquer ordem.You can create the database and resource pool in any order. O que importa é que ambos existam antes de associar o banco de dados ao pool de recursos.What matters is that they both exist prior to binding the database to the resource pool.

Criar o banco de dadosCreate the database

O Transact-SQLTransact-SQL a seguir cria um banco de dados chamado IMOLTP_DB que conterá uma ou mais tabelas com otimização de memória.The following Transact-SQLTransact-SQL creates a database named IMOLTP_DB which will contain one or more memory-optimized tables. O caminho <driveAndPath> deve existir antes da execução desse comando.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  

Determine o valor mínimo de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENTDetermine the minimum value for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

Depois que você determinar as necessidades de memória para tabelas com otimização de memória, será necessário determinar o percentual de memória disponível necessário e definir os percentuais de memória com esse valor ou um valor mais alto.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.

Exemplo: Example:
Para este exemplo, vamos pressupor que, com base nos cálculos, você determinou que suas tabelas com otimização de memória e índices precisam de 16 GB de memória.For this example we will assume that from your calculations you determined that your memory-optimized tables and indexes need 16 GB of memory. Digamos que você tenha 32 GB de memória confirmada para uso.Assume that you have 32 GB of memory committed for your use.

À primeira vista pode parecer que você precisa definir MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT como 50 (16 são de 50% de 32).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). No entanto, isso não daria memória suficiente às tabelas com otimização de memória otimizada.However, that would not give your memory-optimized tables sufficient memory. Examinando a tabela abaixo (Percentual de memória disponível de índices e tabelas com otimização de memória), vemos que, se houver 32 GB de memória confirmada, somente 80% dessa quantidade estará disponível para índices e tabelas com otimização de memória.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. Consequentemente, calculamos percentuais mínimo e máximo com base na memória disponível, não na memória confirmada.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

Em números reais:Plugging in real numbers:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

Dessa forma, você precisa de pelo menos 62,5% da memória disponível para cumprir o requisito de 16 GB de tabelas com otimização de memória e índices.Thus you need at least 62.5% of the available memory to meet the 16 GB requirement of your memory-optimized tables and indexes. Como os valores de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT devem ser inteiros, vamos configurá-los como pelo menos 63%.Since the values for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT must be integers, we set them to at least 63%.

Criar um pool de recursos e configurar a memóriaCreate a resource pool and configure memory

Ao configurar a memória para tabelas com otimização de memória, o planejamento de capacidade deve ser feito com base em MIN_MEMORY_PERCENT, não 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. Veja ALTER RESOURCE POOL (Transact-SQL) para obter informações sobre MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT.See ALTER RESOURCE POOL (Transact-SQL) for information on MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. Isso fornece uma disponibilidade de memória mais previsível para tabelas com otimização de memória porque o MIN_MEMORY_PERCENT causa uma pressão de memória em outros pools de recursos para garantir que seja cumprida.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. Para garantir que essa memória esteja disponível e para ajudar a evitar condições de memória insuficiente, os valores de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT devem ser iguais.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. Veja Percentual de memória disponível de índices e tabelas com otimização de memória abaixo para obter o percentual de memória disponível para tabelas com otimização de memória com base na quantidade de memória confirmada.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.

Veja Práticas recomendadas: usando OLTP in-memory em um ambiente de máquina virtual para obter mais informações ao trabalhar em um ambiente de VM.See Best Practices: Using In-Memory OLTP in a VM environment for more information when working in a VM environment.

O código Transact-SQLTransact-SQL a seguir cria um pool de recursos chamado Pool_IMOLTP com metade da memória disponível para esse uso.The following Transact-SQLTransact-SQL code creates a resource pool named Pool_IMOLTP with half of the memory available for its use. Depois que o pool é criado, o Administrador de Recursos é reconfigurado para incluir 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  

Associar o banco de dados ao poolBind the database to the pool

Use a função do sistema sp_xtp_bind_db_resource_pool para associar o banco de dados ao pool de recursos.Use the system function sp_xtp_bind_db_resource_pool to bind the database to the resource pool. A função usa dois parâmetros: o nome do banco de dados e o nome do pool de recursos.The function takes two parameters: the database name and the resource pool name.

O Transact-SQLTransact-SQL a seguir define uma associação do banco de dados IMOLTP_DB ao pool de recursos Pool_IMOLTP.The following Transact-SQLTransact-SQL defines a binding of the database IMOLTP_DB to the resource pool Pool_IMOLTP. A associação não entra em vigor até que você coloque o banco de dados online.The binding does not become effective until you bring the database online.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO  

A função de sistema sp_xtp_bind_db_resourece_pool usa dois parâmetros de cadeia de caracteres: nome_do_banco_de_dados e nome_do_pool.The system function sp_xtp_bind_db_resourece_pool takes two string parameters: database_name and pool_name.

Confirmar a associaçãoConfirm the binding

Confirme a associação, observando a ID do pool de recursos para IMOLTP_DB.Confirm the binding, noting the resource pool id for IMOLTP_DB. Não deve ser NULL.It should not be NULL.

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

Tornar a associação efetivaMake the binding effective

Você deve colocar o banco de dados offline e depois online de novo depois de associá-lo ao pool de recursos para que a associação entre em vigor.You must take the database offline and back online after binding it to the resource pool for binding to take effect. Se seu banco de dados tiver sido associado a um pool diferente anteriormente, isso removerá a memória alocada do pool de recursos anterior, e as alocações de memória para sua tabela com otimização de memória e os índices agora virão do pool de recursos recém-associado ao banco de dados.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  

Agora o banco de dados está associado ao pool de recursos.And now, the database is bound to the resource pool.

Alterar MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT em um pool existenteChange MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT on an existing pool

Se você adicionar mais memória ao servidor ou se a quantidade de memória das suas tabelas com otimização de memória for alterada, talvez seja necessário alterar o valor de MIN_MEMORY_PERCENT e de 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. As etapas a seguir mostram como alterar o valor de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT em um pool de recursos.The following steps show you how to alter the value of MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT on a resource pool. Consulte a seção abaixo, para obter orientação sobre quais valores usar para MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT.See the section below, for guidance on what values to use for MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT. Confira o tópico Práticas recomendadas: Usando OLTP in-memory em um ambiente de máquina virtual para obter mais informações.See the topic Best Practices: Using In-Memory OLTP in a VM environment for more information.

  1. Use ALTER RESOURCE POOL para alterar o valor de MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT.Use ALTER RESOURCE POOL to change the value of both MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT.

  2. Use ALTER RESURCE GOVERNOR para reconfigurar o Administrador de Recursos com os novos valores.Use ALTER RESURCE GOVERNOR to reconfigure the Resource Governor with the new values.

Código de exemploSample 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  

Porcentagem de memória disponível para tabelas com otimização de memória e índicesPercent of memory available for memory-optimized tables and indexes

Se você mapear um banco de dados com tabelas com otimização de memória e uma carga de trabalho do SQL ServerSQL Server para o mesmo pool de recursos, o Administrador de Recursos definirá um limite interno para o OLTP na memóriaIn-Memory OLTP usar de modo que os usuários do pool não tenham conflitos sobre o uso do pool.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 OLTP na memóriaIn-Memory OLTP use so that the users of the pool do not have conflicts over pool usage. Em linhas gerais, o limite para o uso do OLTP na memóriaIn-Memory OLTP é de aproximadamente 80% do pool.Generally speaking, the threshold for OLTP na memóriaIn-Memory OLTP use is about 80% of the pool. A tabela a seguir mostra os limites reais para vários tamanhos de memória.The following table shows actual thresholds for various memory sizes.

Quando você cria um pool de recursos dedicado para o banco de dados OLTP na memóriaIn-Memory OLTP , precisa estimar a quantidade de memória física necessária para as tabelas na memória, após considerar versões de linhas e o crescimento de dados.When you create a dedicated resource pool for the OLTP na memóriaIn-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. Após calcular a memória necessária, crie um pool de recursos com uma porcentagem da memória de destino de confirmação para a Instância SQL, conforme refletido pela coluna ‘committed_target_kb’ no DMV sys.dm_os_sys_info (veja sys.dm_os_sys_info).Once 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 (see sys.dm_os_sys_info). Por exemplo, você pode criar um pool de recursos P1 com 40% da memória total disponível para a instância.For example, you can create a resource pool P1 with 40% of the total memory available to the instance. Além desses 40%, o mecanismo de OLTP na memóriaIn-Memory OLTP obtém uma porcentagem menor para armazenar dados de OLTP na memóriaIn-Memory OLTP .Out of this 40%, the OLTP na memóriaIn-Memory OLTP engine gets a smaller percent to store OLTP na memóriaIn-Memory OLTP data. Isso é feito para garantir que OLTP na memóriaIn-Memory OLTP não consuma toda a memória desse pool.This is done to make sure OLTP na memóriaIn-Memory OLTP does not consume all the memory from this pool. Esse valor de porcentagem menor depende da Memória confirmada de destino.This value of the smaller percent depends upon the Target committed Memory. A tabela a seguir descreve a memória disponível para o banco de dados de OLTP na memóriaIn-Memory OLTP em um pool de recursos (nomeado ou padrão), antes que um erro de OOM seja gerado.The following table describes memory available to OLTP na memóriaIn-Memory OLTP database in a resource pool (named or default) before an OOM error is raised.

Memória confirmada de destinoTarget Committed Memory Porcentagem disponível para tabelas na memóriaPercent 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%

Por exemplo, se a 'memória confirmada de destino' for 100 GB e você calcular que as tabelas com otimização de memória e os índices precisam de 60 GB de memória, poderá criar um pool de recursos com MAX_MEMORY_PERCENT = 67 (60 GB necessários / 0,90 = 66,667 GB – arredondar para 67 GB, 67 GB / 100 GB instalados = 67%) para garantir que seus objetos OLTP na memóriaIn-Memory OLTP tenham os 60 GB necessários.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 OLTP na memóriaIn-Memory OLTP objects have the 60GB they need.

Quando um banco de dados for associado a um pool de recursos nomeado, use a consulta a seguir para ver as alocações de memória em diferentes pools de recursos.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  

Esta saída de exemplo mostra que a memória usada por objetos com otimização de memória é de 1356 MB no pool de recursos, PoolIMOLTP, com um limite superior de 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. Esse limite superior controla a memória total que pode ser usada pelo usuário e os objetos com otimização de memória do sistema que são mapeados para esse pool.This upper bound controls the total memory that can be taken by user and system memory-optimized objects mapped to this pool.

Saída de exemplo Sample Output
Esta saída é do banco de dados e das tabelas criadas anteriormente.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  

Para obter mais informações, veja sys.dm_resource_governor_resource_pools (Transact-SQL).For more information see sys.dm_resource_governor_resource_pools (Transact-SQL).

Se você não associa o banco de dados a um pool de recursos nomeado, ele é associado ao pool ‘padrão‘.If you do not bind your database to a named resource pool, it is bound to the 'default' pool. Como o pool de recursos padrão é usado pelo SQL ServerSQL Server para a maioria das demais alocações, você não poderá monitorar a memória consumida por tabelas com otimização de memória usando o DMV sys.dm_resource_governor_resource_pools de forma precisa para o banco de dados de interesse.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.

Consulte TambémSee 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)
Administrador de Recursos Resource Governor
Pool de recursos do Administrador de Recursos Resource Governor Resource Pool
Criar um pool de recursos Create a Resource Pool
Alterar configurações do pool de recursos Change Resource Pool Settings
Excluir um pool de recursosDelete a Resource Pool