Erro com Page Level Locking na Manutenção de Índice

Olá pessoal, mais uma semana passando e feriado chegando, e essa como todas as outras muito corrida, de muitas atribulações e problemas para serem resolvidos. Convenço que curto muito essa agitação, pois é nesses momentos que surgem as oportunidades de aprendermos algo novo ou até mesmo aplicarmos conhecimentos e técnicas aprendidas.

Bem, não irei me estender muito, pois irei relatar uma situação simples vivida no final da semana passada. Recebi uma demanda para planejar e configurar em um novo ambiente rotinas de manutenções em dois bancos de dados.

No escopo do planejamento uma das atividades configuradas foi a de reorganização dos índices. Normalmente o fluxo definido pra está atividade são as de reorganização dos índices seguida da limpeza dos históricos e notificações por email da execução das tarefas se ocorrem com êxito ou teve falha na execução.  A figura abaixo ilustra melhor o fluxo que cito.

reorganizando indices

Não entrando nos detalhes do que cada tarefa acima faz, fui notificado pelo email que a tarefa de reorganização dos índices tinha falhado em um dos bancos de dados. Verificando os logs de erros do SQL Server Agent me deparei com a seguinte mensagem:

Executed as user: dominio\sqlagent. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 4:19:15 PM Error: 2015-04-19 16:20:25.27 Code: 0xC002F210 Source: Reorganize Index Task Execute SQL Task Description: Executing the query “ALTER INDEX [idx_indice_1] ON [dbo].[coluna_1…” failed with the following error: “The index “idx_indice_1” (partition 1) on table “tabela_1″ cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:19:15 PM Finished: 4:20:25 PM Elapsed: 69.654 seconds. The package execution failed. The step failed.

Analisando a mensagem do erro percebe-se que existe algum problema com o índice idx_indice_1, onde a mensagem mesmo diz “…Executing the query “ALTER INDEX [idx_indice_1] ON [dbo].[coluna_1…” failed with the following error: “The index “idx_indice_1” (partition 1) on table “tabela_1″ cannot be reorganized because page level locking is disabled.”, ou seja, ao executar a consulta foi identificado que o idx_indice_1 está com a opção de nível de bloqueio de página desativado. Aproveito e ressalto a importância que os logs de erro tem na resoluções de problemas. Diante de já ter a ideia de quem impediu a execução com êxito do plano de manutenção ficou mais fácil de encointrar uma solução.

A primeira coisa que fiz foi pesquisar se existia mais índices na mesma situação usando o script abaixo:

SELECT name,
       type_desc,
       fill_factor,
       allow_row_locks,
       allow_page_locks
FROM sys.indexes
WHERE allow_page_locks = 0
  AND allow_row_locks = 0

Podemos usar também o script abaixo pra saber a situação especifica de um índice:

SELECT name,
       type_desc,
       fill_factor,
       allow_row_locks,
       allow_page_locks
FROM sys.indexes
WHERE name = 'idx_indice_1'

Através do SSMS:

propriedades indices

Os resultados mostraram que o índice em questão não estava podendo ser bloqueado para executar a operação configurada. Levemos em consideração que a configuração padrão na reorganização de índices é definida com compactção de objetos grandes (LOB) habilitada que necessita de bloqueio. Figura abaixo mostra a opção habilitada na tarefa.

configuração reorganização

Analisado os efeitos das soluções, feitos alguns testes foi possível executar com segurança o scritp de ajuste e resolver o problema na execução do job de manutenção do banco.

USE [base_dados]
GO
ALTER INDEX [idx_indice_1] ON [dbo].[tabela_1] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO

Quem quiser saber mais detalhes sobre compactação de objetos, bloqueios a nível de linha e página, bloqueios escalonados, dentre outros, tem tudo no Book Online da Microsoft. Lá explica detalhadamente o uso de cada opção e mostra exemplos usando o  banco AdventureWorks.

Espero ter ajudado.

Até a próxima!

Anúncios

Descobrindo a memória usada pelos bancos de dados de uma instância SQL Server

Olá pessoal, vou abrir essa semana com um assunto bem recorrente e comum no que diz respeito a desempenho de servidores de banco de dados. É uma situação que trás algumas inquietações para o time de banco de dados de qualquer organização, mas que se forem usadas as técnicas de monitoramento junto com as DVMs é possível identificar rapidamente onde está o problema e resolvê-lo. Eu particularmente costumo utilizar algumas premissas quando sou informado de algum problema no servidor SQL Server, que consistem na:

· identificação e definição do problema para isolar o mesmo;

· verificação de todos os logs e scripts;

· testes;

· definição de soluções;

· documentação de todo o processo.

Dentro destes parêmetros citados é possível determinar uma correção eficiente e segura, além também de documentar todo fluxo que foi usado para resolver tal problema.

Ressalto que se seguirmos as práticas metodológicas de Troubleshooting teremos maiores chances de obtermos êxito na aplicação das corretivas do ambiente em análise.

Estou esclarecendo esses pontos antes e não indo direto ao assunto, porque muitos profissionais ainda esquecem de seguir esses passos e muita das vezes, tomam decisões sem consciência dos efeitos que podem causar no ambiente.

Recentemente fui questionado pelo time de infraestrutura que um de meus ambientes estava com o serviço de SQL Server consumindo muita memória física. Achei um pouco estranho, pois os usuários não tinham registrado queixa nenhuma de lentidão na aplicação, ao contrário, informaram que o sistema estava funcionando perfeitamente.

Então para ter certeza que tudo estava bem mesmo e dar um retorno do que foi levantado pelo time de infraestrutura, iniciei uma análise no ambiente, no qual, em um dos passos da minha análiase utilizei a DVM sys.dm_os_buffer_descriptors. Essa DVM é responsável de retornar informações sobre todas as páginas de dados atualmente no pool de buffer do SQL Server.

Veja um exemplo abaixo de retorno de resultado da DMV sys.dm_os_buffer_descriptors:

select *
from sys.dm_os_buffer_descriptors

resultado 1

A consulta acima está bem genérica e um pouco dificil de compreender o resultado retornado. Veremos adiante um consulta mais bem formulada que facilitará a compreensão do estudo dos dados.

Bem, seguindo o entendimento da DMV sys.dm_os_buffer_descriptors, o meu intuito era deterninar a quantidade de memória que cada banco de dados estava alocando na área de buffer.  Assim, poderia determinar se algum banco de dados da instância monopolizava o pool de buffer. Utilizei o script abaixo, extraido do livro DVMs SQL Server em Ação, onde, de ante mão, sugiro a todos que trabalham com banco de dados terem em sua cabeçeira.

SET TRAN ISOLATION LEVEL READ UNCOMMITTED

SELECT
ISNULL(DB_NAME(database_id), ‘ResourceDb’) AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

resultado 2

Seguindo as práticas de Troubleshooting, o resultado acima, juntamente com outros dados coletados, me ajudou a determinar e provar pra o time de infraestrutura que não existia problema de memória no servidor.

Espero ter ajudado.

Até a próxima!

Usando o comando SET IDENTITY_INSERT para preencher lacunas deixadas pelo comando DELETE.

Olá pessoal, vou abrir o ano de 2015 com uma dica simples, a qual, em algum momento vai ser providencial ao time que cuida do ambiente de dados de qualquer organização. Já necessitei usar a dica que irei postar aqui, então é uma obrigação compartilhar com vocês essa experiência. Sei que muitos iniciantes e talvez até experientes na área de banco de dados podem não conhecer a opção SET IDENTITY_INSERT, mas farei um breve resumo dizendo o que significa e para que serve.

Quem não conhece a propriedade IDENTITY? Essa é uma propriedade importante no SQL Server e na modelagem de dados, pois é através dela que garantimos o auto incremento, ou seja, permite que um número único possa ser gerado quando um novo registro é inserido em uma tabela.

Não irei entrar em muitos detalhes, nem nos méritos de outras propriedades e estruturas que podem ser modeladas em um banco de dados que atenderia os mesmos resultados da propriedade IDENTITY, pois fugiria do objetivo do post. O intuito é de mostrar o uso da opção SET IDENTITY_INSERT na prática e cada um interpretar a melhor maneira de usar em seu ambiente caso surja a necessidade.

Bem, vamos deixar de conversa fiada e irmos direto ao assunto.

A opção SET IDENTITY_INSERT em sua essência significa, de acordo com a Microsoft – comando que permite inserir valores explícitos na coluna de identidade de uma tabela. Normalmente quando temos na tabela uma coluna com a propriedade IDENTITY definida, não é possível inserimos registros de forma direta naquela coluna declarando qual o valor que queremos como ID.

Veja no exemplo do comando abaixo:

INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’); GO

É neste momento que se faz o uso do comando SET IDENTITY_INSERT. Seguindo o exemplo acima seria possível obtermos o resultado esperado 3 para a coluna ID habilitando a opção para ON.

Veja no exemplo do comando abaixo:

SET IDENTITY_INSERT dbo.Tool ON; GO INSERT INTO dbo.Tool (ID, Name) VALUES (3, ‘Garden shovel’); GO SELECT * FROM dbo.Tool; GO

Seguindo os passos apresentados seria possível incrementar o valor 3 tranquilamente e sem muita dificuldade. Só lembrando que é necessário depois, desabilitar a opção após o uso.

Só pra deixar claro qual o cenário que precisei utilizar o comando, foi para o preenchimento na tabela de uma lacuna nos valores de identidade causados por uma instrução DELETE. Com o uso dessa opção mantive a tabela do banco de dados organizada.

Espero ter ajudado.

Até a próxima!