none
Registro de acesso derrubando o servidor RRS feed

  • Pergunta

  • Bom dia pessoal,

    A query abaixo é para registro de LOG de acesso, mas com 100 conexões por segundo, o servidor ( com 64gb de ram, ssd , 8 nucleso ) vai a 100% de uso.

    Tenho um indice usando empresa_id, acesso_dataRegistro, cliente_id e visitante_id, mas o servidor não está suportando.

    Alguém tem uma dica de como otimizar isso ?
    Na tabela existe 1 milhão de registros.

    Agradeço qualquer ajuda

    alter PROCEDURE [dbo].[Stp_Acesso_Registrar]
    	@Empresa_Id smallint,
    	@Cliente_Id int,
    	@Visitante_Id char(30)
    AS
    Declare
    	@FlagReg int,
    	@Ano char(4), 
    	@Dia char(2), 
    	@Mes char(2), 
    	@Hora char(2), 
    	@Data datetime 
    BEGIN
    
    --return
    
    set @Ano = datepart(yyyy,getdate()) 
    set @Mes = datepart(MM,getdate()) 
    set @Dia = datepart(dd,getdate()) 
    set @Hora = datepart(HH,getdate()) 
    set @Data = @Ano + '-' + ltrim(rtrim(@Mes)) + '-' + @Dia + ' ' + @Hora + ':00' 
    
    
    
    set @cliente_id = isnull(@Cliente_id,0)
    set @visitante_id = ltrim(rtrim(@visitante_id))
    
    	SET NOCOUNT ON;
    
    	SET @FlagReg = (SELECT
    		Empresa_Id
    	FROM
    		Acesso
    	WHERE
    		Empresa_Id = @Empresa_Id AND
    		Acesso_DataRegistro = @Data AND
    		isnull(Cliente_Id,0) = @Cliente_Id AND
    		Visitante_Id = @Visitante_Id)
    
    	IF @FlagReg is NULL
    		BEGIN 
    			INSERT INTO Acesso
               (Empresa_Id
    		   ,Cliente_Id
    		   ,Visitante_Id
               ,Acesso_DataRegistro
               ,Acesso_Quantidade)
    			VALUES
               (@Empresa_Id
    		   ,@Cliente_Id
    		   ,@Visitante_Id
               ,@Data
               ,1)
    		END
    	ELSE
    		BEGIN
    			UPDATE
    				Acesso
    			SET
    				Acesso_Quantidade = Acesso_Quantidade + 1
    			WHERE
    				Empresa_Id = @Empresa_Id AND
    				Acesso_DataRegistro = @Data AND
    				isnull(Cliente_Id,0) = @Cliente_Id AND
    				Visitante_Id = @Visitante_Id
    		END
        
    END
    
    
    
    
    
    
    
    


    terça-feira, 21 de fevereiro de 2017 13:07

Respostas

  • Rafael,

    Table Scan é um dos operadores existentes no plano de execução que devemos evitar com que ele seja apresentado, o mesmo indica a falta de índice clusterizado ou seja falta de uma chave primária na table que esta sendo varrida pelo Database Engine, identifique qual é esta tabela e adicione uma chave primária na mesma.

    Outro ponto importante, em alguns casos quando estamos trabalhando com Stored Procedure as variáveis "parâmetros" que estão sendo utilizados como elementos para passagem de valores podem acabar forçando um comportamento conhecido como parameter sniffing, quando o SQL Server é obrigado é fatiar o processamento em partes para tentar reconhecer o valor da variável, afim de evitar isso utilize a opção Optimize For no código fonte da sua Stored Procedure, bem como, a table hint Recompile.

    Veja se este exemplo ajuda:

    -- Criando o Banco de Dados 
    Create Database ParameterSniffing
    Go
    
    -- Criando a TabelaDados --
    Create Table TabelaDados
    (Id Int Identity Primary Key,
     Descricao Char(100),
     Localal Char(6),
     DataCriacao DateTime Default Getdate(),
     Status char(2) default 'AC')
    Go
    
    -- Criando Índice na coluna Local para TabelaDados --
    Create Index IND_Local on TabelaDados(Local)
    Go
    
    -- Inserindo massa de dados --
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalA')
    GO 100000
    
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalB')
    GO 10
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalA'
    Go
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalB'
    Go
    
    -- Criando a Stored Procedure 
    Create Procedure ObterDadosPorLocal (@Local Char(6))
    As
    Begin
    
     Set NoCount ON
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    
    -- Obtendo informações sobre Plano de Execução de Stored Procedures --
    Select OBJECT_NAME(s.object_id) SP_Name,
           eqp.query_plan
    From sys.dm_exec_procedure_stats s CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
    Where DB_NAME(database_id) = 'ParameterSniffing'
    
    -- Recompilando a Stored Procedure --
    SP_Recompile 'ObterDadosPorLocal'
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal adicionando a opção Recompile --
    Alter Procedure ObterDadosPorLocal (@Local as Char(6)) With Recompile
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, descartando o Cache do Plano de Execução --
    Exec ObterDadosPorLocal @Local= N'LocalA' 
    Exec ObterDadosPorLocal @Local= N'LocalB' 
    Go
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, recompilando e utilizando o do Plano de Execução, sem fazer o armazenamento --
    Exec [dbo].[ObterDadosPorLocal] @Local = N'LocalA' WITH RECOMPILE
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal removendo a opção Recompile e adicionando a Query Hint Optimize --
    Alter Procedure ObterDadosPorLocal (@Local CHAR(6))
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
     OPTION (OPTIMIZE FOR (@Local = 'LocalA'))
    
    End
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 22 de fevereiro de 2017 18:37

