Scenari di gestione del carico di lavoro di Resource Governor

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.

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.

1. Creare gruppi per i carichi di lavoro.

2. Creare una funzione di classificazione.

3. Registrare la funzione di classificazione con Resource Governor.

4. Attivare Resource Governor.

5. 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

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

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.

1. Impostare un limite di utilizzo della CPU per il gruppo ad hoc.

2. Monitorare gli eventi di traccia SQL (classe di evento Resource Governor management).

3. 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

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.

1. Creare un nuovo pool di risorse e configurare i limiti della CPU.

2. 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

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.

1. Creare un nuovo pool di risorse e configurare i limiti delle risorse.

2. 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

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.

Eseguire il seguente passaggio per modificare la configurazione di Resource Governor corrente. Viene fornito un esempio di configurazione.

1. 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

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.

1. Creare un nuovo pool di risorse con le impostazioni predefinite.

2. Creare un nuovo gruppo del carico di lavoro in un pool esistente.

3. 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

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.

1. Creare e registrare una nuova funzione di classificazione che classifica le richieste nel gruppo del carico di lavoro rimanente.

2. Eliminare il gruppo del carico di lavoro.

3. Eliminare il pool di risorse.

4. 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