Gestione risorse in pool elastici densi

  • Articolo

Si applica a:Database SQL di Azure

I pool elastici di database SQL di Azure sono una soluzione conveniente per la gestione di molti database con un uso variabile delle risorse. Tutti i database in un pool elastico condividono la stessa allocazione di risorse, ad esempio CPU, memoria, thread di lavoro, spazio di archiviazione tempdb, presupponendo che solo un subset di database nel pool userà le risorse di calcolo in qualsiasi momento. Questo presupposto consente ai pool elastici di essere economicamente convenienti. Invece di pagare per tutte le risorse di cui ogni singolo database potrebbe potenzialmente avere bisogno, i clienti pagano per un set di risorse molto più piccolo, condiviso tra tutti i database nel pool.

Governance delle risorse

La condivisione delle risorse richiede che il sistema controlli attentamente l'utilizzo delle risorse per ridurre al minimo l'effetto "noisy neighbor", in cui un database con un utilizzo elevato delle risorse influisce sugli altri database nello stesso pool elastico. Il database SQL di Azure raggiungere questi obiettivi implementando la governance delle risorse. Allo stesso tempo, il sistema deve fornire risorse sufficienti per il funzionamento affidabile di funzionalità quali la disponibilità elevata e ripristino di emergenza (HADR), il backup e ripristino, il monitoraggio, il Query Store, l'ottimizzazione automatica e così via.

L'obiettivo principale della progettazione di pool elastici è la convenienza. Per questo motivo, il sistema consente volutamente ai clienti di creare pool densi, ovvero pool con un numero di database vicino o pari al massimo consentito, ma con un'allocazione moderata delle risorse di calcolo. Per lo stesso motivo, il sistema non riserva tutte le risorse potenzialmente necessarie per i suoi processi interni, ma consente la condivisione delle risorse tra i processi interni e i carichi di lavoro dell'utente.

Questo approccio consente ai clienti di usare pool elastici densi per ottenere prestazioni adeguate e notevoli risparmi sui costi. Tuttavia, se il carico di lavoro su molti database in un pool denso è sufficientemente intenso, la contesa delle risorse diventa significativa. La contesa delle risorse riduce le prestazioni del carico di lavoro dell'utente e può avere un impatto negativo sui processi interni.

Importante

Nei pool densi con molti database attivi, potrebbe non essere possibile aumentare il numero di database nel pool fino ai massimi documentati per i pool elastici DTU e vCore.

Il numero di database che possono essere inseriti in pool densi senza causare contese di risorse e problemi di prestazioni dipende dal numero di database attivi simultaneamente e dal consumo di risorse da parte dei carichi di lavoro dell'utente in ciascun database. Questo numero può cambiare nel tempo, man mano che cambiano i carichi di lavoro dell'utente.

Inoltre, se l'impostazione minima di vCore o DTU per database è impostata su un valore maggiore di 0, il numero massimo di database nel pool verrà implicitamente limitato. Per ulteriori informazioni, consultare Proprietà del database per i database vCore in pool e Proprietà del database per i database DTU in pool.

Quando si verifica una contesa di risorse in un pool densamente popolato, i clienti possono scegliere una o più delle seguenti azioni per attenuarla:

  • Ottimizzare il carico di lavoro delle query per ridurre il consumo di risorse o distribuirlo tra più database nel tempo.
  • Ridurre la densità del pool spostando alcuni database in un altro pool o rendendoli database indipendenti.
  • Aumentare le prestazioni del pool per ottenere altre risorse.

Per suggerimenti su come implementare le ultime due azioni, vedere le Raccomandazioni operative più avanti in questo articolo. La riduzione della contesa delle risorse offre vantaggi sia per i carichi di lavoro dell'utente che per i processi interni e consente al sistema di mantenere in modo affidabile il livello di servizio previsto.

Monitoraggio dell'utilizzo delle risorse

