Creare e testare una funzione di classificazione User-Defined

Questo argomento illustra come creare e testare una funzione utente di classificazione. I passaggi implicano l'esecuzione di istruzioni Transact-SQL nell'editor di query di SQL Server Management Studio.

L'esempio illustrato nella procedura seguente illustra le possibilità di creazione di una funzione definita dall'utente di classificatore abbastanza complessa.

In questo esempio:

Un pool di risorse (pProductionProcessing) e un gruppo di carico di lavoro (gProductionProcessing) vengono creati per l'elaborazione di produzione durante un intervallo di tempo specificato.
Vengono creati un pool di risorse (pOffHoursProcessing) e un gruppo di carico di lavoro (gOffHoursProcessing) per la gestione delle connessioni che non soddisfano i requisiti per l'elaborazione di produzione.
Una tabella (TblClassificationTimeTable) viene creata nel master per contenere l'ora di inizio e di fine che può essere valutata rispetto a un'ora di accesso. Questa operazione deve essere creata nel master perché Resource Governor usa l'associazione dello schema per le funzioni di classificazione.

Annotazioni

Come procedura consigliata, non è consigliabile archiviare tabelle aggiornate di grandi dimensioni nel master.

La funzione di classificazione estende i tempi di accesso. Una funzione eccessivamente complessa può causare il timeout degli accessi o rallentare le connessioni veloci.

Per creare la funzione personalizzata per il classificatore

Creare e configurare i nuovi pool di risorse e i gruppi di carico di lavoro. Assegnare ogni gruppo di 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

Aggiornare la configurazione in memoria.

ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Creare una tabella e definire l'ora di inizio e di fine per l'intervallo di tempo di elaborazione 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

Creare la funzione di classificazione che usa funzioni temporali e valori che possono essere valutati rispetto ai tempi nella tabella di ricerca. Per informazioni sull'uso delle tabelle di ricerca in una funzione di classificazione, vedere "Procedure consigliate per l'uso di tabelle di ricerca in una funzione di classificazione" in questo argomento.

Annotazioni

SQL Server 2008 ha introdotto un set esteso di funzioni e tipi di dati di data e ora. Per altre 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

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 di carico di lavoro e la funzione definita dall'utente del classificatore

Ottenere la configurazione del pool di risorse e del gruppo di carico di lavoro usando la query seguente.

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

Verificare che la funzione di classificazione esista e sia abilitata usando 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

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

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

Scopri quali sessioni si trovano in ogni gruppo usando 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

Scopri quali richieste ci sono in ogni gruppo usando 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

Scopri quali richieste sono in esecuzione nel classificatore 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

Procedure consigliate per l'uso delle tabelle di ricerca in una funzione di classificazione

Non usare una tabella di ricerca a meno che non sia assolutamente necessario. Se è necessario usare una tabella di ricerca, può essere codificata nella funzione stessa; tuttavia, questo deve essere bilanciato con la complessità e le variazioni dinamiche della funzione di classificazione.
Limitare le operazioni di I/O eseguite per le tabelle di ricerca.
1. Utilizzare TOP 1 per restituire solo una riga.
2. Ridurre al minimo il numero di righe nella tabella.
3. Fare in modo che tutte le righe della tabella esistano in una singola pagina o in un numero ridotto di pagine.
4. Verificare che le righe trovate usando le operazioni Index Seek usino il maggior numero possibile di colonne di ricerca.
5. Annullare la normalizzazione in una singola tabella se si valuta l'uso di più tabelle con join.
Impedire il blocco nella tabella di ricerca.
1. Usare l'hint NOLOCK per impedire il blocco o l'uso SET LOCK_TIMEOUT nella funzione con un valore massimo di 1000 millisecondi.
2. Le tabelle devono esistere nel database master. Il database master è l'unico database che viene garantito di essere recuperato quando i computer client tentano di connettersi.
3. Specificare sempre il nome completo della tabella con lo schema. Il nome del database non è necessario perché deve essere il database master.
4. Nessun trigger nella tabella.
5. Se si aggiorna il contenuto della tabella, assicurarsi di usare una transazione al livello di isolamento dello snapshot per impedire che lo Scrittore blocchi i Lettori. Si noti che l'uso dell'hint NOLOCK deve anche attenuare questo problema.
6. Se possibile, disabilitare la funzione di classificazione quando si modifica il contenuto del sommario.
  
  Avvertimento
  
  È consigliabile seguire queste procedure consigliate. Se si verificano problemi che impediscono di seguire le procedure consigliate, è consigliabile contattare il supporto tecnico Microsoft per evitare in modo proattivo eventuali problemi futuri.

Vedere anche

Resource Governor
Abilitare Resource Governor
Pool di risorse di Resource Governor
Gruppo di carico di lavoro del Resource Governor
Configurare Resource Governor usando un modello
Visualizzare proprietà di Resource Governor
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREARE GRUPPO DI CARICO DI LAVORO (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Last updated on 2017-06-13

Condividi tramite

Creare e testare una funzione di classificazione User-Defined

Per creare la funzione personalizzata per il classificatore

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

Procedure consigliate per l'uso delle tabelle di ricerca in una funzione di classificazione

Vedere anche

Risorse aggiuntive