Como remover um plano de execução específico do cache do SQL Server

Tudo está indo bem, o seu servidor de SQL está com baixa utilização de CPU, PLE alto, poucos índices fragmentados e as estatísticas atualizadas, quando de repente a utilização de CPU sobe para 100% e você nota que existem várias sessões de uma mesma aplicação executando a mesma consulta, que até então nunca tinha apresentado problema, demorando demais para retornar o resultado e estão bombardeando o SQL Server.

Screen Shot 2016-06-17 at 11.53.55 PM

Respire fundo. Muita calma. Há uma saída.

Primeiramente você precisa do conteúdo do campo sql_handle ou plan_handle da consulta que está com problemas para remover o seu plano de execução do cache.

Para capturar o sql_handle ou plan_handle da consulta você pode utilizar apenas uma DMV e uma DMFsys.dm_exec_query_statssys.dm_exec_sql_text. Você pode filtrar utilizando um trecho da consulta que você sabe que está com problemas e/ou até mesmo ordenar por um dos campos de total de utilização de CPU ou de tempo de duração, conforme exemplo abaixo:


SELECT TOP 10
execution_count,
total_elapsed_time / 1000 as totalDurationms,
total_worker_time / 1000 as totalCPUms,
total_logical_reads,
total_physical_reads,
t.text,
sql_handle,
plan_handle
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) as t
WHERE t.text LIKE '%linq%'
ORDER BY total_elapsed_time DESC

Screen Shot 2016-06-17 at 11.52.10 PM

Copie o conteúdo do campo plan_handle ou sql_handle da consulta exata que está com problemas.

Insira o conteúdo copiado dentro dos parênteses do comando DBCC FREEPROCCACHE, conforme exemplo abaixo:


DBCC FREEPROCCACHE (0x0600060020527003B0928B53CC01000001000000000000000000000000000000000000000000000000000000)

Screen Shot 2016-06-18 at 12.24.18 AM

Pronto! O plano de execução da sua consulta foi removido com sucesso do cache do SQL Server e provavelmente o seu servidor irá diminuir a alta utilização de recursos e voltar a responder normalmente. Depois deste susto procure melhorar o desempenho da sua consulta e entender o que fez com que o plano de execução tenha sofrido alteração.

Observações:

  1. Utilize este procedimento como uma solução de contorno e não como uma solução definitiva para o problema. Procure melhorar o desempenho da consulta e entender o que está fazendo com que o plano de execução sofra alteração ou tenha um desempenho ruim.
  2. Nunca utilize o comando DBCC FREEPROCCACHE sem nenhum conteúdo dentro dos parênteses, principalmente em ambientes de produção, pois isto irá remover TODOS os planos de execução do cache.

 

É isso galera! Espero que tenham gostado. Até a próxima!

Anúncios

Atraso no truncamento do Transaction Log: Snapshot Replication

Galera,

Algumas vezes podemos nos deparar com cenários aonde o Transaction Log de uma database configurada com Recovery Model FULL não para de crescer e o mesmo nunca é truncado, mesmo que os backups de transaction log ocorram com frequência. Este comportamento pode ocorrer por várias razões: transações ativas, backups/restores em andamento, replicação, entre outros motivos, os quais você pode conferir neste link.

Neste post vamos falar de um motivo específico no atraso do truncamento do log de transação: REPLICATION. Mais especificamente SNAPSHOT REPLICATION e de como aplicar uma possível solução de contorno para situações de emergência.

Para conferirmos o motivo da não reutilização do log de transação podemos sempre utilizar a coluna log_reuse_wait_desc da tabela de sistema sys.databases, conforme consulta abaixo:

01

Como podemos conferir na imagem abaixo o motivo do nosso log não ser truncado/reutilizado é devido a uma replicação configurada na instância do SQL Server.

02

Se esta replicação configurada for do tipo SNAPSHOT, seu log de transação estiver cheio, você estiver sem espaço em disco e não souber o que fazer, comece executando o comando abaixo na sua database:

03

Captura de Tela 2016-02-24 às 00.28.29

Se o resultado do comando DBCC OPENTRAN for semelhante ao retorno exibido acima há uma saída.

Na sua base de dados que está com o log de transação cheio e não pode ser reutilizado devido a replicação, conforme conferido anteriormente na coluna log_reuse_wait_desc execute o comando abaixo:

05

Após a execução deste comando as pendências de replicação devem estar resolvidas e o seu log de transação poderá ser reutilizado. Agora você deve garantir que a replicação está replicando todas alterações de estrutura para garantir a integridade da mesma.

