Мониторинг и устранение неполадок использования памяти с помощью OLTP в памяти

Применимо к:SQL Server

В памяти OLTP использует память в разных шаблонах, отличных от таблиц на основе дисков. Можно контролировать объем выделенной памяти, используемый оптимизированными для памяти таблицами и индексами в базе данных, с помощью динамических административных представлений (DMV) и счетчиков производительности, предназначенных для подсистемы памяти и сборки мусора. Это обеспечивает видимость на уровне системы и базы данных и позволяет предотвращать проблемы нехватки памяти.

В этой статье описывается мониторинг использования памяти OLTP в памяти для SQL Server.

Примечание.

Это руководство не применяется в Управляемый экземпляр SQL Azure или База данных SQL Azure. Вместо этого ознакомьтесь с демонстрацией OLTP в памяти в SQL Azure:

• Пример в памяти в База данных SQL Azure

Дополнительные сведения о мониторинге использования OLTP в памяти см. в следующем разделе:

• Мониторинг хранилища OLTP в памяти в База данных SQL Azure
• Мониторинг хранилища OLTP в памяти в Управляемый экземпляр SQL Azure

1. Создание примера базы данных с оптимизированными для памяти таблицами

Следующие шаги по созданию базы данных для использования в нашем упражнении.

1. Запустите SQL Server Management Studio.

2. Выберите Создать запрос.

   Примечание.

   Этот следующий шаг можно пропустить, если у вас уже есть база данных с оптимизированными для памяти таблицами.

3. Вставьте этот код в новое окно запроса и выполните каждый раздел, чтобы создать тестовую базу данных для этого упражнения IMOLTP_DB.

   -- create a database to be used  
   CREATE DATABASE IMOLTP_DB  
   GO
   

4. Приведенный ниже пример скрипта используется C:\Data, но экземпляр, скорее всего, использует разные расположения папок для файлов данных базы данных. Обновите следующий скрипт, чтобы использовать правильное расположение для расположения файла в памяти и выполнить его.

   ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA  
   ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_xtp' , FILENAME = 'C:\Data\IMOLTP_DB_xtp') TO FILEGROUP IMOLTP_DB_xtp_fg;  
   GO
   

