Condividi tramite

  • Articolo

Procedura: Creazione e test di una funzione di classificazione definita dall'utente (Transact-SQL)

In questo argomento viene illustrato come creare e testare una funzione di classificazione definita dall'utente. La procedura comprende l'esecuzione di istruzioni Transact-SQL nell'editor di query di SQL Server Management Studio. Prima di continuare, è consigliabile leggere l'argomento Considerazioni per la scrittura di una funzione di classificazione.

Nell'esempio incluso nella procedura seguente vengono illustrate le diverse opzioni disponibili per la creazione di una funzione di classificazione definita dall'utente piuttosto complessa.

Nell'esempio:

  • Vengono creati un pool di risorse (pProductionProcessing) e un gruppo del carico di lavoro (gProductionProcessing) per l'elaborazione in un ambiente di produzione durante un intervallo di tempo specificato.

  • Vengono creati un pool di risorse (pOffHoursProcessing) e un gruppo del carico di lavoro (gOffHoursProcessing) per la gestione delle connessioni che non soddisfano i requisiti per l'elaborazione in un ambiente di produzione.

  • Viene creata una tabella (TblClassificationTimeTable) nel database master per indicare date e ore di inizio e di fine da valutare rispetto a una data e un'ora di accesso. Tale tabella deve essere creata nel database master perché per le funzioni di classificazione Resource Governor utilizza l'associazione allo schema.

    [!NOTA]

    Come procedura ottimale, è consigliabile non archiviare nel database master tabelle di grandi dimensioni aggiornate di frequente.

Come notato in Considerazioni per la scrittura di una funzione di classificazione, la funzione di classificazione prolunga i tempi di accesso. Una funzione eccessivamente complessa può provocare il timeout degli accessi o rallentare connessioni veloci.

Per creare la funzione di classificazione definita dall'utente

  1. Creare e configurare i nuovi pool di risorse e i nuovi gruppi del carico di lavoro. Assegnare ogni gruppo del carico di lavoro al pool di risorse appropriato.

    --- Create a resource pool for production processing
--- and set limits.
USE master
GO
CREATE RESOURCE POOL pProductionProcessing
WITH
(
     MAX_CPU_PERCENT = 100,
     MIN_CPU_PERCENT = 50
)
GO
--- Create a workload group for production processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gProductionProcessing
WITH
(
     IMPORTANCE = MEDIUM
)
--- Assign the workload group to the production processing
--- resource pool.
USING pProductionProcessing
GO
--- Create a resource pool for off-hours processing
--- and set limits.

CREATE RESOURCE POOL pOffHoursProcessing
WITH
(
     MAX_CPU_PERCENT = 50,
     MIN_CPU_PERCENT = 0
)
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gOffHoursProcessing
WITH
(
     IMPORTANCE = LOW
)
--- Assign the workload group to the off-hours processing
--- resource pool.
USING pOffHoursProcessing
GO

  2. Aggiornare la configurazione in memoria.

    ALTER RESOURCE GOVERNOR RECONFIGURE
GO

  3. Creare una tabella e definire le ore di inizio e di fine per l'intervallo di tempo di elaborazione nell'ambiente di produzione.

    USE master
GO
CREATE TABLE tblClassificationTimeTable
(
     strGroupName     sysname          not null,
     tStartTime       time              not null,
     tEndTime         time              not null
)
GO
--- Add time values that the classifier will use to
--- determine the workload group for a session.
INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM')
go

  4. Creare la funzione di classificazione in modo che utilizzi funzioni e valori di data e ora da valutare rispetto ai valori di data e ora specificati nella tabella di ricerca.

    [!NOTA]

    SQL Server 2008 include un set espanso di tipi di dati e funzioni di data e ora. Per ulteriori informazioni, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).

    CREATE FUNCTION fnTimeClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
     DECLARE @strGroup sysname
     DECLARE @loginTime time
     SET @loginTime = CONVERT(time,GETDATE())
     SELECT TOP 1 @strGroup = strGroupName
          FROM dbo.tblClassificationTimeTable
          WHERE tStartTime <= @loginTime and tEndTime >= @loginTime
     IF(@strGroup is not null)
     BEGIN
          RETURN @strGroup
     END
--- Use the default workload group if there is no match
--- on the lookup.
     RETURN N'gOffHoursProcessing'
END
GO

  5. Registrare la funzione di classificazione e aggiornare la configurazione in memoria.

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Per verificare i pool di risorse, i gruppi del carico di lavoro e la funzione di classificazione definita dall'utente

  1. Ottenere la configurazione dei pool di risorse e dei gruppi del carico di lavoro utilizzando la query seguente.

    USE master
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups
GO

  2. Verificare che la funzione di classificazione sia presente e abilitata utilizzando le query seguenti.

    --- Get the classifier function Id and state (enabled).
SELECT * FROM sys.resource_governor_configuration
GO
--- Get the classifer function name and the name of the schema
--- that it is bound to.
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

  3. Ottenere i dati di runtime correnti per i pool di risorse e i gruppi del carico di lavoro utilizzando la query seguente.

    SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups
GO

  4. Individuare le sessioni incluse in ciascun gruppo utilizzando la query seguente.

    SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
          FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
          ON g.group_id = s.group_id
ORDER BY g.name
GO

  5. Individuare le richieste incluse in ciascun gruppo utilizzando la query seguente.

    SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
           FROM sys.dm_exec_requests r
     INNER JOIN sys.dm_resource_governor_workload_groups g
            ON g.group_id = r.group_id
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO

  6. Individuare le richieste in esecuzione nella funzione di classificazione utilizzando la query seguente.

    SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name 
           FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
           ON g.group_id = s.group_id
                 AND 'preconnect' = s.status
ORDER BY g.name
GO
 
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
           FROM sys.dm_exec_requests r
     INNER JOIN sys.dm_resource_governor_workload_groups g
           ON g.group_id = r.group_id
                 AND 'preconnect' = r.status
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO