Привязка базы данных с таблицами, оптимизированными для памяти, к пулу ресурсов

Статья
05/30/2023

Область применения: SQL Server

Пул ресурсов представляет подмножество физических ресурсов, доступных для управления. По умолчанию базы данных SQL Server привязаны к ресурсам пула ресурсов по умолчанию и используют их. Чтобы защитить SQL Server от использования ресурсов, оптимизированных для одной или нескольких таблиц, оптимизированных для памяти, и для предотвращения использования памяти, необходимо создать отдельный пул ресурсов для управления потреблением памяти для базы данных с оптимизированными для памяти таблицами.

Базу данных можно привязать только к одному пулу ресурсов. Однако можно привязать несколько баз данных к одному и тому же пулу. SQL Server позволяет привязывать базу данных без оптимизированных для памяти таблиц к пулу ресурсов, но она не влияет. Может потребоваться привязка базы данных к указанному пулу ресурсов, если в будущем потребуется создание в базе данных оптимизированных для памяти таблиц.

Прежде чем создавать привязку базы данных к пулу ресурсов, как база данных, так и пул ресурсов должны существовать. Привязка вступит в силу при следующем переводе базы данных в режим «в сети». Дополнительные сведения см. в разделе Состояния базы данных .

Сведения о пулах ресурсов см. в разделе Пул ресурсов регулятора ресурсов.

Шаги для привязки базы данных к пулу ресурсов

Другое содержимое этого раздела

Создайте базу данных и пул ресурсов

Можно создать базу данных и пул ресурсов в любом порядке. Важно, чтобы база данных и пул ресурсов существовали еще до привязки.

Создание базы данных

В следующем примере Transact-SQL создается база данных с именем IMOLTP_DB которая будет содержать одну или несколько таблиц, оптимизированных для памяти. Путь <driveAndPath> должен существовать перед выполнением этой команды.

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

После определения потребностей в памяти для оптимизированных для памяти таблиц нужно определить, какой процент доступной памяти требуется, и задать в процентах это значение или выше.

Пример:
В этом примере предполагается, что согласно вычислениям для оптимизированных для памяти таблиц и индексов требуется 16 ГБ памяти. Предположим, доступно 32 ГБ памяти.

На первый взгляд может показаться, что для MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT нужно задать значение 50 (16 — это 50 % от 32). Но в этом случае оптимизированные для памяти таблицы не получат достаточного объема памяти. В приведенной ниже таблице (Процент доступной памяти для оптимизированных для памяти таблиц и индексов) видно, что при наличии 32 ГБ выделенной памяти только 80 % доступно для оптимизированных для памяти таблиц и индексов. Поэтому следует вычислять минимальный и максимальный процент исходя из доступной памяти, а не общей.

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

Использование реальных чисел:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

Таким образом, необходимо по крайней мере 62,5 % доступной памяти, чтобы уложиться в требование 16 ГБ для оптимизированных для памяти таблиц и индексов. Поскольку значения MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT должны быть целыми числами, рекомендуется задать для них значение не менее 63 %.

Создайте новый пул ресурсов и настройте память.

При настройке памяти для таблиц, оптимизированных для памяти, планирование загрузки следует выполнять на основе MIN_MEMORY_PERCENT, но не MAX_MEMORY_PERCENT. Дополнительные сведения о MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT см. в разделе ALTER RESOURCE POOL (Transact-SQL ). Это повышает прогнозируемую доступность памяти для таблиц, оптимизированных для памяти, так как использование MIN_MEMORY_PERCENT повышает нагрузку на другие пулы ресурсов, чтобы добиться выделения памяти. Чтобы обеспечить доступность памяти и избежать ее нехватки, значения MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT должны быть одинаковыми. В разделе Процент доступной памяти для оптимизированных для памяти таблиц и индексов ниже процент доступной памяти для таблиц, оптимизированных для памяти, основан на объеме выделенной памяти.

Дополнительные сведения см. в разделе Использование In-Memory OLTP в среде ВМ .

Следующий код Transact-SQL создает пул ресурсов с именем Pool_IMOLTP с половиной памяти, доступной для его использования. После создания пула регулятор ресурсов настраивается для включения 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

Привязка базы данных к пулу

Используйте системную функцию sp_xtp_bind_db_resource_pool , чтобы привязать базу данных к пулу ресурсов. Эта функция принимает два параметра: имя базы данных и имя пула ресурсов.

В следующем примере Transact-SQL определяется привязка базы данных IMOLTP_DB к пулу ресурсов Pool_IMOLTP. Привязка не вступит в силу до тех пор, пока база данных не будет переведена в режим запуска.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO

Системная функция sp_xtp_bind_db_resourece_pool принимает два строковых параметра: database_name и pool_name.

Подтвердите привязку