5. Следующий сценарий создаст три оптимизированные для памяти таблицы, которые можно использовать в оставшейся части этого раздела. В примере мы сопоставили базы данных с пулом ресурсов, что позволит управлять объемом памяти, который может быть занят таблицами, оптимизированными для памяти. Выполните следующий скрипт в IMOLTP_DB базе данных.

   -- create some tables  
   USE IMOLTP_DB  
   GO  
   
   -- create the resoure pool  
   CREATE RESOURCE POOL PoolIMOLTP WITH (MAX_MEMORY_PERCENT = 60);  
   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   GO  
   
   -- bind the database to a resource pool  
   EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'PoolIMOLTP'  
   
   -- you can query the binding using the catalog view as described here  
   SELECT d.database_id  
        , d.name  
        , d.resource_pool_id  
   FROM sys.databases d  
   GO  
   
   -- take database offline/online to finalize the binding to the resource pool  
   USE master  
   GO  
   
   ALTER DATABASE IMOLTP_DB SET OFFLINE  
   GO  
   ALTER DATABASE IMOLTP_DB SET ONLINE  
   GO  
   
   -- create some tables  
   USE IMOLTP_DB  
   GO  
   
   -- create table t1  
   CREATE TABLE dbo.t1 (  
          c1 int NOT NULL CONSTRAINT [pk_t1_c1] PRIMARY KEY NONCLUSTERED  
        , c2 char(40) NOT NULL  
        , c3 char(8000) NOT NULL  
        ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
   GO  
   
   -- load t1 150K rows  
   DECLARE @i int = 0  
   BEGIN TRAN  
   WHILE (@i <= 150000)  
      BEGIN  
         INSERT t1 VALUES (@i, 'a', replicate ('b', 8000))  
         SET @i += 1;  
      END  
   Commit  
   GO  
   
   -- Create another table, t2  
   CREATE TABLE dbo.t2 (  
          c1 int NOT NULL CONSTRAINT [pk_t2_c1] PRIMARY KEY NONCLUSTERED  
        , c2 char(40) NOT NULL  
        , c3 char(8000) NOT NULL  
        ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
   GO  
   
   -- Create another table, t3   
   CREATE TABLE dbo.t3 (  
          c1 int NOT NULL CONSTRAINT [pk_t3_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 1000000)  
        , c2 char(40) NOT NULL  
        , c3 char(8000) NOT NULL  
        ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
   GO  
   

2. Мониторинг использования памяти

Мониторинг использования памяти с помощью SQL Server Management Studio

Так как SQL Server 2014 (12.x), СРЕДА SQL Server Management Studio имеет встроенные стандартные отчеты для мониторинга памяти, потребляемой таблицами в памяти. Эти отчеты можно получить с помощью обозреватель объектов. Обозреватель объектов также можно использовать для контроля памяти, занятой отдельными таблицами, оптимизированными для памяти.

Использование на уровне базы данных

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

1. Запустите SQL Server Management Studio и подключитесь к управляемому экземпляру SQL Server или SQL.

2. В обозреватель объектов щелкните правой кнопкой мыши нужную базу данных.

3. В контекстном меню выберите Отчеты ->Стандартные отчеты ->Использование памяти для оптимизированных объектов памяти

Данный отчет показывает потребление памяти базой данных, созданной ранее.

Мониторинг использования памяти с помощью динамических административных представлений

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

Использование памяти оптимизированными для памяти таблицами и индексами

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

SELECT object_name(object_id) AS [Name]
     , *  
   FROM sys.dm_db_xtp_table_memory_stats;

Образец вывода

Name       object_id   memory_allocated_for_table_kb memory_used_by_table_kb memory_allocated_for_indexes_kb memory_used_by_indexes_kb  
---------- ----------- ----------------------------- ----------------------- ------------------------------- -------------------------  
t3         629577281   0                             0                       128                             0  
t1         565577053   1372928                       1200008                 7872                            1942  
t2         597577167   0                             0                       128                             0  
NULL       -6          0                             0                       2                               2  
NULL       -5          0                             0                       24                              24  
NULL       -4          0                             0                       2                               2  
NULL       -3          0                             0                       2                               2  
NULL       -2          192                           25                      16                              16  

Дополнительные сведения см. в статье sys.dm_db_xtp_table_memory_stats.

Использование памяти внутренними системными структурами

Память также используется системными объектами, например транзакционными структурами, буферами для разностных файлов и данных, структурами для сборки мусора и другими объектами. Можно узнать объем памяти, используемый для этих системных объектов с помощью запроса sys.dm_xtp_system_memory_consumers , как показано ниже.

SELECT memory_consumer_desc  
     , allocated_bytes/1024 AS allocated_bytes_kb  
     , used_bytes/1024 AS used_bytes_kb  
     , allocation_count  
   FROM sys.dm_xtp_system_memory_consumers  

Образец вывода

memory_consumer_ desc allocated_bytes_kb   used_bytes_kb        allocation_count  
------------------------- -------------------- -------------------- ----------------  
VARHEAP                   0                    0                    0  
VARHEAP                   384                  0                    0  
DBG_GC_OUTSTANDING_T      64                   64                   910  
ACTIVE_TX_MAP_LOOKAS      0                    0                    0  
RECOVERY_TABLE_CACHE      0                    0                    0  
RECENTLY_USED_ROWS_L      192                  192                  261  
RANGE_CURSOR_LOOKSID      0                    0                    0  
HASH_CURSOR_LOOKASID      128                  128                  455  
SAVEPOINT_LOOKASIDE       0                    0                    0  
PARTIAL_INSERT_SET_L      192                  192                  351  
CONSTRAINT_SET_LOOKA      192                  192                  646  
SAVEPOINT_SET_LOOKAS      0                    0                    0  
WRITE_SET_LOOKASIDE       192                  192                  183  
SCAN_SET_LOOKASIDE        64                   64                   31  
READ_SET_LOOKASIDE        0                    0                    0  
TRANSACTION_LOOKASID      448                  448                  156  
PGPOOL:256K               768                  768                  3  
PGPOOL: 64K               0                    0                    0  
PGPOOL:  4K               0                    0                    0  

Дополнительные сведения см. в разделе sys.dm_xtp_system_memory_consumers).

Использование памяти во время выполнения при обращении к оптимизированным для памяти таблицам

Следующим запросом можно определить объем памяти, занятый структурами времени выполнения, например для кэша процедур. Выполните этот запрос, чтобы получить объем памяти, используемый структурами времени выполнения, например для кэша процедур. Все структуры времени помечены как XTP.

SELECT memory_object_address  
     , pages_in_bytes  
     , bytes_used  
     , type  
   FROM sys.dm_os_memory_objects WHERE type LIKE '%xtp%'  

Образец вывода

memory_object_address pages_ in_bytes bytes_used type  
--------------------- ------------------- ---------- ----  
0x00000001F1EA8040    507904              NULL       MEMOBJ_XTPDB  
0x00000001F1EAA040    68337664            NULL       MEMOBJ_XTPDB  
0x00000001FD67A040    16384               NULL       MEMOBJ_XTPPROCCACHE  
0x00000001FD68C040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD284040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD302040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD382040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD402040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD482040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD502040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD67E040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001F813C040    8192                NULL       MEMOBJ_XTPBLOCKALLOC  
0x00000001F813E040    16842752            NULL       MEMOBJ_XTPBLOCKALLOC  

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

Память, потребляемая подсистемой OLTP в памяти в экземпляре

Память, выделенная подсистеме OLTP в памяти, и оптимизированные для памяти объекты управляются так же, как и любой другой потребитель памяти в экземпляре SQL Server. Clerks типа MEMORYCLERK_XTP учетных записей для всей памяти, выделенной подсистеме OLTP в памяти. Используйте следующий запрос, чтобы найти всю память, используемую подсистемой OLTP в памяти.

-- This DMV accounts for all memory used by the in-memory engine  
SELECT type  
   , name  
   , memory_node_id  
   , pages_kb/1024 AS pages_MB   
   FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  

В следующем примере выходных данных показано, что выделенная память составляет 18 МБ памяти на уровне системы и 1358 МБ выделено database_id = 5. Поскольку эта база данных сопоставлена с выделенным пулом ресурсов, то эта память учитывается в данном пуле ресурсов.

type                 name       memory_node_id pages_MB  
-------------------- ---------- -------------- --------------------  
MEMORYCLERK_XTP      Default    0              18  
MEMORYCLERK_XTP      DB_ID_5    0              1358  
MEMORYCLERK_XTP      Default    64             0  

Дополнительные сведения см. в статье sys.dm_os_memory_clerks.

3. Управление памятью, потребляемой объектами, оптимизированными для памяти

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

Устранение неполадок с памятью

Диагностика проблем памяти выполняется в три этапа.

1. Определите, сколько памяти используется объектами в вашей базе данных или экземпляре. Можно использовать широкий набор средств наблюдения, доступных для таблиц, оптимизированных для памяти, как описано выше. Например, ознакомьтесь с примерами запросов на динамические административные sys.dm_db_xtp_table_memory_stats административные представления или sys.dm_os_memory_clerks.

2. Определите, как растет потребление памяти и сколько места для маневра вам останется. Периодически наблюдая за использованием памяти, вы узнаете, как растет использование памяти. Например, если база данных сопоставлена с именованным пулом ресурсов, можно понаблюдать за счетчиком использованной памяти (в КБ), чтобы оценить, как растет потребление памяти.

3. Примите меры, чтобы избежать потенциальных проблем с памятью. Дополнительные сведения см. в статье Устранение проблем нехватки памяти.

Связанный контент

• Привязка базы данных с таблицами, оптимизированными для памяти, к пулу ресурсов
• Измените параметры MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула