Monitorar e solucionar problemas de uso da memóriaMonitor and Troubleshoot Memory Usage

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

OLTP na memória do SQL ServerSQL Server In-Memory OLTP consome memória em padrões diferentes comparado a tabelas baseadas em disco.consumes memory in different patterns than disk-based tables. Você pode monitorar a quantidade de memória alocada e usada pelas tabelas e índices com otimização de memória em seu banco de dados, usando as DMVs ou os contadores de desempenho fornecidos para a memória e o subsistema de coleta de lixo.You can monitor the amount of memory allocated and used by memory-optimized tables and indexes in your database using the DMVs or performance counters provided for memory and the garbage collection subsystem. Isso oferece visibilidade em nível de sistema e banco de dados, e permite que você evite problemas devido ao esgotamento de memória.This gives you visibility at both the system and database level and lets you prevent problems due to memory exhaustion.

Este tópico abrange o monitoramento do uso de memória de OLTP na memóriaIn-Memory OLTP .This topic covers monitoring your OLTP na memóriaIn-Memory OLTP memory usage.

Seções neste tópicoSections in this topic

Criar um banco de dados de exemplo com tabelas com otimização de memóriaCreate a sample database with memory-optimized tables

Você poderá ignorar esta seção se já tiver um banco de dados com tabelas com otimização de memória.You can skip this section if you already have a database with memory-optimized tables.

As etapas a seguir criam um banco de dados com três tabelas com otimização de memória que você pode usar no restante deste tópico.The following steps create a database with three memory-optimized tables that you can use in the remainder of this topic. No exemplo, mapeamos o banco de dados para um pool de recursos a fim de controlar a quantidade de memória que pode ser usada por tabelas com otimização de memória.In the example, we mapped the database to a resource pool so that we can control how much memory can be taken by memory-optimized tables.

  1. Inicie o SQL Server Management StudioSQL Server Management Studio.Launch SQL Server Management StudioSQL Server Management Studio.

  2. Clicar em Nova Consulta.Click New Query.

  3. Colar este código na nova janela de consulta e executar cada seção.Paste this code into the new query window and execute each section.

    -- create a database to be used  
    CREATE DATABASE IMOLTP_DB  
    GO  
    
    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  
    
    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  
    

Monitorando o uso da memóriaMonitoring Memory Usage

Usando o SQL Server Management StudioSQL Server Management StudioUsing SQL Server Management StudioSQL Server Management Studio

SQL Server 2014 (12.x)SQL Server 2014 (12.x) é fornecido com relatórios internos padrão para monitorar a memória consumida por tabelas na memória.ships with built-in standard reports to monitor the memory consumed by in-memory tables. Você pode acessar esses relatórios usando o Pesquisador de Objetos.You can access these reports using Object Explorer. Você também pode usar o Pesquisador de Objetos para monitorar a memória consumida por tabelas individuais com otimização de memória.You can also use the object explorer to monitor memory consumed by individual memory-optimized tables.

Consumo em nível de banco de dadosConsumption at the database level

Você pode monitorar o uso da memória em nível de banco de dados da forma a seguir.You can monitor memory use at the database level as follows.

  1. Inicie o SQL Server Management StudioSQL Server Management Studio e conecte-se a um servidor.Launch SQL Server Management StudioSQL Server Management Studio and connect to a server.

  2. No Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados sobre o qual você deseja obter relatórios.In Object Explorer, right-click the database you want reports on.

  3. No menu de contexto, selecione Relatórios -> Relatórios Padrão -> Uso de Memória por Objetos com Otimização de MemóriaIn the context menu select, Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects

HK_MM_SSMSHK_MM_SSMS

Esse relatório mostra o consumo de memória pelo banco de dados criado anteriormente.This report shows memory consumption by the database we created above.

HK_MM_SSMSHK_MM_SSMS

Usando DMVsUsing DMVs

Há várias DMVs disponíveis para monitorar a memória consumida por tabelas com otimização de memória, índices, objetos de sistema e estruturas de tempo de execução.There are a number of DMVs available to monitor memory consumed by memory-optimized tables, indexes, system objects, and by run-time structures.

Consumo de memória por tabelas com otimização de memória e índicesMemory consumption by memory-optimized tables and indexes

Você pode localizar o consumo de memória para todas as tabelas de usuário, índices e objetos do sistema consultando sys.dm_db_xtp_table_memory_stats , conforme mostrado aqui.You can find memory consumption for all user tables, indexes, and system objects by querying sys.dm_db_xtp_table_memory_stats as shown here.

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

Saída de exemploSample Output

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  

Para obter mais informações, veja sys.dm_db_xtp_table_memory_stats.For more information see sys.dm_db_xtp_table_memory_stats.

Consumo de memória por estruturas internas do sistemaMemory consumption by internal system structures

