Usuário com melhor resposta
Registro de acesso derrubando o servidor

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 ajudaalter 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
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]
- Marcado como Resposta Robson William Silva quinta-feira, 23 de fevereiro de 2017 20:01
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.NETWesley Neves
-
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/
- Sugerido como Resposta Robson William Silva quarta-feira, 22 de fevereiro de 2017 12:33
-
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 -
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]
- Marcado como Resposta Robson William Silva quinta-feira, 23 de fevereiro de 2017 20:01
-
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. -
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/
-
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 !!! -
-
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/
-
-
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]
-
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]