Associare un database con tabelle con ottimizzazione per la memoria a un pool di risorse

Articolo
06/13/2017

Un pool di risorse rappresenta un subset di risorse fisiche che è possibile governare. Per impostazione predefinita, i database di SQL Server vengono associati e utilizzano le risorse del pool di risorse predefinito. Per proteggere SQL Server dalla presenza delle risorse utilizzate da una o più tabelle ottimizzate per la memoria e per impedire ad altri utenti della memoria di utilizzare la memoria necessaria per le tabelle ottimizzate per la memoria, è necessario creare un pool di risorse separato per gestire l'utilizzo della memoria per il database con tabelle ottimizzate per la memoria.

Un database può essere associato a un solo pool di risorse. Tuttavia, è possibile associare più database allo stesso pool. SQL Server consente di associare un database senza tabelle ottimizzate per la memoria a un pool di risorse, ma questa operazione non ha alcun effetto. Può essere opportuno associare un database a un pool di risorse denominato se, in futuro, si intende creare tabelle ottimizzate per la memoria nel database.

Prima di poter associare un database a un pool di risorse, è necessario che sia presente sia il database che il pool di risorse. L'associazione viene applicata alla successiva connessione del database. Per altre informazioni, vedere Stati del database .

Per informazioni sui pool di risorse, vedere Pool di risorse di Resource Governor.

Creare il database e il pool di risorse

È possibile creare il database e il pool di risorse in qualsiasi ordine. La cosa importante è che entrambi esistano prima di associare il database al pool di risorse.

Creare il database

Il codice Transact-SQL seguente crea un database denominato IMOLTP_DB che conterrà una o più tabelle ottimizzate per la memoria. L'unità pathAndPath <> deve esistere prima di eseguire questo comando.

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

Determinare il valore minimo per MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT.

Dopo aver determinato la memoria necessaria per le tabelle ottimizzate per la memoria, è necessario determinare la percentuale di memoria disponibile necessaria e impostare le percentuali di memoria su un valore uguale o superiore.

Esempio:
In questo esempio si suppone che sia stato calcolato che gli indici e le tabelle ottimizzate per la memoria richiedano 16 GB di memoria. Si suppone inoltre che siano stati riservati 32 GB di memoria per l'utilizzo da parte dell'utente.

A prima vista, si potrebbe ritenere corretto impostare MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT su 50 (16 è il 50% di 32). Tuttavia, questo valore non garantirebbe memoria sufficiente alle tabelle ottimizzate per la memoria. Nella tabella seguente (la sezione relativa alla percentuale di memoria disponibile per indici e tabelle ottimizzate per la memoria) è possibile notare che se si riservano 32 GB di memoria, solo l'80% di tale valore sarà disponibile per gli indici e le tabelle ottimizzate per la memoria. Pertanto, le percentuali minima e massima sono calcolate in base alla memoria disponibile, non alla memoria riservata.

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

Inserimento di numeri reali:
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

È pertanto necessario almeno il 62,5% della memoria disponibile per soddisfare il requisito di 16 GB degli indici e delle tabelle ottimizzate per la memoria. Poiché i valori di MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT devono essere numeri interi, viene impostato un valore pari almeno al 63%.

Creare un pool di risorse e configurare la memoria

Quando si configura la memoria per le tabelle ottimizzate per la memoria, la pianificazione della capacità deve essere eseguita in base a MIN_MEMORY_PERCENT, non MAX_MEMORY_PERCENT. Per informazioni su MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT, vedere ALTER RESOURCE POOL (Transact-SQL ). Ciò rende maggiormente stimabile la disponibilità di memoria per le tabelle ottimizzate per la memoria, poiché MIN_MEMORY_PERCENT causa un utilizzo elevato di memoria per gli altri pool di risorse, al fine di garantire la disponibilità. Per garantire che la memoria sia disponibile ed evitare condizioni di memoria insufficiente, i valori di MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT devono essere uguali. Vedere la sezione relativa alla percentuale di memoria disponibile per indici e tabelle ottimizzate per la memoria per i valori in base alla quantità di memoria riservata.

Per altre informazioni sull'uso di un ambiente di VM, vedere Procedure consigliate: Uso di OLTP in memoria in un ambiente di VM.

Il codice Transact-SQL seguente crea un pool di risorse denominato Pool_IMOLTP con metà della memoria disponibile per l'uso. Dopo la creazione del pool, Resource Governor viene riconfigurato in modo da includere 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

Associare il database al pool.

Usare la funzione di sistema sp_xtp_bind_db_resource_pool per associare il database al pool di risorse. La funzione accetta due parametri: il nome del database e il nome del pool di risorse.

Il codice Transact-SQL seguente definisce un'associazione del database IMOLTP_DB al pool di risorse Pool_IMOLTP. L'associazione non diventa effettiva finché il database non viene portato online.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO

La funzione di sistema sp_xtp_bind_db_resourece_pool accetta due parametri di stringa: database_name e pool_name.

Verificare l'associazione