Atenção: O comando sp_repldone deve ser utilizado apenas em situações de emergência. Esta não deve ser considerada como a solução definitiva do problema e sim apenas uma solução de contorno.

 

Referências:

https://msdn.microsoft.com/pt-br/library/ms173775(v=sql.120).aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

Atualizando uma coluna IDENTITY

Galera,

Recebi uma dúvida hoje: “É possível atualizar uma coluna IDENTITY de um registro específico?”
Respondendo: a atualização direta com a utilização do comando UPDATE não é permitida. Mas, se você realmente precisa realizar este procedimento por algum motivo específico, há uma solução de contorno bem simples.

Primeiro vamos criar o cenário para realizarmos o procedimento.

  • Criação da tabela:

Captura de Tela 2016-01-14 às 20.41.20

  • Adição de registros:

Captura de Tela 2016-01-14 às 20.41.25

  • Consulta:

Captura de Tela 2016-01-14 às 20.42.26

Agora vamos fazer uma tentativa de atualização da coluna id criada como IDENTITY na tabela TesteIdentity:

Captura de Tela 2016-01-14 às 20.45.47
Como podemos ver, ocorreu o erro 8102 “Cannot update identity column ‘id'”, pois não é possível atualizarmos diretamente um campo com a propriedade IDENTITY.
Para resolvermos este problema vamos precisar apenas de dois passos:

  1. Deletar o registro que queremos atualizar (Não se esqueça de fazer backup da sua tabela);
  2. Inserir o registro novamente com o seu novo valor para a coluna ID utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON.

Agora que já sabemos a solução, vamos tentar atualizar o mesmo registro do cenário criado anteriormente utilizando os passos acima.

  • Vamos excluir o registro com o valor de id = 1:

Captura de Tela 2016-01-14 às 20.46.24

  • E por último vamos inserir o registro com o seu novo valor para a coluna id utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON:

Captura de Tela 2016-01-14 às 20.47.23

O resultado é a “atualização” do id = 1 para id = 20, conforme abaixo:

Captura de Tela 2016-01-15 às 01.43.38

  • Se esquecermos de utilizar a cláusula SET IDENTITY_INSERT [nomeTabela] ON, o SQL Server irá retornar o erro 544 “Cannot insert explicit value for identity column in table ‘TesteIdentity’ when IDENTITY_INSERT is set to OFF“:

Captura de Tela 2016-01-15 às 01.35.58

IMPORTANTE:

  • Nunca se esqueça de retornar a cláusula IDENTITY_INSERT para OFF, pois o SQL Server permite que apenas uma tabela por vez utilize esta opção. Caso esqueça de desligar esta propriedade para uma tabela e tente ativar para uma segunda tabela o SQL Server irá retornar o erro abaixo:Captura de Tela 2016-01-15 às 01.43.07
  • Se o valor inserido for maior que a IDENTITY atual da tabela, automaticamente o SQL Server irá alterar o IDENTITY para o valor inserido, como podemos ver na imagem abaixo ao inserir um novo valor após termos inserido o valor com a cláusula SET IDENTITY_INSERT ON:

Captura de Tela 2016-01-15 às 01.49.38

Captura de Tela 2016-01-15 às 01.50.01

  • Se for necessário visualizar ou alterar o valor atual da IDENTITY podemos utilizar o comando DBCC CHECKIDENT. No nosso caso vamos primeiramente conferir o valor atual da IDENTITY, alterá-lo para 30 e por último inserir um novo registro na tabela.

Conferindo o valor atual da IDENTITY. Notem que o valor atual é 21.

Captura de Tela 2016-01-15 às 02.01.38

Alterando o valor da IDENTITY para 30:

Captura de Tela 2016-01-15 às 02.02.06

Conferindo novamente o valor atual da IDENTITY. Notem que agora o valor atual foi alterado para 30, conforme comando executado anteriormente. O valor 21 que ainda permanece é referente ao valor máximo da coluna IDENTITY existente na tabela:

Captura de Tela 2016-01-15 às 02.02.20

Inserindo um novo registro na tabela TesteIdentity:

Captura de Tela 2016-01-15 às 02.02.43

Como podemos ver após alterarmos o valor da IDENTITY para 30, o próximo valor inserido foi o 31, conforme evidência abaixo:

Captura de Tela 2016-01-15 às 02.03.28

Por hoje é isso galera! Se tiverem dúvidas, críticas ou sugestões de assuntos podem enviar pelos comentários ou até mesmo por e-mail: felipe@lauffer.me

Obrigado!