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

Article
06/13/2017

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 le SQL Server Management Studio Éditeur de requête.

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.

Notes

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

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

Mettez à jour la configuration en mémoire.

ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

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

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.

Notes

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

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

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

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

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  
```

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 s  
     INNER JOIN sys.dm_resource_governor_workload_groups g  
          ON g.group_id = s.group_id  
ORDER BY g.name  
GO

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 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

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 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 recommandées pour l'utilisation de tables de recherche dans une fonction classifieur

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 de façon irréversible dans la fonction elle-même ; toutefois, cette action doit être équilibrée avec la complexité et les modifications dynamiques de la fonction classifieur.
Limitez les E/S effectuées pour les tables de recherche.
1. Utilisez TOP 1 pour ne retourner qu'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.
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 effectuer une mise à jour du contenu de la table, veillez à utiliser une transaction de niveau d'isolement d'instantané 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 du gouverneur de ressources
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Partager via

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

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

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

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

Voir aussi

Ressources supplémentaires