Подтвердите привязку, указав идентификатор пула ресурсов для IMOLTP_DB. Он не должен принимать значение NULL.

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

Сделайте привязку эффективной

Необходимо вывести базу данных из сети, а затем снова вернуть в сеть после ее привязки к пулу ресурсов, чтобы привязка вступила в силу. Если база данных была ранее привязана к другому пулу, то перезапуск базы данных удаляет выделенную память из предыдущего пула ресурсов, после этого память будет выделяться для оптимизированных для памяти таблиц и индексов пула ресурсов, только что привязанного к базе данных.

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

Теперь база данных будет привязана к пулу ресурсов.

Измените параметры MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула

Если на сервере увеличилась дополнительная память либо если изменился объем памяти, необходимый оптимизированным для памяти таблицам, может потребоваться изменить значения MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT. Следующие шаги показывают, как изменить значение MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT в пуле ресурсов. Инструкции по использованию значений для MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT см. ниже. Дополнительные сведения см. в статье Использование In-Memory OLTP в среде ВМ .

Используйте ALTER RESOURCE POOL , чтобы изменить значение как MIN_MEMORY_PERCENT, так и MAX_MEMORY_PERCENT.
Используйте ALTER RESOURCE GOVERNOR , чтобы настроить регулятор ресурсов с новыми значениями.

Образец кода

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

Процент доступной памяти для оптимизированных для памяти таблиц и индексов

При сопоставлении базы данных с оптимизированными для памяти таблицами и рабочей нагрузкой SQL Server с тем же пулом ресурсов регулятор ресурсов задает внутреннее пороговое значение для использования OLTP в памяти, чтобы пользователи пула не имели конфликтов с использованием пула. Как правило, пороговое значение для использования OLTP в памяти составляет около 80 % пула. В следующей таблице показаны фактические пороговые значения для разного размера памяти.

При создании выделенного пула ресурсов для базы данных OLTP в памяти необходимо оценить объем физической памяти, необходимой для таблиц в памяти после учета версий строк и роста данных. Когда требуется оценка памяти, необходимо создать пул ресурсов с частью памяти целевого объема для экземпляра SQL, который показан в столбце committed_target_kb в динамическом административном представлении sys.dm_os_sys_info. Например, можно создать пул ресурсов P1 с 40 % от общего объема памяти, доступного для экземпляра. Из этого 40 %, подсистема OLTP в памяти получает меньший процент для хранения данных OLTP в памяти. Это делается, чтобы убедиться, что OLTP в памяти не потребляет всю память из этого пула. Это значение меньшего процента зависит от целевого объема зафиксированной памяти. В следующей таблице описывается память, доступная для базы данных OLTP в памяти в пуле ресурсов (с именем или по умолчанию) до возникновения ошибки OOM.

Память, выделенная в целевом режиме	Процент доступной памяти для таблиц
<= 8 ГБ	70 %
<= 16 ГБ	75%
<= 32 ГБ	80%
<= 96 ГБ	85 %
>96 ГБ	90 %

Например, если целевой зафиксированный объем памяти составляет 100 ГБ, и вы оцениваете, что оптимизированные для памяти таблицы и индексы нуждаются в 60 ГБ памяти, а затем можно создать пул ресурсов с MAX_MEMORY_PERCENT = 67 (60 ГБ необходимо / 0,90 = 66,667 ГБ - округление до 67 ГБ; 67 ГБ / 100 ГБ установлен = 67 %) для обеспечения того, чтобы объекты OLTP в памяти имели необходимый 60 ГБ.

После привязки базы данных к указанному пулу ресурсов выполните следующий запрос, чтобы узнать объем выделенной памяти в разных пулах ресурсов.

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

Этот пример вывода показывает, что память, занятая оптимизированными для памяти объектами, равна 1356 МБ в пуле ресурсов PoolIMOLTP с верхней границей в 2307 МБ. Эта верхняя граница определяет общий объем памяти, который может быть занят пользовательскими и оптимизированными для памяти объектами, сопоставленными с этим пулом.

Образец вывода
Этот вывод из базы данных и таблиц, созданных ранее.

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         Pool_IMOLTP 0                  100                3845          1356           2307

Дополнительные сведения см. в разделе sys.dm_resource_governor_resource_pools (Transact-SQL).

Если не привязать базу данных к именованному пулу ресурсов, она привязывается к пулу ресурсов default. Так как пул ресурсов по умолчанию используется SQL Server для большинства других выделений, вы не сможете отслеживать объем памяти, потребляемой оптимизированными для памяти таблицами, с помощью sys.dm_resource_governor_resource_pools dmV точно для базы данных, интересующей вас.

См. также

sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)
регулятор ресурсов
Пул ресурсов регулятора ресурсов
Создание пула ресурсов
Изменение параметров пула ресурсов
Удаление пула ресурсов

Поделиться через