Per evitare una riduzione del livello delle prestazioni a causa della contesa delle risorse, i clienti che utilizzano pool elastici densi devono monitorare in modo proattivo il consumo delle risorse e intervenire tempestivamente se l'aumento della contesa delle risorse inizia a influire sui carichi di lavoro. Il monitoraggio continuo è importante poiché l'uso delle risorse in un pool varia nel tempo a causa delle modifiche apportate al carico di lavoro dell'utente, alle modifiche nei volumi di dati e nella distribuzione, alle modifiche della densità del pool e alle modifiche apportate al servizio database SQL di Azure.

Il database SQL di Azure fornisce diverse metriche rilevanti per questo tipo di monitoraggio. Il superamento del valore medio consigliato per ogni metrica indica una contesa delle risorse nel pool e dev'essere risolta usando una delle azioni indicate in precedenza.

Per inviare un avviso quando l'utilizzo delle risorse del pool (CPU, I/O di dati, I/O dei log, ruoli di lavoro e così via) supera una soglia, è consigliabile creare avvisi tramite il portale di Azure o il cmdlet PowerShell Add-AzMetricAlertRulev2. Durante il monitoraggio dei pool elastici, è consigliabile creare avvisi anche per singoli database nel pool, se necessario nel caso specifico. Per uno scenario esemplificativo del monitoraggio dei pool elastici, vedere Monitorare e gestire le prestazioni del database SQL di Azure in un'app SaaS multi-tenant.

Nome metrica Descrizione Valore medio consigliato
avg_instance_cpu_percent Utilizzo della CPU da parte del processo SQL associato a un pool elastico, misurato dal sistema operativo sottostante. Disponibile nella vista sys.dm_db_resource_stats in ogni database e nella vista sys.elastic_pool_resource_stats del database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominatasql_instance_cpu_percent e può essere visualizzata nel portale di Azure. Questo valore è lo stesso per tutti i database nello stesso pool elastico. Al di sotto del 70%. Possono essere accettabili brevi picchi occasionali fino al 90%.
max_worker_percent Utilizzo del thread di lavoro. Fornito per ogni database nel pool e per il pool stesso. Esistono limiti diversi per il numero di thread di lavoro a livello di database e a livello di pool, dunque si consiglia di monitorare questa metrica a entrambi i livelli. Disponibile nella vista sys.dm_db_resource_stats in ogni database e nella vista sys.elastic_pool_resource_stats del database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominataworkers_percent e può essere visualizzata nel portale di Azure. Al di sotto dell'80%. I picchi fino al 100% provocheranno l'esito negativo dei tentativi di connessione e delle query.
avg_data_io_percent Utilizzo delle operazioni di I/O al secondo per operazioni fisiche di I/O in lettura e scrittura. Fornito per ogni database nel pool e per il pool stesso. Esistono limiti diversi per il numero di operazioni di I/O al secondo di lavoro a livello di database e a livello di pool, dunque si consiglia di monitorare questa metrica a entrambi i livelli. Disponibile nella vista sys.dm_db_resource_stats in ogni database e nella vista sys.elastic_pool_resource_stats del database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominataphysical_data_read_percent e può essere visualizzata nel portale di Azure. Al di sotto dell'80%. Possono essere accettabili brevi picchi occasionali fino al 100%.
avg_log_write_percent Utilizzo della velocità effettiva per operazioni di I/O in scrittura del log delle transazioni. Fornito per ogni database nel pool e per il pool stesso. Esistono limiti diversi per la velocità effettiva del log a livello di database e a livello di pool, dunque si consiglia di monitorare questa metrica a entrambi i livelli. Disponibile nella vista sys.dm_db_resource_stats in ogni database e nella vista sys.elastic_pool_resource_stats del database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominatalog_write_percent e può essere visualizzata nel portale di Azure. Quando questa metrica è vicina al 100%, tutte le modifiche al database (istruzioni INSERT, UPDATE, DELETE, MERGE, SELECT … INTO, BULK INSERT, ecc.) saranno più lente. Al di sotto del 90%. Possono essere accettabili brevi picchi occasionali fino al 100%.
oom_per_second Frequenza di errori di memoria insufficiente (OOM) in un pool elastico, che è un indicatore di un utilizzo elevato di memoria. Disponibile nella vista sys.dm_resource_governor_resource_pools_history_ex. Vedere gli Esempi per una query esemplificativa per calcolare questa metrica. Per ulteriori informazioni, vedere i limiti delle risorse per i pool elastici che usano DTU o i pool elastici che usano vCore e Risolvere i problemi relativi a errori di memoria insufficiente con il database SQL di Azure. Se si verificano errori di memoria insufficiente, esaminare sys.dm_os_out_of_memory_events. 0
avg_storage_percent Spazio di archiviazione totale usato dai dati in tutti i database all'interno di un pool elastico. Non include lo spazio vuoto nei file di database. Disponibile nella vista sys.elastic_pool_resource_stats nel database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominatastorage_percent e può essere visualizzata nel portale di Azure. Al di sotto dell'80%. Può avvicinarsi al 100% per i pool senza crescita di dati.
avg_allocated_storage_percent Spazio di archiviazione totale usato dai file di database nell'archiviazione di tutti i database all'interno di un pool elastico. Include lo spazio vuoto nei file di database. Disponibile nella vista sys.elastic_pool_resource_stats nel database master. Questa metrica viene generata anche nel Monitoraggio di Azure, dove è denominataallocated_data_storage_percent e può essere visualizzata nel portale di Azure. Al di sotto del 90%. Può avvicinarsi al 100% per i pool senza crescita di dati.
tempdb_log_used_percent Utilizzo dello spazio del log delle transazioni nel database tempdb. Anche se gli oggetti temporanei creati in un database non sono visibili negli altri database dello stesso pool elastico, tempdb è una risorsa condivisa per tutti i database dello stesso pool. Una transazione a esecuzione prolungata o orfana in tempdb avviata da un database del pool può utilizzare un'ampia porzione del log delle transazioni e causare errori per le query in altri database nello stesso pool. Derivato dalle viste sys.dm_db_log_space_usage e sys.database_files. Questa metrica viene generata anche nel Monitoraggio di Azure e può essere visualizzata nel portale di Azure. Vedere gli Esempi per una query esemplificativa per restituire il valore corrente di questa metrica. Al di sotto del 50%. Sono accettabili picchi occasionali fino all'80%.

