使用記憶體內部 OLTP 監視和疑難解答記憶體使用量

適用於：SQL Server

記憶體內部 OLTP 耗用記憶體的模式與磁碟資料表不同。 您可以使用針對記憶體和記憶體回收子系統提供的 DMV 或效能計數器，監視資料庫中記憶體最佳化資料表和索引所配置和使用的記憶體數量。 如此就能讓您同時深入查看系統和資料庫層級，並且讓您防止因記憶體耗盡而發生問題。

本文涵蓋監視 SQL Server 的記憶體內部 OLTP 記憶體使用量。

注意

本教學課程不適用於 Azure SQL 受控執行個體 或 Azure SQL 資料庫。 相反地，如需 Azure SQL 中記憶體內部 OLTP 的示範，請參閱：

• Azure SQL 資料庫 中的記憶體內部範例

如需監視記憶體內部 OLTP 使用量的詳細資訊，請參閱：

• 在 Azure SQL 資料庫 中監視記憶體內部 OLTP 記憶體
• 在 Azure SQL 受控執行個體 中監視記憶體內部 OLTP 記憶體

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. 在操作功能表中，依序選取 [報表] -> [標準報表] -> [記憶體最佳化物件使用的記憶體]

此報表會顯示上面所建立資料庫的記憶體耗用量。

使用 DMV 監視記憶體使用量

有許多 DMV 可用來監視記憶體最佳化資料表、索引、系統物件及執行階段結構所耗用的記憶體。

記憶體最佳化資料表和索引的記憶體耗用量

您可以藉由查詢 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 執行個體中任何其他記憶體取用的管理方式相同。 MEMORYCLERK_XTP 類型的 Clerk 會考量所有配置給記憶體內部 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 MB 的系統層級記憶體，而配置給 database_id = 5 的記憶體為 1358 MB。 由於此資料庫對應至專用資源集區，因此該記憶體會佔用資源集區。

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. 識別資料庫或執行個體中物件所耗用的記憶體數量。 您可以使用可供記憶體最佳化資料表使用的各種不同監視工具，如前文所述。 例如，請參閱 DMV sys.dm_db_xtp_table_memory_stats 或 sys.dm_os_memory_clerks上的範例查詢。

2. 判斷記憶體耗用量增加的情況，以及您所剩的預留空間。 透過定期監視記憶體耗用量，就可以得知記憶體使用量成長的情況。 例如，如果您已將資料庫對應至具名資源集區，就可以監視效能計數器 Used Memory (KB)，查看記憶體使用量成長的情況。

3. 採取動作消除可能發生的記憶體問題。 如需詳細資訊，請參閱 解決記憶體不足問題。

相關內容

• 資料庫並繫結至資源集區的指引，請參閱
• 變更現有集區上的 MIN_MEMORY_PERCENT 和 MAX_MEMORY_PERCENT