Verificare l'associazione, annotando l'ID del pool di risorse per IMOLTP_DB. Non deve essere NULL.

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

Rendere effettiva l'associazione

Dopo aver associato il database al pool di risorse, è necessario portare il database offline, quindi di nuovo online per rendere effettiva l'associazione. Se in precedenza il database era stato associato a un pool diverso, tramite questa operazione la memoria allocata verrà rimossa dal pool di risorse precedente e verranno usate le allocazioni di memoria per gli indici e la tabella ottimizzata per la memoria provenienti dal pool di risorse appena associato al database.

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

Il database è ora associato al pool di risorse.

Modificare MIN MEMORY PERCENT e MAX MEMORY PERCENT in un pool esistente

Se si aggiunge altra memoria al server o se cambia la quantità di memoria necessaria per le tabelle ottimizzate per la memoria, può essere necessario modificare il valore di MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT. Nei passaggi seguenti viene illustrato come modificare il valore di MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT in un pool di risorse. Per informazioni sui valori da usare per MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT, vedere la sezione seguente. Per altre informazioni, vedere l'argomento Procedure consigliate: Uso di OLTP in memoria in un ambiente di VM.

Usare ALTER RESOURCE POOL per modificare il valore di MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT.
Usare ALTER RESURCE GOVERNOR per riconfigurare Resource Governor con i nuovi valori.

Codice di esempio

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

Percentuale di memoria disponibile per indici e tabelle ottimizzate per la memoria

Se si esegue il mapping di un database con tabelle ottimizzate per la memoria e un carico di lavoro SQL Server allo stesso pool di risorse, il Resource Governor imposta una soglia interna per In-Memory OLTP in modo che gli utenti del pool non abbiano conflitti nell'utilizzo del pool. In generale, la soglia per In-Memory uso OLTP è circa l'80% del pool. Nella tabella seguente vengono illustrate le soglie effettive per varie dimensioni di memoria.

Quando si crea un pool di risorse dedicato per il database OLTP In-Memory, è necessario stimare la quantità di memoria fisica necessaria per le tabelle in memoria dopo aver contabile le versioni di riga e la crescita dei dati. Dopo avere stimato la memoria necessaria, è possibile creare un pool di risorse con una percentuale della memoria di destinazione di commit per l'istanza di SQL come indicato nella colonna 'committed_target_kb' nella DMV sys.dm_os_sys_info (vedere sys.dm_os_sys_information). Ad esempio, è possibile creare un pool di risorse P1 con il 40% della memoria totale disponibile per l'istanza. Al di fuori di questo 40%, il motore OLTP In-Memory ottiene una percentuale inferiore per archiviare In-Memory dati OLTP. Questa operazione viene eseguita per assicurarsi che In-Memory OLTP non utilizza tutta la memoria da questo pool. Il valore della percentuale inferiore dipende dalla memoria riservata alla destinazione. Nella tabella seguente viene descritta la memoria disponibile per In-Memory database OLTP in un pool di risorse (denominato o predefinito) prima che venga generato un errore OOM.

Memoria riservata di destinazione	Percentuale disponibile per le tabelle in memoria
<= 8 GB	70%
<= 16 GB	75%
<= 32 GB	80%
<= 96 GB	85%
>96 GB	90%

Ad esempio, se la "memoria di cui è stato eseguito il commit di destinazione" è 100 GB, e si stima che le tabelle e gli indici ottimizzati per la memoria richiedano 60GBof memoria, quindi è possibile creare un pool di risorse con MAX_MEMORY_PERCENT = 67 (60 GB necessari / 0,90 = 66,667 GB - arrotondare fino a 67 GB; 67 GB / 100 GB installati = 67%) per assicurarsi che gli oggetti OLTP In-Memory abbiano i 60 GB necessari.

Dopo l'associazione di un database a un pool di risorse denominato, usare la query seguente per visualizzare le allocazioni di memoria tra pool di risorse diversi.

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

In questo esempio campione viene illustrato che la memoria usata dagli oggetti ottimizzati per la memoria è 1356 MB nel pool di risorse, PoolIMOLTP, con un limite superiore di 2307 MB. Il limite superiore controlla la memoria totale che può essere usata dagli oggetti ottimizzati per la memoria di utente e sistema dei quali è stato eseguito il mapping a questo pool.

Output di esempio
Questo output è tratto dal database e dalle tabelle create in precedenza.

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

Per altre informazioni, vedere sys.dm_resource_governor_resource_pools (Transact-SQL).

Se il database non viene associato a un pool di risorse denominato, viene associato al pool predefinito ('default'). Poiché il pool di risorse predefinito è utilizzato da SQL Server per la maggior parte delle altre allocazioni, non sarà possibile monitorare in modo accurato la memoria utilizzata dalle tabelle ottimizzate per la memoria tramite la DMV sys.dm_resource_governor_resource_pools per il database di interesse.

Vedere anche

sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)
Resource Governor
Pool di risorse di Resource Governor
Creare un pool di risorse
Modificare le impostazioni del pool di risorse
Eliminare un pool di risorse

Condividi tramite