Oltre a queste metriche, il database SQL di Azure offre una vista che restituisce i limiti effettivi di governance delle risorse, nonché delle viste aggiuntive che restituiscono le statistiche di utilizzo delle risorse a livello di pool di risorse e a livello di gruppo di carico di lavoro.

Nome visualizzazione Descrizione
sys.dm_user_db_resource_governance Restituisce le impostazioni effettive di configurazione e capacità usate dai meccanismi di governance delle risorse nel database corrente o nel pool elastico.
sys.dm_resource_governor_resource_pools Restituisce le informazioni sullo stato del pool di risorse corrente, la configurazione del pool di risorse corrente e le statistiche cumulative del pool di risorse.
sys.dm_resource_governor_workload_groups Restituisce le statistiche cumulative del gruppo di carico di lavoro e la configurazione corrente del gruppo di carico di lavoro. Questa vista può essere unita a sys.dm_resource_governor_resource_pools sulla colonna pool_id per ottenere informazioni sul pool di risorse.
sys.dm_resource_governor_resource_pools_history_ex Restituisce le statistiche di utilizzo del pool di risorse relative alla cronologia recente, in base al numero di snapshot disponibili. Ciascuna riga rappresenta un intervallo di tempo. La durata dell'intervallo è indicata nella colonna duration_ms. Le colonne delta_ restituiscono la variazione di ciascuna statistica durante l'intervallo.
sys.dm_resource_governor_workload_groups_history_ex Restituisce le statistiche di utilizzo del gruppo di carico di lavoro relative alla cronologia recente, in base al numero di snapshot disponibili. Ciascuna riga rappresenta un intervallo di tempo. La durata dell'intervallo è indicata nella colonna duration_ms. Le colonne delta_ restituiscono la variazione di ciascuna statistica durante l'intervallo.

