locked
Configuração "Query Governor Cost Limit" RRS feed

  • Pergunta

  • Fiz a configuração do recurso "Query Governor" mas obtive o resultado desejado, realizei o procedimento seguindo o livro "Guia de bolso do administrador - Microsoft SQL Server 2008", segue abaixo os testes realizado e a configuração do Query governor.

    1 - Criei um banco e uma tabela;

    CREATE DATABASE CADASTRO
    GO

    USE CADASTRO
    GO
    CREATE TABLE TABELA (
    COD INT IDENTITY (1,1),
    NOME VARCHAR (30),
    SEXO CHAR (1),
    CIDADE VARCHAR (30),
    ESTADO VARCHAR (30),
    PAIS VARCHAR (30)
    )

    2 - Populei a tabela;

    INSERT INTO TABELA VALUES ('JOSE CARLOS DA SILVA', 'M', 'FORTALEZA', 'CEARÁ', 'BRASIL')
    GO 500000

    3 - Consula é retornada em 15 segundos;

    SELECT *
    FROM TABELA

    4 - Configurei o Query Governor Cost Limit;

    USE CADASTRO;
    GO
    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE ;
    GO
    EXEC sp_configure 'query governor cost limit', 10 ;
    GO
    RECONFIGURE;
    GO

    5 - Considerações:

    Mesmo com a execução do comando com sucesso o select continua sendo executado com o um retorno de 15 segundos, o que mais deve ser configurado?
    domingo, 31 de março de 2013 20:22

Respostas

  • Valtemberg,

    Sim, para isso o Resource Governor é indicado e deverá ser utilizado, mas você tem que definir o Cost_Limit para o seu pool.

    Veja abaixo se este exemplo ajuda:

    -- Configura o Resource Governor --
    ---DMV's Resource Governor
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_RESOURCE_POOLS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_CONFIGURATION
    
    --Criando os Pools
    Create RESOURCE POOL PoolMarketingAdHoc
    With
    (
     MAX_CPU_Percent = 20,
     MIN_CPU_Percent = 5,
     MAX_Memory_Percent = 30, 
     MIN_Memory_Percent =  10
    )  
    
    CREATE RESOURCE POOL PoolVP
    With
    (
     MAX_CPU_Percent = 25,
     MIN_CPU_Percent = 5
    )  
    
    -- Criando Grupos de WorkLoad --
    CREATE WORKLOAD GROUP GroupMarketing Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupAdHoc 
    With (Importance = Medium) Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupVP 
    With (Importance = Low) Using PoolVP
    Go
    
    -- Criando logins para separar os usuários dentro de diferentes grupos --
    CREATE LOGIN UserMarketing With Password = 'UserMarketingPwd', Check_Policy = Off
    CREATE LOGIN UserAdHoc With Password = 'UserAdHocPWD', Check_Policy = Off
    CREATE LOGIN UserVP With Password = 'UserVPPwd', Check_Policy = Off
    
    -- Criando Function para gerenciamento do pool --
    Create FUNCTION [dbo].[Classifier_ConectionPool]() 
    RETURNS SYSNAME 
    WITH SCHEMABINDING
    
    BEGIN
    
     DECLARE @WorkGrupo VarChar(32)
     SET @WorkGrupo = 'default'
     
     If 'UserVP' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupVP'
     Else If 'UserMarketing' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupMarketing'
     Else If 'UserAdHoc' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupAdHoc'
     RETURN @WorkGrupo
    End
    Go
    
    -- Alterando a configuração do Resource Governor --
    Alter Resource Governor
    With (Classifier_Function = dbo.classifier_conectionpool)
    Go
    
    -- Aplicando as reconfigurações no Resource Governor --
    Alter Resource Governor Reconfigure
    Go
    
    
    -- Obtendo o nome da Classifier Function --
    SELECT 
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration

    -- Utilizando --
    ---DMV's Resource Governor
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_RESOURCE_POOLS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_CONFIGURATION
    
    --Criando os Pools
    Create RESOURCE POOL PoolMarketingAdHoc
    With
    (
     MAX_CPU_Percent = 20,
     MIN_CPU_Percent = 5,
     MAX_Memory_Percent = 30, 
     MIN_Memory_Percent =  10
    )  
    
    CREATE RESOURCE POOL PoolVP
    With
    (
     MAX_CPU_Percent = 25,
     MIN_CPU_Percent = 5
    )  
    
    -- Criando Grupos de WorkLoad --
    CREATE WORKLOAD GROUP GroupMarketing Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupAdHoc 
    With (Importance = Medium) Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupVP 
    With (Importance = Low) Using PoolVP
    Go
    
    -- Criando logins para separar os usuários dentro de diferentes grupos --
    CREATE LOGIN UserMarketing With Password = 'UserMarketingPwd', Check_Policy = Off
    CREATE LOGIN UserAdHoc With Password = 'UserAdHocPWD', Check_Policy = Off
    CREATE LOGIN UserVP With Password = 'UserVPPwd', Check_Policy = Off
    
    -- Criando Function para gerenciamento do pool --
    Create FUNCTION [dbo].[Classifier_ConectionPool]() 
    RETURNS SYSNAME 
    WITH SCHEMABINDING
    
    BEGIN
    
     DECLARE @WorkGrupo VarChar(32)
     SET @WorkGrupo = 'default'
     
     If 'UserVP' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupVP'
     Else If 'UserMarketing' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupMarketing'
     Else If 'UserAdHoc' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupAdHoc'
     RETURN @WorkGrupo
    End
    Go
    
    -- Alterando a configuração do Resource Governor --
    Alter Resource Governor
    With (Classifier_Function = dbo.classifier_conectionpool)
    Go
    
    -- Aplicando as reconfigurações no Resource Governor --
    Alter Resource Governor Reconfigure
    Go
    
    
    -- Obtendo o nome da Classifier Function --
    SELECT 
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 4 de abril de 2013 14:49
    Moderador
  • Valtember,

    Então, mas neste caso, você vai ter que fazer uso de outro recurso que foi criado a partir do SQL Server 2008, que Policy Management, criando uma condition e depois uma policies fazendo uso do Resource Governor ou Resource Pool.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 4 de abril de 2013 14:52
    Moderador

