Criar e testar uma função de classificação User-Defined

Este tópico mostra como criar e testar uma UDF (função definida pelo usuário) do classificador. As etapas envolvem a execução de instruções Transact-SQL no Editor de Consultas do SQL Server Management Studio.

O exemplo mostrado no procedimento a seguir ilustra as possibilidades de criar uma função definida pelo usuário do classificador bastante complexa.

Em nosso exemplo:

Um pool de recursos (pProductionProcessing) e um grupo de carga de trabalho (gProductionProcessing) são criados para processamento de produção durante um intervalo de tempo especificado.
Um pool de recursos (pOffHoursProcessing) e um grupo de carga de trabalho (gOffHoursProcessing) são criados para lidar com conexões que não atendem aos requisitos de processamento de produção.
Uma tabela (TblClassificationTimeTable) é criada no banco de dados mestre para manter os horários de início e término que podem ser avaliados em relação a um tempo de logon. Isso deve ser criado no mestre porque o Administrador de Recursos usa a associação de esquema para funções de classificador.

Observação

Como prática recomendada, você não deve armazenar tabelas grandes e atualizadas com frequência no mestre.

A função de classificador estende o tempo de logon. Uma função excessivamente complexa pode fazer com que os logons excedam o tempo limite ou desacelerem conexões rápidas.

Para criar a função do classificador definida pelo usuário

Crie e configure os novos pools de recursos e grupos de carga de trabalho. Atribua cada grupo de carga de trabalho ao pool de recursos apropriado.

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

Atualize a configuração na memória.

ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Crie uma tabela e defina os horários de início e término para o intervalo de tempo de processamento de produção.

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

Crie a função de classificador que usa funções de tempo e valores que podem ser avaliados em relação aos horários na tabela de pesquisa. Para obter informações sobre como usar Tabelas de Pesquisa em uma função de classificador, consulte "Práticas recomendadas para usar tabelas de pesquisa em uma função de classificador" neste tópico.

Observação

O SQL Server 2008 introduziu um conjunto expandido de funções e tipos de dados de data e hora. Para obter mais informações, consulte Tipos e Funções de Dados de Data e Hora (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

Registre a função de classificador e atualize a configuração na memória.

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

Para verificar os pools de recursos, os grupos de carga de trabalho e a função definida pelo usuário do classificador

Obtenha a configuração do pool de recursos e do grupo de carga de trabalho usando a consulta a seguir.

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

Verifique se a função de classificador existe e se está habilitada usando as consultas a seguir.

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

Obtenha os dados de runtime atuais para os pools de recursos e grupos de carga de trabalho usando a consulta a seguir.

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

Descubra quais sessões estão em cada grupo usando a consulta a seguir.

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

Descubra quais solicitações estão em cada grupo usando a consulta a seguir.

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

Descubra quais solicitações estão em execução no classificador usando a consulta a seguir.

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

Práticas recomendadas para usar tabelas de pesquisa em uma função de classificador

Não use uma tabela de pesquisa, a menos que seja absolutamente necessária. Se você precisar usar uma tabela de pesquisa, ela poderá ser codificada na própria função; no entanto, isso precisa ser equilibrado com a complexidade e as alterações dinâmicas da função de classificador.
Limite a E/S executada para tabelas de pesquisa.
1. Use o TOP 1 para retornar apenas uma linha.
2. Minimize o número de linhas na tabela.
3. Faça com que todas as linhas da tabela existam em uma única página ou em um pequeno número de páginas.
4. Confirme se as linhas obtidas usando as buscas de Índice utilizam o máximo possível de colunas para busca.
5. Desnormalizar para uma única tabela se você estiver considerando usar várias tabelas com associações.
Evitar bloqueio na tabela de consulta.
1. Use a dica NOLOCK para impedir o bloqueio ou use SET LOCK_TIMEOUT na função com um valor máximo de 1000 milissegundos.
2. As tabelas devem existir no banco de dados mestre. (O banco de dados mestre é o único banco de dados que tem a garantia de ser recuperado quando os computadores cliente tentam se conectar).
3. Sempre qualifique totalmente o nome da tabela com o esquema. O nome do banco de dados não é necessário, pois precisa ser o banco de dados mestre.
4. Nenhum gatilho na tabela.
5. Se você estiver atualizando o conteúdo da tabela, certifique-se de usar uma transação com nível de isolamento de instantâneo para impedir que o escritor bloqueie os leitores. Observe que o uso da NOLOCK sugestão também deve atenuar isso.
6. Se possível, desabilite a função de classificador ao alterar o conteúdo da tabela.
  
  Aviso
  
  É altamente recomendável seguir essas práticas recomendadas. Se houver problemas que o impeçam de seguir as práticas recomendadas, recomendamos que você entre em contato com o Suporte da Microsoft para evitar proativamente quaisquer problemas futuros.

Consulte Também

Administrador de Recursos
Habilitar o Resource Governor
Pool de recursos do Resource Governor
Grupos de carga de trabalho do Resource Governor
Configurar o Resource Governor usando um modelo
Exibir Propriedades do Administrador de Recursos
ALTER RESOURCE GOVERNOR (Transact-SQL)
CRIAR POOL DE RECURSOS (Transact-SQL)
CRIAR GRUPO DE CARGA DE TRABALHO (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

Last updated on 2017-06-13

Partilhar via

Criar e testar uma função de classificação User-Defined

Para criar a função do classificador definida pelo usuário

Para verificar os pools de recursos, os grupos de carga de trabalho e a função definida pelo usuário do classificador

Práticas recomendadas para usar tabelas de pesquisa em uma função de classificador

Consulte Também

Recursos adicionais