Suggerimento

Per eseguire query su queste e altre viste a gestione dinamica usando un'entità di sicurezza diversa dall'amministratore del server, aggiungere questa entità al ruolo del server##MS_ServerStateReader##.

Queste viste possono essere usate per monitorare l'utilizzo delle risorse e risolvere i problemi relativi alla contesa delle risorse quasi in tempo reale. Il carico di lavoro dell'utente nelle repliche primarie e secondarie leggibili, incluse le repliche geografiche, viene classificato nel pool di risorse SloSharedPool1 e nel gruppo di carico di lavoro UserPrimaryGroup.DBId[N], dove N sta per il valore ID del database.

Oltre a monitorare l'utilizzo corrente delle risorse, i clienti che usano pool densi possono conservare i dati cronologici di utilizzo delle risorse in un archivio dati separato. Questi dati possono essere usati nell'analisi predittiva per gestire in modo proattivo l'utilizzo delle risorse in base alle tendenze cronologiche e stagionali.

Raccomandazioni operative

Lasciare una capacità aggiuntiva sufficiente per le risorse. Se si verifica una contesa delle risorse e una riduzione del livello delle prestazioni, la mitigazione può comportare lo spostamento di alcuni database al di fuori del pool elastico interessato o l'aumento delle prestazioni del pool, come indicato in precedenza. Tuttavia, queste azioni richiedono risorse di calcolo aggiuntive per essere completate. In particolare, per i pool Premium e Business Critical, queste azioni richiedono il trasferimento di tutti i dati dei database da spostare o di tutti i database del pool elastico se vengono aumentate le prestazioni del pool. Il trasferimento dei dati è un'operazione a esecuzione prolungata e che richiede un elevato utilizzo delle risorse. Se il pool è già sottoposto a un uso elevato delle risorse, l'operazione di mitigazione peggiorerà ulteriormente le prestazioni. In casi estremi, potrebbe non essere possibile risolvere la contesa delle risorse tramite lo spostamento del database o l'aumento delle prestazioni del pool poiché le risorse necessarie non sono disponibili. In questo caso, l'unica soluzione potrebbe essere la riduzione temporanea del carico di lavoro delle query nel pool elastico interessato.

I clienti che usano pool densi devono monitorare attentamente le tendenze di utilizzo delle risorse, come descritto in precedenza, ed eseguire azioni di mitigazione mentre le metriche rimangono entro gli intervalli raccomandati e il pool elastico dispone ancora di risorse sufficienti.

L'utilizzo delle risorse dipende da molteplici fattori che cambiano nel tempo per ciascun database e ciascun pool elastico. Il raggiungimento di un rapporto prezzo/prestazioni ottimale in pool densi richiede un monitoraggio e un ribilanciamento continui, ovvero lo spostamento di database da pool più utilizzati a pool meno utilizzati e la creazione di nuovi pool in base alle esigenze per far fronte a un aumento del carico di lavoro.

Nota

Per i pool elastici DTU, la metrica eDTU a livello di pool non è MAX o SUM relativamente all'uso del database singolo, ma deriva dall'uso di varie metriche a livello di pool. I limiti delle risorse a livello di pool possono essere superiori ai singoli limiti a livello di database, pertanto è possibile che un singolo database possa raggiungere un limite di risorse specifico (CPU, I/O dei dati, I/O del log e così via), anche quando la creazione di report eDTU per il pool indica che non è stato raggiunto alcun limite.

