Partager via

Créer et tester une fonction de classifieur User-Defined

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

L’exemple présenté dans la procédure suivante illustre les possibilités de création d’une fonction classifieur relativement complexe définie par l’utilisateur.

Dans notre exemple :

  • Un pool de ressources (pProductionProcessing) et un groupe de charges de travail (gProductionProcessing) sont créés pour le traitement de production pendant un intervalle de temps spécifié.

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

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

    Remarque

    Comme meilleure pratique, vous ne devez pas stocker de tables volumineuses et fréquemment mises à jour dans master.

La fonction classificateur étend le temps de connexion. Une fonction trop complexe peut entraîner l’expiration des connexions 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 les groupes de charge 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 de l’intervalle de temps 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 de temps et des valeurs qui peuvent être évaluées par rapport aux heures de la table de recherche. Pour plus d’informations sur l’utilisation des tables de recherche dans une fonction classifieur, consultez « Meilleures pratiques pour l’utilisation de tables de recherche dans une fonction classifieur » dans cette rubrique.

    Remarque

    SQL Server 2008 a introduit un ensemble étendu de fonctions et types de données de date et d’heure. Pour plus d'informations, consultez Types de données de date et d'heure et fonctions (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. 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 classifieur définie par l’utilisateur

  1. Obtenez la configuration du pool de ressources et du groupe de charge 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 est activée à l’aide des 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 les groupes de charge de travail à l’aide de la requête suivante.

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

  4. Découvrez les sessions dans chaque groupe à l’aide de 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 s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
          ON g.group_id = s.group_id  
ORDER BY g.name  
GO

  5. Découvrez les requêtes qui se trouvent dans chaque groupe à l’aide de 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 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. Découvrez les requêtes qui s’exécutent dans le classifieur à l’aide de 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 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

Meilleures pratiques pour l’utilisation de tables de recherche dans une fonction classifieur

  1. N’utilisez pas de table de recherche, sauf s’il est absolument nécessaire. Si vous avez besoin d’utiliser une table de recherche, elle peut être codée en dur dans la fonction elle-même ; Toutefois, cela doit être équilibré avec la complexité et les changements dynamiques de la fonction classifieur.

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

    1. Utilisez le TOP 1 pour renvoyer une seule ligne.

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

    3. Placez toutes les lignes de la table sur une seule page ou un petit nombre de pages.

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

    5. Considérez la dénormalisation en une seule table si vous envisagez d’utiliser plusieurs tables avec des jointures.

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

    1. Utilisez l’indicateur NOLOCK pour empêcher le blocage ou l’utilisation 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 garantie à être récupérée lorsque les ordinateurs clients tentent de se connecter).

    3. Veillez toujours à spécifier entièrement le nom de la table avec le schéma. Le nom de la base de données n’est pas nécessaire, car il doit s’agir de la base de données master.

    4. Aucun déclencheur sur la table.

    5. Si vous mettez à jour le contenu de la table, veillez à utiliser une transaction de niveau d’isolation d’instantané pour empêcher l’enregistreur de bloquer les lecteurs. Notez que l’utilisation de l’indicateur NOLOCK doit également atténuer ce problème.

    6. Si possible, désactivez la fonction classifieur lors de la modification du contenu de la table.

      Avertissement

      Nous vous recommandons vivement de suivre ces meilleures pratiques. S’il existe des problèmes qui vous empêchent de suivre les meilleures pratiques, nous vous recommandons de contacter le support Microsoft afin de pouvoir éviter de manière proactive les é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 du gouverneur de ressources
ALTER RESOURCE GOVERNOR (Transact-SQL)
CRÉER UN GROUPE DE RESSOURCES (Transact-SQL)
CRÉER GROUPE DE TRAVAIL (Transact-SQL)
CRÉER FONCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

  • Last updated on