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

SQL Server Storage: Como visualizar o tamanho dos blocos de todos os volumes

Em certas ocasiões durante a instalação de uma nova instância de SQL Server ou até mesmo para revisar um ambiente precisamos verificar todos os volumes dos nossos servidores para obter os tamanhos de blocos de cada um por motivos de desempenho do SQL Server ou apenas para revisar as configurações de storage do SQL Server.

IMPORTANTE: Lembre-se sempre de formatar os seus discos em blocos de 64K para melhor desempenho do SQL Server.

Vou mostrar pra vocês uma alternativa simples para obter o tamanho do bloco de todos os volumes existentes em um servidor.

Em vez de executar o comando “fsutil fsinfo ntfsinfo C:” para cada volume e ficar olhando para a linha “Bytes per cluster”, você só precisa executar o script abaixo.

Abra uma nova janela do Powershell como administrador e digite o seguinte script:

Captura de Tela 2016-03-31 às 21.40.01

Para facilitar você pode fazer o download deste script no link abaixo da TechNet Gallery:

https://gallery.technet.microsoft.com/PowerShell-Get-Volumes-ac89376b

Após a execução do script você terá uma lista completa de todos os seus volumes e o tamanho do bloco de cada um:

Captura de Tela 2016-03-31 às 19.32.39

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

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

Script para listar arquivos e tamanhos por database

Primeiro script publicado na galeria da Technet para SQL Server.
Baixem, confiram e avaliem!

https://gallery.technet.microsoft.com/MSSQL-List-files-and-sizes-e35197a6

Este script T-SQL retorna o caminho e tamanho (total, usado e disponível) dos arquivos de dados primários, secundários e de log de transações de todas as bases de dados da sua instância do SQL Server.

Este script é útil quando você precisar a quantidade total de espaço em disco de todos os bancos de dados de todos os servidores registrados no SQL Server Management Studio.

Você pode optar por usá-lo também quando você precisa salvar tamanhos históricos de seus arquivos de banco de dados para futuramente saber o quanto a sua base de dados está crescendo por dia, semana, mês e ano.

Como utilizar o recurso Registered Servers no SQL Server Management Studio

Galera,
Sejam bem-vindos! Vamos ao primeiro post com uma dica básica, mas que pode ajudar muito no dia-a-dia e na organização de um DBA.

Dependendo da quantidade de servidores administrados, fica cada vez mais difícil de manter a organização e também de não esquecer daquele SQL Server de criticidade baixa que quase nunca é utilizado. Para resolver esse problema, vou mostrar um recurso bem simples do SQL Server que auxilia na organização dos servidores existentes.

O recurso é chamado de Registered Servers (Servidores Registrados). Para acessar o recurso no SQL Server Management Studio, você pode ir até o menu View > Registered Servers ou utilizar as teclas de atalho Ctrl + Alt + G:

Captura de Tela 2016-01-11 às 22.24.25

Isto irá habilitar a aba de servidores registrados no canto esquerdo da janela do Management Studio:

Captura de Tela 2016-01-11 às 22.41.12

Acessando esta nova aba de servidores registrados temos duas opções: Armazenar a lista de servidores de forma local (perfil do usuário) ou em um servidor específico de forma centralizada.

Captura de Tela 2016-01-11 às 22.26.02

Recomendo o armazenamento em um servidor centralizado. Desta forma, independente do cliente que você esteja utilizando, você pode se conectar a este servidor e ter a sua lista de servidores pronta e disponível. A única limitação que temos no caso do servidor centralizado é que todos os servidores que iremos registrar devem utilizar a forma de autenticação Windows Authentication. Se você possuir um servidor que só tenha acesso com autenticação SQL, deve optar pelo armazenamento local (Local Server Groups).

Para cadastrar um servidor de gerenciamento centralizado, devemos clicar com o botão direito de Central Management Servers e navegar até a opção Register Central Management Server.

Captura de Tela 2016-01-11 às 22.41.32

Na nova janela aberta devemos apenas informar o nome do servidor que irá centralizar a lista de servidores existentes no ambiente.

Captura de Tela 2016-01-11 às 22.26.55

Com o servidor central configurado podemos iniciar a organização da lista dos servidores de SQL Server criando grupos e registrando os servidores abaixo destes grupos.

Para criar um novo grupo podemos navegar até o servidor central, clicar com o botão direito em cima dele e escolher a opção New Server Group e por fim informar um nome e uma descrição para o novo grupo.

Captura de Tela 2016-01-11 às 22.41.43

Captura de Tela 2016-01-11 às 22.28.48

Captura de Tela 2016-01-11 às 22.29.07

Para registrar um servidor abaixo de um grupo podemos navegar até o grupo criado, clicar com o botão direito em cima dele, navegar até a opção New Server Registration, e por fim informar o nome do servidor e suas credenciais de acesso.

Captura de Tela 2016-01-11 às 22.42.10

Captura de Tela 2016-01-11 às 22.32.55

Com isso você pode ir cadastrando todos os seus servidores de SQL e organizá-los por tipo de ambiente, localização, versões, etc.

Captura de Tela 2016-01-11 às 22.35.27

É possível também exportar os servidores registrados para um arquivo para serem usados posteriormente ou importados em outro servidor. Para exportar um grupo de servidores clique com o botão direito em cima do grupo e navegue até o item Tasks > Export, em seguida escolha o local e o nome do arquivo de destino.

Captura de Tela 2016-01-11 às 23.45.33

Captura de Tela 2016-01-11 às 23.47.34

Captura de Tela 2016-01-11 às 23.48.53

Com isso o grupo de servidores foi exportado com sucesso. Tá mas e agora como importa em outro servidor?

Praticamente do mesmo jeito. Clicando com o botão direito em cima do servidor de gerenciamento, navegando até o item Tasks > Import e selecionando o arquivo criado na exportação.

Captura de Tela 2016-01-11 às 23.53.04

Captura de Tela 2016-01-11 às 23.52.38

Captura de Tela 2016-01-11 às 23.52.45

Um benefício bacana que o recurso Registered Servers proporciona é realizar uma consulta em um grupo de servidores de uma forma bem simples. Clicando com o botão direito em cima de um grupo de servidores ou até mesmo do servidor de gerenciamento central e navegando até o item New query podemos executar uma consulta ao mesmo tempo em todos os servidores de um grupo.

Captura de Tela 2016-01-11 às 23.02.02

Veja abaixo dois exemplos de uma consulta feita em cima do grupo PRD e outra em cima do servidor de gerenciamento central utilizando a função SERVERPROPERTY().

Captura de Tela 2016-01-11 às 22.54.38

Captura de Tela 2016-01-11 às 22.55.16

É isso pessoal. Espero que tenham gostado!

Em breve…

Em breve conteúdo sobre SQL Server.