Scenari di gestione del carico di lavoro di Resource Governor
È possibile utilizzare Resource Governor in varie modalità per monitorare e gestire i carichi di lavoro nel sistema SQL Server. In questo argomento viene fornita una panoramica di configurazione di Resource Governor e viene illustrato l'utilizzo di Resource Governor. Gli scenari forniti includono esempi di codici di Transact-SQL per la creazione e la modifica dei gruppi del carico di lavoro e dei pool di risorse.
Configurazione di Resource Governor
Una volta installato SQL Server 2008, Resource Governor è disponibile per l'uso ma non è attivato. I gruppi del carico di lavoro interni e predefiniti e i relativi pool di risorse sono presenti.
Per creare e utilizzare i propri pool di risorse e gruppi del carico di lavoro, è necessario completare i seguenti passaggi:
Creare un pool di risorse con i limiti specificati dall'utente.
Creare un gruppo del carico di lavoro con i limiti e i criteri specificati dall'utente e identificare il pool di risorse al quale appartiene il gruppo del carico di lavoro.
Definire e attivare una funzione di classificazione da utilizzare per le richieste in arrivo.
Una volta completati i passaggi precedenti, è possibile vedere la configurazione di Resource Governor attiva e lo stato di tutte le richieste attive classificate.
Determinazione dei requisiti del pool di risorse e del gruppo del carico di lavoro
Il compito è determinare la configurazione del gruppo del carico di lavoro e del pool di risorse da utilizzare per l'ambiente SQL Server. Per effettuare questa operazione, è necessario conoscere come le risorse di SQL Server sono attualmente utilizzate da un'applicazione.
La soluzione consiste nel creare un gruppo del carico di lavoro nel pool di risorse predefinito. Eseguire l'applicazione per molti giorni e utilizzare le informazioni raccolte per rispondere alle seguenti domande.
Qual è il requisito della CPU?
Determinare una stima di larghezza di banda della CPU moltiplicando l'utilizzo totale medio della CPU per richiesta per il numero medio di richieste al secondo.È possibile utilizzare la deviazione massima, media e standard di utilizzo della CPU per determinare se è necessario un limite massimo della CPU.
Qual è il numero di richieste concorrenti?
Utilizzare il numero massimo e medio di statistiche di richieste concorrenti per determinare il fattore di concorrenza minimo.A quanto ammonta la memoria totale necessaria?
Utilizzare la memoria totale massima e media utilizzata per determinare il requisito di memoria.Quanta memoria è necessaria per una singola query?
Utilizzare la memoria massima e media per statistica di query per determinare la quantità di memoria necessaria.Sono presenti query non in esecuzione a causa della mancanza di memoria?
Utilizzare il tempo medio di attesa nella statistica della coda di memoria per determinare se sono presenti query bloccate a causa della disponibilità di memoria.
Scenari
Vengono forniti sette scenari come esempio. Gli scenari da 1 a 5 sono cumulativi. Ciascuno di essi si basa sullo scenario precedente. Lo scenario 6 non è correlato agli scenari precedenti, e lo scenario 7 si basa sullo scenario 6.
Scenario 1
D: Ho appena installato una nuova versione di SQL Server vorrei utilizzare Resource Governor. Come posso utilizzarlo nel mio ambiente?
R: Utilizzare Resource Governor per il monitoraggio dell'utilizzo delle risorse in base ai carichi di lavoro. Eseguire la procedura seguente per configurare un ambiente Resource Governor. Viene fornito un esempio di configurazione.
Creare gruppi per i carichi di lavoro.
Creare una funzione di classificazione.
Registrare la funzione di classificazione con Resource Governor.
Attivare Resource Governor.
Monitorare i contatori delle prestazioni di Resource Governor ed eseguire una query alle viste a gestione dinamica che restituiranno le informazioni relative all'utilizzo delle risorse di un gruppo del carico di lavoro.
Esempio
[!NOTA]
Nella configurazione seguente non viene specificato alcun pool di risorse utilizzato dal gruppo del carico di lavoro. Per impostazione predefinita, i gruppi del carico di lavoro utilizzano il pool predefinito.
BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs.
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF (SUSER_NAME() = 'sa')
SET @grp_name = 'GroupAdmin'
IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
OR (APP_NAME() LIKE '%QUERY ANALYZER%')
SET @grp_name = 'GroupAdhoc'
IF (APP_NAME() LIKE '%REPORT SERVER%')
SET @grp_name = 'GroupReports'
RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 2
D: In base ai risultati del monitoraggio dello scenario 1, vorrei visualizzare un evento ogni volta che una query nel gruppo ad hoc (GroupAdhoc) viene eseguita per più di 30 secondi.
R: Eseguire la procedura seguente per modificare la configurazione di Resource Governor corrente. Viene fornito un esempio di configurazione.
Impostare un limite di utilizzo della CPU per il gruppo ad hoc.
Monitorare gli eventi di traccia SQL (classe di evento Resource Governor management).
Eseguire un'azione sull'evento. Ad esempio, ignorare l'evento, inviare un messaggio di posta elettronica, inviare una pagina o eseguire il comando KILL sulla richiesta.
Esempio
-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 3
D: Come è possibile restringere ulteriormente il gruppo ad hoc in modo che non superi il 50% di utilizzo della CPU quando vengono cumulate tutte le richieste?
R: Poiché negli scenari precedenti viene utilizzato il pool predefinito, è necessario creare un nuovo pool di risorse. Eseguire la seguente procedura per modificare la configurazione di Resource Governor corrente. Viene fornito un esempio di configurazione.
Creare un nuovo pool di risorse e configurare i limiti della CPU.
Configurare il gruppo del carico di lavoro ad hoc per l'utilizzo del nuovo pool di risorse.
Esempio
BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool.
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 4
A: Vorrei garantire che il gruppo degli amministratori disponga sempre di risorse per l'esecuzione di query di diagnostica, ma tali query non devono utilizzare più del 10 per cento delle risorse di memoria del server.
R: A questo scopo, è necessario creare un nuovo pool di risorse. Eseguire la seguente procedura per modificare la configurazione di Resource Governor corrente. Viene fornito un esempio di configurazione.
Creare un nuovo pool di risorse e configurare i limiti delle risorse.
Configurare il gruppo del carico di lavoro dell'amministratore per l'utilizzo del nuovo pool di risorse.
Esempio
BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
MIN_CPU_PERCENT = 10,
MIN_MEMORY_PERCENT = 10,
MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 5
D: I report si trovano attualmente nel pool di risorse predefinito, ma utilizzano un gruppo del carico di lavoro distinto (GroupReports). In questo caso, come è possibile garantire che il contenuto del gruppo di risorse predefinito sia associato a una priorità di utilizzo delle risorse inferiore rispetto ai report?
R: Per ottenere questo risultato, è necessario configurare l'impostazione IMPORTANCE nel gruppo predefinito.
[!NOTA]
È possibile modificare le impostazioni per il gruppo predefinito, ma non per il pool predefinito. Se si ritiene che sia necessaria una modifica del pool predefinito, ciò indica che è necessario creare un nuovo pool di risorse.
Eseguire il seguente passaggio per modificare la configurazione di Resource Governor corrente. Viene fornito un esempio di configurazione.
- Modificare l'impostazione del gruppo predefinito.
Esempio
-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default]
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 6
D: Vorrei creare un nuovo gruppo del carico di lavoro che utilizza un pool di risorse esistente. Quindi, vorrei modificare la funzione di classificazione in modo da spostare il gruppo in un nuovo pool di risorse.
R: Utilizzare i seguenti passaggi per configurare un nuovo ambiente di Resource Governor. Viene fornito un esempio di configurazione.
Creare un nuovo pool di risorse con le impostazioni predefinite.
Creare un nuovo gruppo del carico di lavoro in un pool esistente.
Creare e registrare una nuova funzione di classificazione per la gestione delle richieste.
Esempio
BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 ()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF SUSER_SNAME() = 'DOMAIN\username'
SET @grp_name = 'MyNewGroup'
ELSE
SET @grp_name = 'MyGroup'
RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Scenario 7
D: Ho deciso che un gruppo del carico di lavoro (creato nello scenario 6) non è più necessario, pertanto desidero eliminarlo insieme al pool di risorse che lo contiene.
R: Utilizzare i seguenti passaggi per modificare un ambiente di Resource Governor. Viene fornito un esempio di configurazione.
Creare e registrare una nuova funzione di classificazione che classifica le richieste nel gruppo del carico di lavoro rimanente.
Eliminare il gruppo del carico di lavoro.
Eliminare il pool di risorse.
Applicare le modifiche alla configurazione.
Esempio
BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 ()
RETURNS sysnameE
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF suser_sname() = 'DOMAIN\username'
SET @grp_name = 'MyGroup'
RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Vedere anche