Non spostare i database "ad accesso frequente". Se la contesa delle risorse a livello di pool è causata principalmente da un piccolo numero di database molto utilizzati, potrebbe essere opportuno spostare questi database in un pool meno utilizzato o renderli database autonomi. Tuttavia, non è consigliabile eseguire questa operazione mentre un database è ancora molto utilizzato, poiché l'operazione di spostamento peggiorerà ulteriormente le prestazioni, sia per il database in fase di spostamento che per l'intero pool. Attendere invece che l'utilizzo elevato si riduca, oppure spostare i database meno utilizzati per alleggerire l'uso delle risorse a livello di pool. Tuttavia, lo spostamento di database con un utilizzo molto basso non offre alcun vantaggio in questo caso, poiché non riduce materialmente l'utilizzo delle risorse a livello di pool.

Creare nuovi database in un pool di "quarantena". Negli scenari in cui vengono creati frequentemente nuovi database, ad esempio le applicazioni che usano il modello tenant per database, esiste il rischio che un nuovo database inserito in un pool elastico esistente consumi inaspettatamente una quantità significativa di risorse e influisca su altri database e processi interni nel pool. Per ridurre questo rischio, creare un pool separato di "quarantena" con un'ampia allocazione di risorse. Usare questo pool per i nuovi database per i quali non si conoscono ancora i modelli di consumo delle risorse. Una volta che un database è rimasto in questo pool per un ciclo aziendale, ad esempio una settimana o un mese, e il relativo consumo di risorse è ormai noto, può essere spostato in un pool con capacità sufficiente per soddisfare questo utilizzo aggiuntivo delle risorse.

Monitorare sia lo spazio usato che quello allocato. Quando lo spazio allocato nel pool (dimensioni totali di tutti i file di database in memoria per tutti i database di un pool) raggiunge le dimensioni massime del pool, possono verificarsi errori di spazio insufficiente. Se lo spazio allocato ha una tendenza elevata e sta per raggiungere la dimensione massima del pool, le opzioni di mitigazione includono:

  • Spostare alcuni database all'esterno del pool per diminuire lo spazio totale allocato
  • Compattare i file di database per diminuire lo spazio vuoto allocato nei file
  • Aumentare le prestazioni del pool verso un obiettivo di servizio con una maggiore dimensione massima del pool

Se lo spazio del pool usato (dimensioni totali dei dati in tutti i database di un pool, escluso lo spazio vuoto nei file) ha una tendenza elevata e sta per raggiungere la dimensione massima del pool, le opzioni di mitigazione includono:

  • Spostare alcuni database all'esterno del pool per diminuire lo spazio totale utilizzato
  • Spostare (archiviare) i dati all'esterno del database o eliminare i dati non più necessari
  • Implementare la compressione dei dati
  • Aumentare le prestazioni del pool verso un obiettivo di servizio con una maggiore dimensione massima del pool

Evitare server eccessivamente densi. Il database SQL di Azure supporta fino a 5000 database per server. I clienti che usano pool elastici con migliaia di database possono prendere in considerazione la possibilità di collocare più pool elastici su un singolo server, con il numero totale di database fino al limite supportato. Tuttavia, i server con molte migliaia di database presentano problemi operativi. Le operazioni che richiedono l'enumerazione di tutti i database su un server, ad esempio la visualizzazione di database nel portale, saranno più lente. Gli errori operativi, ad esempio la modifica errata degli accessi a livello di server o delle regole del firewall, interesseranno un numero maggiore di database. L'eliminazione accidentale del server richiederà l'assistenza da parte del supporto tecnico Microsoft per ripristinare i database nel server eliminato e causerà un'interruzione prolungata per tutti i database interessati.

Limitare il numero di database per server a un numero inferiore rispetto a quello massimo supportato. In molti scenari, l'utilizzo di un massimo di 1000-2000 database per server è ottimale. Per ridurre la probabilità di eliminazione accidentale di un server, inserire un blocco di eliminazione nel server o nel relativo gruppo di risorse.

Esempi

Visualizzare le impostazioni di capacità dei singoli database