Todas as Respostas

  • Valtemberg,

    O que você esta querendo fazer? Seria melhorar a performance do processamento da sua query? O Query Governor não é uma ferramenta para este tipo de finalidade, na verdade é uma tecnologia utilizada para distribuição e balanceamento de processamento de dados.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    terça-feira, 2 de abril de 2013 16:12
    Moderador
  • Junior, pelo que entendi após setar o Query Governor com valor igual a 10 segundo, não seria possível realizar consultas no banco que ultrapassassem esse limite de tempo, mas isso não ocorreu, antes do parâmetro eu executava uma consulta com tempo de 15 segundo e após a determinação de 10 segundos essa mesma consulta e todas as outras não seriam possível de serem realizadas.


    quarta-feira, 3 de abril de 2013 03:39
  • Valtemberg,

    Você já tentou utilizar pelo SET? Faça teste pelo set para verificar se está aplicando as restrições, pois seus comandos estão corretos.

    SET QUERY_GOVERNOR_COST_LIMIT 10
    Lembrando que pelo SET, aplica-se em apenas na consulta atual.



    []'s

    quarta-feira, 3 de abril de 2013 13:07

  • Fernando, usando o SET funciona so na consulta atual, mas eu quero limitar pelo servidor, para identificar quais usuarios ou aplicações estão fazendo consultas demoradas.
    quarta-feira, 3 de abril de 2013 16:38
  • Valtemberg,

    Sim, para isso o Resource Governor é indicado e deverá ser utilizado, mas você tem que definir o Cost_Limit para o seu pool.

    Veja abaixo se este exemplo ajuda:

    -- Configura o Resource Governor --
    ---DMV's Resource Governor
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_RESOURCE_POOLS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_CONFIGURATION
    
    --Criando os Pools
    Create RESOURCE POOL PoolMarketingAdHoc
    With
    (
     MAX_CPU_Percent = 20,
     MIN_CPU_Percent = 5,
     MAX_Memory_Percent = 30, 
     MIN_Memory_Percent =  10
    )  
    
    CREATE RESOURCE POOL PoolVP
    With
    (
     MAX_CPU_Percent = 25,
     MIN_CPU_Percent = 5
    )  
    
    -- Criando Grupos de WorkLoad --
    CREATE WORKLOAD GROUP GroupMarketing Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupAdHoc 
    With (Importance = Medium) Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupVP 
    With (Importance = Low) Using PoolVP
    Go
    
    -- Criando logins para separar os usuários dentro de diferentes grupos --
    CREATE LOGIN UserMarketing With Password = 'UserMarketingPwd', Check_Policy = Off
    CREATE LOGIN UserAdHoc With Password = 'UserAdHocPWD', Check_Policy = Off
    CREATE LOGIN UserVP With Password = 'UserVPPwd', Check_Policy = Off
    
    -- Criando Function para gerenciamento do pool --
    Create FUNCTION [dbo].[Classifier_ConectionPool]() 
    RETURNS SYSNAME 
    WITH SCHEMABINDING
    
    BEGIN
    
     DECLARE @WorkGrupo VarChar(32)
     SET @WorkGrupo = 'default'
     
     If 'UserVP' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupVP'
     Else If 'UserMarketing' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupMarketing'
     Else If 'UserAdHoc' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupAdHoc'
     RETURN @WorkGrupo
    End
    Go
    
    -- Alterando a configuração do Resource Governor --
    Alter Resource Governor
    With (Classifier_Function = dbo.classifier_conectionpool)
    Go
    
    -- Aplicando as reconfigurações no Resource Governor --
    Alter Resource Governor Reconfigure
    Go
    
    
    -- Obtendo o nome da Classifier Function --
    SELECT 
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration

    -- Utilizando --
    ---DMV's Resource Governor
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_WORKLOAD_GROUPS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_RESOURCE_POOLS
    SELECT * FROM SYS.DM_RESOURCE_GOVERNOR_CONFIGURATION
    
    --Criando os Pools
    Create RESOURCE POOL PoolMarketingAdHoc
    With
    (
     MAX_CPU_Percent = 20,
     MIN_CPU_Percent = 5,
     MAX_Memory_Percent = 30, 
     MIN_Memory_Percent =  10
    )  
    
    CREATE RESOURCE POOL PoolVP
    With
    (
     MAX_CPU_Percent = 25,
     MIN_CPU_Percent = 5
    )  
    
    -- Criando Grupos de WorkLoad --
    CREATE WORKLOAD GROUP GroupMarketing Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupAdHoc 
    With (Importance = Medium) Using PoolMarketingAdHoc
    
    CREATE WORKLOAD GROUP GroupVP 
    With (Importance = Low) Using PoolVP
    Go
    
    -- Criando logins para separar os usuários dentro de diferentes grupos --
    CREATE LOGIN UserMarketing With Password = 'UserMarketingPwd', Check_Policy = Off
    CREATE LOGIN UserAdHoc With Password = 'UserAdHocPWD', Check_Policy = Off
    CREATE LOGIN UserVP With Password = 'UserVPPwd', Check_Policy = Off
    
    -- Criando Function para gerenciamento do pool --
    Create FUNCTION [dbo].[Classifier_ConectionPool]() 
    RETURNS SYSNAME 
    WITH SCHEMABINDING
    
    BEGIN
    
     DECLARE @WorkGrupo VarChar(32)
     SET @WorkGrupo = 'default'
     
     If 'UserVP' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupVP'
     Else If 'UserMarketing' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupMarketing'
     Else If 'UserAdHoc' = SUSER_SNAME()
      SET @WorkGrupo = 'GroupAdHoc'
     RETURN @WorkGrupo
    End
    Go
    
    -- Alterando a configuração do Resource Governor --
    Alter Resource Governor
    With (Classifier_Function = dbo.classifier_conectionpool)
    Go
    
    -- Aplicando as reconfigurações no Resource Governor --
    Alter Resource Governor Reconfigure
    Go
    
    
    -- Obtendo o nome da Classifier Function --
    SELECT 
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 4 de abril de 2013 14:49
    Moderador
  • Valtember,

    Então, mas neste caso, você vai ter que fazer uso de outro recurso que foi criado a partir do SQL Server 2008, que Policy Management, criando uma condition e depois uma policies fazendo uso do Resource Governor ou Resource Pool.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 4 de abril de 2013 14:52
    Moderador