Créer et tester une fonction classifieur définie par l’utilisateur

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Cette rubrique indique comment créer et tester une fonction définie par l'utilisateur classifieur (UDF). Les étapes impliquent l’exécution d’instructions Transact-SQL dans l’éditeur de requête SQL Server Management Studio.

L'exemple de la procédure suivante illustre les possibilités de création d'une fonction définie par l'utilisateur classifieur assez complexe.

Dans notre exemple :

  • Un pool de ressources (pProductionProcessing) et un groupe de charges de travail (gProductionProcessing) sont créés pour le traitement de la production pendant une plage temporelle spécifiée.

  • Un pool de ressources (pOffHoursProcessing) et un groupe de charges de travail (gOffHoursProcessing) sont créés pour gérer les connexions qui ne répondent pas aux besoins du traitement de production.

  • Une table (TblClassificationTimeTable) est créée dans la base de données master afin de contenir les heures de début et de fin qui peuvent être évaluées par rapport à une heure de connexion. Cette table doit être créée dans la base de données master car Resource Governor utilise la liaison de schéma pour les fonctions classifieur.

    Note

    Il est recommandé de ne pas stocker de grandes tables fréquemment mises à jour dans la base de données master.

La fonction classifieur étend le temps de connexion. Une fonction trop complexe peut provoquer l'expiration des délais d'attente de connexion ou ralentir les connexions rapides.

Pour créer la fonction classifieur définie par l'utilisateur

  1. Créez et configurez les nouveaux pools de ressources et groupes de charges de travail. Affectez chaque groupe de charge de travail au pool de ressources approprié.

    --- 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. Mettez à jour la configuration en mémoire.

    ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

  3. Créez une table et définissez les heures de début et de fin pour la plage temporelle de traitement de production.

    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. Créez la fonction classifieur qui utilise des fonctions d'heure et des valeurs qui peuvent être évaluées par rapport aux heures figurant dans la table de recherche. Pour plus d’informations sur l’utilisation des tables de recherche dans une fonction classifieur, consultez la section « Meilleures pratiques recommandées pour l’utilisation de tables de recherche dans une fonction classifieur » dans cette rubrique.

    Note

    SQL Server 2008 (10.0.x) a introduit un ensemble étendu de types et de fonctions de données de date et d’heure. Pour plus d’informations, consultez Types et fonctions de données date et heure (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. Inscrivez la fonction classifieur et mettez à jour la configuration en mémoire.

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

Pour vérifier les pools de ressources, les groupes de charges de travail et la fonction définie par l'utilisateur classifieur

  1. Obtenez la configuration de pool de ressources et de groupe de charges de travail à l'aide de la requête suivante.

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

  2. Vérifiez que la fonction classifieur existe et qu'elle est activée en utilisant les requêtes suivantes.

    --- 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. Obtenez les données d'exécution actuelles pour les pools de ressources et groupes de charges de travail en utilisant la requête suivante.

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

  4. Déterminez quelles sessions se trouvent dans chaque de groupe en utilisant la requête suivante.

    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. Déterminez quelles demandes se trouvent dans chaque groupe en utilisant la requête suivante.

    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. Déterminez quelles demandes s'exécutent dans la fonction classifieur en utilisant la requête suivante.

    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

Meilleures pratiques recommandées pour l'utilisation de tables de recherche dans une fonction classifieur

  1. N'utilisez pas de table de recherche sauf en cas d'absolue nécessité. Si vous devez utiliser une table de recherche, vous pouvez la coder en dur dans la fonction elle-même ; cependant, vous devrez composer avec la complexité et les modifications dynamiques de la fonction classifieur.

  2. Limitez les E/S effectuées pour les tables de recherche.

    1. Utilisez TOP 1 pour retourner une seule ligne.

    2. Réduisez le nombre de lignes présentes dans la table.

    3. Faites en sorte que toutes les lignes de la table s'affichent sur une seule page ou sur un petit nombre de pages.

    4. Vérifiez que les lignes trouvées à l'aide des opérations de recherche d'index utilisent autant de colonnes de recherche que possible.

    5. Dénormalisez une seule table si vous envisagez d'utiliser plusieurs tables comportant des jointures.

  3. Empêchez tout blocage sur la table de recherche.

    1. Utilisez l’indicateur NOLOCK pour empêcher tout blocage ou utilisez SET LOCK_TIMEOUT dans la fonction avec une valeur maximale de 1 000 millisecondes.

    2. Les tables doivent exister dans la base de données master. (La base de données master est la seule base de données dont la récupération est garantie lorsque les ordinateurs clients essaient de se connecter.)

    3. Qualifiez toujours entièrement le nom de la table avec le schéma. Le nom de la base de données n'est pas nécessaire dans la mesure où il doit s'agir de la base de données master.

    4. Pas de déclencheurs sur la table.

    5. Si vous mettez à jour le contenu de la table, veillez à utiliser une transaction de niveau d’isolement de capture instantanée dans la fonction classifieur afin d’éviter que l’enregistreur ne bloque les lecteurs. Notez que l'utilisation de l'indicateur NOLOCK doit également réduire cet effet.

    6. Si possible, désactivez la fonction classifieur lorsque vous modifiez le contenu de la table.

      Avertissement

      Nous vous recommandons vivement d'appliquer ces meilleures pratiques. Si des problèmes vous empêchent de les mettre en œuvre, nous vous recommandons de contacter le suport technique de Microsoft afin de d'éviter d'éventuels problèmes futurs.

Voir aussi

gouverneur de ressources
Activer Resource Governor
Pool de ressources de Resource Governor
Groupe de charge de travail de Resource Governor
Configurer Resource Governor à l’aide d’un modèle
Afficher les propriétés de Resource Governor
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)