A memória também é consumida por objetos do sistema, tais como estruturas transacionais, buffers para arquivos de dados e delta, estruturas de coleta de lixo, entre outros.Memory is also consumed by system objects, such as, transactional structures, buffers for data and delta files, garbage collection structures, and more. Você pode localizar a memória usada para esses objetos de sistema consultando sys.dm_xtp_system_memory_consumers , conforme mostrado aqui.You can find the memory used for these system objects by querying sys.dm_xtp_system_memory_consumers as shown here.

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  

Saída de exemploSample Output

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  

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

Consumo de memória em tempo de execução ao acessar tabelas com otimização de memóriaMemory consumption at run-time when accessing memory-optimized tables

Você pode determinar a memória consumida por estruturas em tempo de execução; por exemplo, o cache de procedimento com a seguinte consulta: execute esta consulta para obter a memória usada por estruturas em tempo de execução, como o cache de procedimento.You can determine the memory consumed by run time structures, such as the procedure cache with the following query: run this query to get the memory used by run-time structures such as for the procedure cache. Todas as estruturas em tempo de execução são marcadas com XTP.All run-time structures are tagged with XTP.

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

Saída de exemploSample Output

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  

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

Memória consumida pelo mecanismo OLTP na memóriaIn-Memory OLTP na instânciaMemory consumed by OLTP na memóriaIn-Memory OLTP engine across the instance

A memória alocada para o mecanismo OLTP na memóriaIn-Memory OLTP e os objetos com otimização de memória são gerenciados da mesma maneira que qualquer outro consumidor de memória em uma instância do SQL ServerSQL Server .Memory allocated to the OLTP na memóriaIn-Memory OLTP engine and the memory-optimized objects is managed the same way as any other memory consumer within a SQL ServerSQL Server instance. Os administradores do tipo MEMORYCLERK_XTP respondem por toda a memória alocada para o mecanismo OLTP na memóriaIn-Memory OLTP .The clerks of type MEMORYCLERK_XTP accounts for all the memory allocated to OLTP na memóriaIn-Memory OLTP engine. Use a consulta a seguir para localizar toda a memória usada pelo mecanismo OLTP na memóriaIn-Memory OLTP .Use the following query to find all the memory used by the OLTP na memóriaIn-Memory OLTP engine.

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

A saída de exemplo mostra que a memória total alocada equivale a 18MB de consumo de memória em nível de sistema e a 1358MB alocados para a ID de banco de dados de valor 5.The sample output shows that the total memory allocated is 18 MB system-level memory consumption and 1358MB allocated to database id of 5. Como esse banco de dados é mapeado para um pool de recursos dedicados, essa memória é contabilizada nesse pool de recursos.Since this database is mapped to a dedicated resource pool, this memory is accounted for in that resource pool.

Saída de exemploSample Output

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

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

Gerenciando a memória consumida por objetos com otimização de memóriaManaging memory consumed by memory-optimized objects

É possível controlar a memória total consumida por tabelas com otimização de memória, associando-a a um grupo de recursos nomeado, conforme descrito no tópico Associar um banco de dados com tabelas com otimização de memória a um pool de recursos.You can control the total memory consumed by memory-optimized tables by binding it to a named resource pool as described in the topic Bind a Database with Memory-Optimized Tables to a Resource Pool.

Solucionando problemas de memóriaTroubleshooting Memory Issues

A solução de problemas de memória é um processo de três etapas:Troubleshooting memory issues is a three step process:

  1. Identificar a quantidade de memória que está sendo consumido pelos objetos no banco de dados ou instância.Identify how much memory is being consumed by the objects in your database or instance. Você pode usar um excelente conjunto de ferramentas de monitoramento disponíveis para tabelas com otimização de memória, conforme descrito anteriormente.You can use a rich set of monitoring tools available for memory-optimized tables as described earlier. Por exemplo, os DMVs sys.dm_db_xtp_table_memory_stats ou sys.dm_os_memory_clerks.For example the DMVs sys.dm_db_xtp_table_memory_stats or sys.dm_os_memory_clerks.

  2. Determine como o consumo de memória aumenta e o espaço disponível para você.Determine how memory consumption is growing and how much head room you have left. Ao monitorar o consumo de memória periodicamente, você pode saber como o uso da memória está aumentando.By monitoring the memory consumption periodically, you can know how the memory use is growing. Por exemplo, se você mapeou o banco de dados para um pool de recursos nomeado, poderá monitorar o contador de desempenho Memória Usada (KB) para ver o aumento no uso de memória.For example, if you have mapped the database to a named resource pool, you can monitor the performance counter Used Memory (KB) to see how memory usage is growing.

  3. Execute uma ação para reduzir os problemas potenciais de memória.Take action to mitigate the potential memory issues. Para obter mais informações, veja Resolver problemas de memória insuficiente.For more information see Resolve Out Of Memory Issues.

Consulte TambémSee Also

Associar um banco de dados com tabelas com otimização de memória a um pool de recursos Bind a Database with Memory-Optimized Tables to a 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