Creare e testare una funzione di classificazione definita dall'utenteCreate and Test a Classifier User-Defined Function

In questo argomento viene illustrato come creare e testare una funzione di classificazione definita dall'utente.This topic shows how to create and test a classifier user-defined function (UDF). La procedura prevede l'esecuzione di istruzioni Transact-SQLTransact-SQL nell'editor di query di SQL Server Management StudioSQL Server Management Studio .The steps involve executing Transact-SQLTransact-SQL statements in the SQL Server Management StudioSQL Server Management Studio Query Editor.

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.The example shown in the following procedure illustrates the possibilities for creating a fairly complex classifier user-defined function.

Nell'esempio:In our example:

  • 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.A resource pool (pProductionProcessing) and workload group (gProductionProcessing) are created for production processing during a specified time range.

  • 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.A resource pool (pOffHoursProcessing) and workload group (gOffHoursProcessing) are created for handling connections that do not meet the requirements for production processing.

  • 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.A table (TblClassificationTimeTable) is created in master to hold start and end times that can be evaluated against a login time. Tale tabella deve essere creata nel database master perché per le funzioni di classificazione Resource Governor utilizza l'associazione allo schema.This must be created in master because Resource Governor uses schema binding for classifier functions.

    Nota

    Come procedura ottimale, è consigliabile non archiviare nel database master tabelle di grandi dimensioni aggiornate di frequente.As a best practice, you should not store large, frequently updated tables in master.

    La funzione di classificazione prolunga i tempi di accesso.The classifier function extends the login time. Una funzione eccessivamente complessa può provocare il timeout degli accessi o rallentare connessioni veloci.An overly complex function can cause logins to time out or slow down fast connections.

