Controllare le risorse di SQL Server

  • 7 minuti

Anche se alcune istanze gestite di SQL Server o SQL di Azure sono dedicate ai database di una singola applicazione, una configurazione spesso visibile nelle applicazioni cruciali, molti server supportano i database per più applicazioni con requisiti di prestazioni diversi e cicli di carico di lavoro di picco. Il bilanciamento di questi requisiti diversi può essere difficile per gli amministratori. Un modo efficace per gestire le risorse del server consiste nell'usare Resource Governor, introdotto in SQL Server 2008.

Resource Governor è una funzionalità di SQL Server e istanze gestite di SQL di Azure che consentono un controllo granulare sulla CPU, sulle risorse di I/O fisiche e sulla memoria per le richieste di applicazioni in ingresso. Se abilitata a livello di istanza, Resource Governor usa una funzione di classificazione per definire la modalità di trattamento delle connessioni, suddividendo le sessioni in gruppi di carico di lavoro. Ogni gruppo di carico di lavoro è configurato per l'utilizzo di un pool specifico di risorse di sistema.

Pool di risorse

Un pool di risorse rappresenta le risorse fisiche disponibili nel server. SQL Server ha sempre due pool: predefinito e interno, anche quando Resource Governor non è abilitato. Il pool interno è riservato alle funzioni critiche di SQL Server e non può essere limitato. Il pool predefinito, insieme a tutti i pool di risorse definiti in modo esplicito, può essere configurato con limiti sulle risorse che possono usare. Per ogni pool noninternale, è possibile specificare i limiti seguenti:

  • Percentuale di CPU minima/massima
  • Limite massimo della percentuale di CPU
  • Percentuale di memoria minima/massima
  • Affinità del nodo NUMA
  • Numero minimo/massimo di operazioni di I/O

Nota

Le modifiche apportate a un pool di risorse influisce solo sulle nuove sessioni, non quelle già in corso. Pertanto, la modifica di un pool non limita le risorse di un processo a esecuzione prolungata. L'eccezione a questa regola è costituita da pool esterni usati con Machine Learning Services per SQL Server, che possono essere limitati da una modifica del pool anche per le sessioni in corso.

Tutte le impostazioni del pool di risorse, ad eccezione della percentuale minima e massima della CPU, rappresentano limiti rigidi che non possono essere superati. La percentuale di CPU min/max si applica solo quando è presente una contesa della CPU. Ad esempio, se si imposta un massimo di 70%, il carico di lavoro può usare fino a 100% di cicli di CPU disponibili quando non sono presenti conflitti. Tuttavia, se altri carichi di lavoro sono in esecuzione, il carico di lavoro sarà limitato a 70%.

Gruppo del carico di lavoro

Un gruppo di carico di lavoro funge da contenitore per le richieste di sessione, classificate dalla funzione di classificazione. Analogamente ai pool di risorse, sono disponibili due gruppi predefiniti: predefinito e interno. Ogni gruppo di carico di lavoro è associato a un singolo pool di risorse, ma un pool di risorse può ospitare più gruppi di carico di lavoro. Per impostazione predefinita, tutte le connessioni vengono indirizzate al gruppo di carico di lavoro predefinito, a meno che la funzione di classificazione non le assegni a un gruppo definito dall'utente. Il gruppo di carico di lavoro predefinito usa le risorse allocate al pool di risorse predefinito.

Funzione di classificazione

La funzione di classificazione viene eseguita nel momento in cui viene stabilita una connessione all'istanza di SQL Server e classifica ogni connessione in un determinato gruppo del carico di lavoro. Se la funzione restituisce un valore NULL, predefinito o il nome del gruppo di carico di lavoro inesistente, la sessione viene trasferita nel gruppo di carico di lavoro predefinito. Poiché la funzione di classificazione viene eseguita a ogni connessione, è consigliabile verificarne l'efficienza. L'immagine seguente mostra una funzione di classificazione di esempio che classifica gli utenti in base al proprio nome utente.

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')
    SET @WorkloadGroup = 'ReportServerGroup'

ELSE IF (SUSER_NAME() = 'PrimaryUser')
    SET @WorkloadGroup = 'PrimaryServerGroup'
ELSE
    SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup
END

È possibile aumentare la complessità della definizione di funzione illustrata nell'esempio, ma è necessario verificare che la funzione più complessa non influisca sulle prestazioni dell'utente.

Casi d'uso di Resource Governor

Resource Governor viene usato principalmente in scenari multi-tenant in cui un gruppo di database condividono una singola istanza di SQL Server e le prestazioni devono essere mantenute coerenti per tutti gli utenti del server. È anche possibile usare Resource Governor per limitare le risorse usate dalle operazioni di manutenzione, ad esempio le verifiche di coerenza e le ricompilazioni di indici, per tentare di garantire risorse sufficienti per le query utente durante le finestre di manutenzione.