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

Article
06/25/2013

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êtes 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 le gouverneur de ressources utilise la liaison de schéma pour les fonctions classifieur.

[!REMARQUE]

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.

[!REMARQUE]

SQL Server 2008 introduit un jeu étendu de types de données et de fonctions 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.

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.
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.)
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.
Pas de déclencheurs sur la table.
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.

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

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