Partager via

  • Article

Procédure : créer et tester une fonction définie par l'utilisateur classifieur (Transact-SQL)

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. Nous vous recommandons de lire les Considérations sur l'écriture d'une fonction classifieur avant de continuer.

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

Comme noté dans Considérations sur l'écriture d'une fonction classifieur, 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 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 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.

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

  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 charge de travail et la fonction définie par l'utilisateur classifieur

  1. Obtenez la configuration de pool de ressources et de 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 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 charge 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 s
     INNER JOIN sys.dm_resource_governor_workload_groups 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 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é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