Usare la vista a gestione dinamica sys.dm_user_db_resource_governance per visualizzare le impostazioni di configurazione e capacità effettive usate dalla governance delle risorse nel database corrente o nel pool elastico. Per ulteriori informazioni, vedere sys.dm_user_db_resource_governance.

Eseguire questa query in qualsiasi database di un pool elastico. Tutti i database del pool hanno le stesse impostazioni di governance delle risorse.

SELECT * FROM sys.dm_user_db_resource_governance AS rg
WHERE database_id = DB_ID();

Monitoraggio del consumo di risorse complessivo del pool elastico

Usare la vista del catalogo di sistema sys.elastic_pool_resource_stats per monitorare il consumo di risorse dell'intero pool. Per ulteriori informazioni, vedere sys.elastic_pool_resource_stats.

Questa query esemplificativa per visualizzare gli ultimi 10 minuti dev'essere eseguita nel database master del server logico di Azure SQL contenente il pool elastico desiderato.

SELECT * FROM sys.elastic_pool_resource_stats AS rs
WHERE rs.start_time > DATEADD(mi, -10, SYSUTCDATETIME()) 
AND rs.elastic_pool_name = '<elastic pool name>';

Monitoraggio del consumo di risorse dei singoli database

Usare la vista a gestione dinamica sys.dm_db_resource_stats per monitorare il consumo di risorse dei singoli database. Per ulteriori informazioni, vedere sys.dm_db_resource_stats. È presente una riga per ogni 15 secondi, anche in assenza di attività. I dati cronologici vengono mantenuti per circa un'ora.

Questa query esemplificativa per visualizzare gli ultimi 10 minuti di dati dev'essere eseguita nel database desiderato.

SELECT * FROM sys.dm_db_resource_stats AS rs
WHERE rs.end_time > DATEADD(mi, -10, SYSUTCDATETIME());

Per ottenere un periodo di conservazione più lungo con una minore frequenza, considerare l'utilizzo della query seguente in sys.resource_stats, eseguire nel database master del server logico di Azure SQL. Per ulteriori informazioni, vedere sys.resource_stats (database SQL di Azure). È presente una riga ogni cinque minuti e i dati cronologici vengono conservati per due settimane.

SELECT * FROM sys.resource_stats
WHERE [database_name] = 'sample'
ORDER BY [start_time] desc;

Monitoraggio dell'utilizzo della memoria

Questa query calcola la metrica oom_per_second per ogni pool di risorse per la cronologia recente, in base al numero di snapshot disponibili. Questa query esemplificativa aiuta a identificare il numero medio recente di allocazioni di memoria non riuscite nel pool. Questa query può essere eseguita in qualsiasi database di un pool elastico.

SELECT pool_id,
       name AS resource_pool_name,
       IIF(name LIKE 'SloSharedPool%' OR name LIKE 'UserPool%', 'user', 'system') AS resource_pool_type,
       SUM(CAST(delta_out_of_memory_count AS decimal))/(SUM(duration_ms)/1000.) AS oom_per_second
FROM sys.dm_resource_governor_resource_pools_history_ex
GROUP BY pool_id, name
ORDER BY pool_id;

Monitoraggio dell'utilizzo dello spazio del log tempdb

Questa query restituisce il valore corrente della metrica tempdb_log_used_percent, che mostra l'utilizzo relativo del log delle transazioni tempdb rispetto alle sue dimensioni massime consentite. Questa query può essere eseguita in qualsiasi database di un pool elastico.

SELECT (lsu.used_log_space_in_bytes / df.log_max_size_bytes) * 100 AS tempdb_log_space_used_percent
FROM tempdb.sys.dm_db_log_space_usage AS lsu
CROSS JOIN (
           SELECT SUM(CAST(max_size AS bigint)) * 8 * 1024. AS log_max_size_bytes
           FROM tempdb.sys.database_files
           WHERE type_desc = N'LOG'
           ) AS df
;

Passaggi successivi