Per creare la funzione di classificazione definita dall'utenteTo create the classifier user-defined function

  1. Creare e configurare i nuovi pool di risorse e i nuovi gruppi del carico di lavoro.Create and configure the new resource pools and workload groups. Assegnare ogni gruppo del carico di lavoro al pool di risorse appropriato.Assign each workload group to the appropriate resource pool.

    --- 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.Update the in-memory configuration.

    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.Create a table and define the start and end times for the production processing time range.

    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.Create the classifier function that uses time functions and values that can be evaluated against the times in the lookup table. Per informazioni sull'utilizzo di tabelle di ricerca in una funzione di classificazione, vedere "Procedure consigliate per l'utilizzo di tabelle di ricerca in una funzione di classificazione" in questo argomento.For information about using Lookup Tables in a classifier function, see “Best practices for using Lookup Tables in a classifier function” in this topic.

    Nota

    SQL Server 2008SQL Server 2008 include un set più ampio di tipi di dati e funzioni di data e ora. introduced an expanded set of date and time data types and functions. Per altre informazioni, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).For more information, see Date and Time Data Types and Functions (Transact-SQL).

    CREATE FUNCTION fnTimeClassifier()  
    RETURNS sysname  
    WITH SCHEMABINDING  
    AS  
    BEGIN  
    /* We recommend running the classifier function code under 
    snapshot isolation level OR using NOLOCK hint to avoid blocking on 
    lookup table. In this example, we are using NOLOCK hint. */
         DECLARE @strGroup sysname  
         DECLARE @loginTime time  
         SET @loginTime = CONVERT(time,GETDATE())  
         SELECT TOP 1 @strGroup = strGroupName  
              FROM dbo.tblClassificationTimeTable WITH(NOLOCK)
              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.Register the classifier function and update the in-memory configuration.

    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'utenteTo verify the resource pools, workload groups, and the classifier user-defined function

  1. Ottenere la configurazione dei pool di risorse e dei gruppi del carico di lavoro utilizzando la query seguente.Obtain the resource pool and workload group configuration by using the following query.

    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.Verify that the classifier function exists and is enabled by using the following queries.

    --- 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.Obtain the current runtime data for the resource pools and workload groups by using the following query.

    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.Find out what sessions are in each group by using the following query.

    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 AS s  
    INNER JOIN sys.dm_resource_governor_workload_groups AS 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.Find out which requests are in each group by using the following query.

    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 AS r  
    INNER JOIN sys.dm_resource_governor_workload_groups AS 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.Find out what requests are running in the classifier by using the following query.

    SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name   
    FROM sys.dm_exec_sessions AS s  
    INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
    INNER JOIN sys.dm_resource_governor_workload_groups AS 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'utilizzo di tabelle di ricerca in una funzione di classificazioneBest practices for using Lookup Tables in a classifier function

  1. Non utilizzare una tabella di ricerca, a meno che non sia strettamente necessario.Do not use a lookup table unless it is absolutely necessary. Se è necessario utilizzare una tabella di ricerca, può essere specificata a livello di codice nella funzione stessa. È necessario tuttavia tenere presente la complessità e le modifiche dinamiche della funzione di classificazione.If you need to use a lookup table, it can be hard coded into the function itself; however, this needs to be balanced with the complexity and dynamic changes of the classifier function.

  2. Limitare l'I/O eseguito per le tabelle di ricerca.Limit the I/O performed for lookup tables.

    1. Utilizzare TOP 1 per restituire solo una riga.Use the TOP 1 to return only one row.

    2. Ridurre al minimo il numero di righe della tabella.Minimize the number of rows in the table.

    3. Includere tutte le righe della tabella in una sola pagina o in un numero ridotto di pagine.Make all rows of the table exist on a single page, or a small number of pages.

    4. Verificare che le righe individuate utilizzando le operazioni Index Seek utilizzino il numero massimo consentito di colonne di ricerca.Confirm that rows found using the Index Seek operations use as many seeking columns as possible.

    5. Se si prevede di utilizzare più tabelle con join, eseguire la denormalizzazione in una sola tabella.De-normalize to a single table if you are considering using multiple tables with joins.

  3. Evitare blocchi nella tabella di ricerca.Prevent blocking on the lookup table.

    1. Utilizzare l'hint NOLOCK per evitare i blocchi o utilizzare SET LOCK_TIMEOUT nella funzione con un valore massimo di 1.000 millisecondi.Use the NOLOCK hint to prevent blocking or use SET LOCK_TIMEOUT in the function with a maximum value of 1000 milliseconds.

    2. Nel database master devono essere presenti una o più tabelle.Table(s) must exist in the master database. Si tratta dell'unico database che verrà recuperato in caso di tentativo di connessione dei computer client.(The master database is the only database that is guaranteed to be recovered when the client computers attempt to connect).

    3. Fornire sempre un nome completo per la tabella con lo schema.Always fully-qualify the table name with the schema. Non è necessario specificare il nome del database, poiché deve essere utilizzato il database master.The database name is not necessary since it has to be the master database.

    4. Nessun trigger nella tabella.No triggers on the table.

    5. In fase di aggiornamento dei contenuti della tabella, accertarsi di usare una transazione con livello di isolamento dello snapshot nella funzione di classificazione per evitare che il writer blocchi i lettori.If you are updating the table contents, make sure to use a snapshot isolation level transaction in the classifier function to prevent Writer blocking Readers. Un altro metodo per evitare questo problema consiste nell'utilizzare l'hint NOLOCK .Note that using the NOLOCK hint should also mitigate this.

    6. Se possibile, disabilitare la funzione di classificazione in fase di modifica dei contenuti della tabella.If possible, disable the classifier function when changing the table contents.

      Avviso

      È consigliabile attenersi scrupolosamente a queste procedure consigliate.We highly recommend following these best practices. In caso non sia possibile seguire le procedure, contattare il supporto Microsoft per evitare in modo proattivo che si verifichino problemi in futuro.If there are issues that prevent you from following the best practices, we recommend that you contact Microsoft Support so that you can proactively prevent any future problems.

Vedere ancheSee Also

Resource Governor Resource Governor
Abilitare Resource Governor Enable Resource Governor
Pool di risorse di Resource Governor Resource Governor Resource Pool
Gruppo di carico di lavoro di Resource Governor Resource Governor Workload Group
Configurare Resource Governor utilizzando un modello Configure Resource Governor Using a Template
Visualizzare proprietà di Resource Governor View Resource Governor Properties
ALTER RESOURCE GOVERNOR (Transact-SQL) ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL) CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)ALTER RESOURCE GOVERNOR (Transact-SQL)