Risoluzione dei problemi relativi a Resource Governor

Se non è disponibile alcuna funzione di classificazione definita dall'utente, tutte le sessioni verranno assegnate automaticamente al gruppo del carico di lavoro predefinito. Dopo avere creato una funzione di classificazione definita dall'utente, è necessario verificare che sia registrata con Resource Governor e che la configurazione in memoria sia aggiornata.

La creazione, la registrazione e l'abilitazione di una funzione di classificazione definita dall'utente vengono completate in tre passaggi:

• Creazione della funzione.

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• Registrazione della funzione con Resource Governor.

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• Aggiornamento della configurazione in memoria di Resource Governor.

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

La prima operazione da eseguire durante la risoluzione dei problemi relativi alla classificazione consiste nel verificare che la funzione creata sia registrata con Resource Governor e che la configurazione sia stata aggiornata. Per ottenere il nome dello schema (schema_name) e il nome dalla funzione di classificazione (function_name) per la funzione di classificazione definita dall'utente attualmente utilizzata da Resource Governor, utilizzare la query seguente.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

È possibile utilizzare l'approccio precedente per risolvere i problemi relativi a uno scenario in cui è stata modificata la funzione di classificazione definita dall'utente, ma in cui Resource Governor utilizza ancora la logica della funzione precedente per classificare le sessioni. Questo comportamento indica che le modifiche apportate non sono state applicate alla configurazione in memoria.

È possibile creare una funzione di classificazione molto complessa che non produce i risultati previsti o che utilizza un numero elevato di risorse. Se sono state eseguite le operazioni di base per la risoluzione dei problemi, è necessario verificare che la logica della funzione sia valida. Nello scenario peggiore è possibile che un errore presente nel codice provochi un ciclo infinito oppure una query runaway.

Per risolvere i problemi relativi a una funzione di classificazione scritta in modo non corretto, è possibile utilizzare una connessione amministrativa dedicata (DAC, Dedicated Administrator Connection) che non è soggetta alla classificazione e può essere utilizzata mentre Resource Governor è in esecuzione e classifica le sessioni in ingresso. Per ulteriori informazioni, vedere Utilizzo di una connessione amministrativa dedicata.

Per ottenere informazioni sulla funzione di classificazione, eseguire una query sugli elementi seguenti:

• sys.dm_exec_query_stats (contiene informazioni sull'istruzione, ma non la funzione effettiva)

• sys.dm_exec_sql_text (da utilizzare con sql_handle ottenuto da sys.dm_exec_query_stats)

• classe di evento PreConnect:Starting (indica ID e nome della funzione di classificazione)

In questo scenario un utente segnala una riduzione delle prestazioni e si ritiene che il numero di richieste sia limitato.

La prima operazione da eseguire consiste nel verificare se la limitazione del numero di richieste è configurata in modo esplicito per il gruppo cui l'utente appartiene. A tale scopo, verificare a quale gruppo appartiene l'utente per esaminare se l'impostazione GROUP_MAX_REQUESTS è abilitata. Se GROUP_MAX_REQUESTS non è abilitata, non è presente alcuna limitazione del numero di richieste esplicita ed è necessario effettuare le operazioni seguenti per eseguire un'analisi più approfondita.

• Eseguire una query su sys.dm_os_waiting_tasks per verificare se sono presenti richieste in attesa sul tipo RESMGR_THROTTLED. La presenza di questo tipo di attesa indica una limitazione del numero di richieste.

• Avviare Performance Monitor e raccogliere i dati utilizzando i contatori Richieste in coda e Richieste attive. Un valore di Richieste in coda diverso da zero indica la limitazione del numero di richieste.

• Verificare se il valore di Richieste attive corrisponde all'impostazione GROUP_MAX_REQUESTS. Se il valore di Richieste attive è più elevato dell'impostazione GROUP_MAX_REQUESTS, nel gruppo possono essere presenti richieste che non possono essere limitate, ad esempio transazioni aperte.

• Se il valore di Richieste in coda è uguale a zero, verificare il valore di Richieste attive per tutti i gruppi del carico di lavoro che condividono lo stesso pool di risorse poiché nel pool potrebbe essersi verificato un overload a causa di un numero troppo elevato di richieste.

Se si dispone di un criterio basato sulla generazione di eventi di Resource Governor, è possibile utilizzare l'evento generato quando viene raggiunto il limite massimo della CPU.

In questo scenario si desidera determinare se il limite massimo della CPU (REQUEST_MAX_CPU_TIME_SEC) configurato per rilevare una query che utilizza una quantità eccessiva di CPU è troppo basso.

Per convalidare l'impostazione relativa al limite della CPU, effettuare le seguenti operazioni.

• Avviare una sessione di Traccia SQL e raccogliere l'evento CPU Threshold Exceeded. Quando una richiesta dell'utente raggiunge il limite massimo di utilizzo della CPU, il server genera automaticamente un evento di Traccia SQL. Se il valore impostato è troppo basso, verrà generato un numero elevato di tali eventi.

• Avviare Performance Monitor e raccogliere i dati utilizzando il contatore Tempo massimo CPU richieste (ms). È possibile utilizzare il valore di tale contatore come guida per l'impostazione del limite appropriato per il gruppo del carico di lavoro.

In questo scenario si ritiene che la larghezza di banda della CPU sia limitata perché il valore del contatore delle prestazioni Utilizzo CPU (%) è uguale o vicino a quello dell'impostazione MAX_CPU_PERCENT di Resource Governor. Nella query seguente viene restituito il valore dell'utilizzo della CPU espresso in percentuale per tutti i gruppi del carico di lavoro e i pool di risorse per un'istanza di SQL Server.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

Per ulteriori informazioni, vedere sys.dm_os_performance_counters (Transact-SQL).

Per determinare se la larghezza di banda della CPU è limitata, effettuare i controlli seguenti nel sistema.

• Controllare l'utilizzo totale della CPU nel server. Se un carico diverso da quello relativo a SQL Server è attualmente attivo, può influire sulla query di cui è in corso la risoluzione dei problemi.

• Controllare la distribuzione dell'utilizzo della CPU tra i pool di risorse. Un pool di risorse potrebbe essere limitato perché per un altro pool è stato configurato un valore minimo elevato relativo all'utilizzo della CPU. Confrontare i contatori relativi all'utilizzo della CPU previsto (calcolato) rispetto a quello effettivo.

• Controllare i gruppi del carico di lavoro assegnati al pool di risorse specifico. Il carico degli altri gruppi del carico di lavoro può influire sugli utenti che condividono lo stesso pool.

• Controllare la distribuzione dell'utilizzo della CPU tra le utilità di pianificazione. La query che si sta esaminando potrebbe essere posizionata in un'utilità di pianificazione che contiene query con esecuzione prolungata. In questo caso la query può sembrare limitata, mentre il problema effettivo è costituito dalla distribuzione non uniforme del carico tra le utilità di pianificazione.

• Controllare i possibili casi in cui il carico di lavoro viene bloccato da altre sessioni anziché essere limitato dalle impostazioni di Resource Governor.

• Controllare il numero di sessioni che eseguono query attualmente nel sistema. Man mano che il numero di richieste di esecuzione simultanee aumenta, in SQL Server verrà eseguito un tentativo per garantire che tutte le richieste ricevano almeno la stessa quantità di tempo di CPU per impedire l'esaurimento delle risorse della CPU stessa.

In questo scenario si ritiene che l'esecuzione di una query sia lenta a causa delle dimensioni della memoria concessa.

In Resource Governor viene applicato il limite di memoria massimo per le query riducendo la memoria concessa in modo che le query di grandi dimensioni rientrino nel limite. Se una query ottiene una concessione di memoria minore del 100%, potrebbe essere necessario eseguire lo spill dei dati temporanei e scriverli sul disco. Questa operazione può influire in modo significativo sulle prestazioni.

È necessario determinare la percentuale di query di grandi dimensioni per impostare il limite massimo delle dimensioni per la query appropriato. Per determinare le impostazioni migliori, eseguire le operazioni seguenti.

• Eseguire una query su sys.dm_exec_query_memory_grants per verificare lo stato corrente delle concessioni di memoria. La colonna ideal_memory_kb indica la quantità ideale basata sulla cardinalità stimata, mentre la colonna requested_memory_kb indica la quantità richiesta che avrebbe potuto essere ridotta dopo che il limite massimo per la query è stato raggiunto. Se il valore requested_memory_kb è significativamente minore di ideal_memory_kb, lo spill relativo alla query potrebbe essere eseguito di frequente qualora la stima della cardinalità sia corretta.

• Avviare Performance Monitor e raccogliere i dati utilizzando il contatore Concessioni di memoria ridotte/sec. Il valore di tale contatore rappresenta la frequenza del conteggio delle concessioni che hanno ricevuto una quantità di memoria minore rispetto a quella ideale dopo avere raggiunto il limite massimo delle dimensioni per le richieste. Le query di grandi dimensioni possono essere eseguite più lentamente rispetto a quelle con la quantità ideale poiché per tali query è necessario eseguire lo spill su disco affinché rientrino nei limiti di memoria.

Per risolvere un problema di concessione di memoria, può essere necessario aumentare il limite delle dimensioni del pool o il limite massimo delle dimensioni della memoria.

In questo scenario una query ha esito negativo e provoca un errore di timeout delle concessioni di memoria.

Sia il numero complessivo di richieste di concessione di memoria attive che i limiti della memoria specificati nelle definizioni dei gruppi del carico di lavoro e dei pool di risorse possono svolgere un ruolo nei timeout delle concessioni di memoria. Se un unico pool di risorse è condiviso da più gruppi di risorse, anche il numero di query simultanee in altri gruppi può influire sui timeout delle concessioni di memoria.

Per determinare le impostazioni migliori per il pool di risorse, eseguire le operazioni seguenti.

• Eseguire una query su sys.dm_exec_query_memory_grants per verificare il numero di concessioni di memoria e delle query in attesa sul gruppo e sul pool specifico.

• Eseguire una query su sys.dm_exec_query_resource_semaphores per verificare la memoria concessa totale e le destinazioni.

Se l'utilizzo della memoria concessa è maggiore dello spazio di memoria disponibile, è possibile aumentare il limite delle dimensioni del pool di risorse.

Una query ha esito negativo e provoca un errore memoria insufficiente.

Operazioni di base per la risoluzione dei problemi

Operazioni avanzate per la risoluzione dei problemi

In questo scenario si ritiene che l'esecuzione di una query sia lenta a causa di un piano di query non ottimale. Query Optimizer può generare un piano di query non ottimale se la memoria ottenuta non è sufficiente a causa di un'impostazione del limite di memoria su un valore non elevato per un pool di risorse.

Per determinare le impostazioni migliori per il pool di risorse, eseguire le operazioni seguenti.

• Ottenere dati dal contatore Ottimizzazioni query/sec per verificare se nel gruppo del carico di lavoro è presente un numero elevato di compilazioni di query.

• Ottenere dati dal contatore Piani non ottimali/sec per verificare se Query Optimizer produce di frequente piani non ottimali.

Se si verifica una delle condizioni precedenti, aumentare il limite di memoria per il pool di risorse.