Todas as Respostas

  • Vamos por partes ?

    1) O SQL Server está registrados para usar todos os Núcleos ??

    2) qual versão do SQL server 

    4)quantas instancias estão instaladas ?

    5) vc consegue reproduzir o problema em um ambiente de desenvolvimento ??

    se sim  já usou  o Profile ??

    Por favor,  marque-o como respondidas se está respondeu a sua pergunta 
    ou marcá-lo como útil se está ajudou a resolver o seu problema 
    Wesley Neves
    MTA-Database Fundamentals 
    Analista Desenvolvedor.NET


    Wesley Neves

    terça-feira, 21 de fevereiro de 2017 13:43
  • Bom Dia,

    Como identificou que o problema de CPU é essa query?

    Executa essa query abaixo para ver quem está usando os cores de CPU durante o pico:

    SELECT
    a.scheduler_id ,
    b.session_id,
     (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
          ( (CASE WHEN statement_end_offset = -1
             THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
             ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
    FROM sys.dm_os_schedulers a
    INNER JOIN sys.dm_os_tasks b on a.active_worker_address = b.worker_address
    INNER JOIN sys.dm_exec_requests c on b.task_address = c.task_address
    CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS s2


    Fabrício França Lima MCITP - SQL Server Database Administrator Trabalho com SQL Server desde 2006 Treinamento DBA ONLINE: http://www.fabriciolima.net/blog/cursos-online/treinamento-tarefas-do-dia-a-dia-de-um-dba-online/

    terça-feira, 21 de fevereiro de 2017 14:32
  • Ola Wesley

    1 - Não sou especialista em SQL então não sei te responder essa pergunta. Ele está com instalação default e pelo que observo no task manager todos os nucleos vão a 100% durante a execução de muitas consultas dessa query
    2 - Sql server 2005 
    3 - 1
    4 - Consigo.

    Analisando o plano de execução Basicamente está acusando 93% de uso com um table scan..

    Obrigado
    terça-feira, 21 de fevereiro de 2017 18:05
  • Rafael,

    Table Scan é um dos operadores existentes no plano de execução que devemos evitar com que ele seja apresentado, o mesmo indica a falta de índice clusterizado ou seja falta de uma chave primária na table que esta sendo varrida pelo Database Engine, identifique qual é esta tabela e adicione uma chave primária na mesma.

    Outro ponto importante, em alguns casos quando estamos trabalhando com Stored Procedure as variáveis "parâmetros" que estão sendo utilizados como elementos para passagem de valores podem acabar forçando um comportamento conhecido como parameter sniffing, quando o SQL Server é obrigado é fatiar o processamento em partes para tentar reconhecer o valor da variável, afim de evitar isso utilize a opção Optimize For no código fonte da sua Stored Procedure, bem como, a table hint Recompile.

    Veja se este exemplo ajuda:

    -- Criando o Banco de Dados 
    Create Database ParameterSniffing
    Go
    
    -- Criando a TabelaDados --
    Create Table TabelaDados
    (Id Int Identity Primary Key,
     Descricao Char(100),
     Localal Char(6),
     DataCriacao DateTime Default Getdate(),
     Status char(2) default 'AC')
    Go
    
    -- Criando Índice na coluna Local para TabelaDados --
    Create Index IND_Local on TabelaDados(Local)
    Go
    
    -- Inserindo massa de dados --
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalA')
    GO 100000
    
    Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalB')
    GO 10
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalA'
    Go
    
    -- Selecionando dados na TabelaDados com Local = 'LocalA' --
    Select * From TabelaDados
    Where Local = 'LocalB'
    Go
    
    -- Criando a Stored Procedure 
    Create Procedure ObterDadosPorLocal (@Local Char(6))
    As
    Begin
    
     Set NoCount ON
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    
    -- Obtendo informações sobre Plano de Execução de Stored Procedures --
    Select OBJECT_NAME(s.object_id) SP_Name,
           eqp.query_plan
    From sys.dm_exec_procedure_stats s CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
    Where DB_NAME(database_id) = 'ParameterSniffing'
    
    -- Recompilando a Stored Procedure --
    SP_Recompile 'ObterDadosPorLocal'
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal adicionando a opção Recompile --
    Alter Procedure ObterDadosPorLocal (@Local as Char(6)) With Recompile
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
    End
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, descartando o Cache do Plano de Execução --
    Exec ObterDadosPorLocal @Local= N'LocalA' 
    Exec ObterDadosPorLocal @Local= N'LocalB' 
    Go
    
    -- Executando novamente a Stored Procedure ObterDadosPorLocal, recompilando e utilizando o do Plano de Execução, sem fazer o armazenamento --
    Exec [dbo].[ObterDadosPorLocal] @Local = N'LocalA' WITH RECOMPILE
    Go
    
    -- Alterando a Stored Procedure ObterDadosPorLocal removendo a opção Recompile e adicionando a Query Hint Optimize --
    Alter Procedure ObterDadosPorLocal (@Local CHAR(6))
    As
    Begin
    
     Set NoCount On
    
     Select * From TabelaDados
     Where Local = @Local
     OPTION (OPTIMIZE FOR (@Local = 'LocalA'))
    
    End
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
    ObterDadosPorLocal 'LocalA'
    Go
    
    -- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
    ObterDadosPorLocal 'LocalB'
    Go
    


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 22 de fevereiro de 2017 18:37
  • Fabrício, bom dia.

    Já havia rodado query parecida e acusou sempre nesta query.

    Para confirmar, durante um stress teste eu simplesmente coloquei um return na primeira linha da SP e o processamento foi a zero.. Quando tirei o return subiu para 100% novamente.
    quinta-feira, 23 de fevereiro de 2017 12:42
  • Legal... Tem que tentar entender porque o sql não está usando o índice e esta fazendo um scan.

    Valida se o índice começa com a coluna mais seletiva da query.

    Fabrício França Lima MCITP - SQL Server Database Administrator Trabalho com SQL Server desde 2006 Treinamento DBA ONLINE: http://www.fabriciolima.net/blog/cursos-online/treinamento-tarefas-do-dia-a-dia-de-um-dba-online/

    quinta-feira, 23 de fevereiro de 2017 12:46
  • Junior, você acertou na mosca..

    Essa tabela estava sem chave primária mesmo. Foi inserido a chave e ele passou a usar o índice e agora ta rodando perfeito.

    Agora me tira uma dúvida.. porque que mesmo tendo um índice composto pelos mesmos campos da chave primária, ele não usava o indice até então ?

    Muito obrigado pela força.. ajudou muito !!!
    quinta-feira, 23 de fevereiro de 2017 13:19
  • Fabricio, depois da dica do Junior, fui ver que esta tabela foi criada sem chave primária.. Erro primário.

    Agora tá 100%.. Muito obrigado pela ajuda de vocês.
    quinta-feira, 23 de fevereiro de 2017 13:20
  • Show. Que bom que resolveu então.

    Seu índice tinha todas as colunas utilizadas na query?

    As vezes, quando o SQL faz um seek + Lookup , se retornar muitas linhas ele pode escolher fazer um scan...

    Mas ai para saber o motivo exato, só olhando os detalhes ai...


    Fabrício França Lima MCITP - SQL Server Database Administrator Trabalho com SQL Server desde 2006 Treinamento DBA ONLINE: http://www.fabriciolima.net/blog/cursos-online/treinamento-tarefas-do-dia-a-dia-de-um-dba-online/

    quinta-feira, 23 de fevereiro de 2017 13:32
  • Sim Fabrício, todas elas.
    Não sei porque o Sql não fazia o uso dele.
    quinta-feira, 23 de fevereiro de 2017 14:28
  • Rafael,

    Isso é normal e comum de acontecer, mesmo sendo um índice composto o mesmo vai ser utilizado somente quando todas as colunas que formam o índice estiverem sendo utilizadas e declaradas no select.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de fevereiro de 2017 19:50
  • Rafael,

    Tem certeza, você chegou a verificar no plano de execução? Um detalhe importante quando acontece parameter sniffing em diversas possibilidades o índice pode ser desconsiderado pelo plano de execução.


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 28 de fevereiro de 2017 19:51