Quantificando o impacto da atualização no sistema

Olá galera, segue um script bem interessante que extrair da leitura do livro DMVs SQL Server em ação, escrito por Ian W. Stirk. É um script útil no dia a dia de muitos administradores de banco, pois nos auxilia a quantificar o impacto que uma atualização pode causar no ambiente de dados. O uso do script é bem simples, executa  a consulta antes de qualquer atualização, seja de substituição de versão do SQL Server ou um service pack, salvar a saida num arquivo excel  e após a atualização utiliza o mesmo script para comparar os resultados e compreender se a mudança foi benéfica ou prejudicial. Para os administradores que se preocupam em acompanhar as atualizações de seus servidores fica a dica.

Abraços!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #prework
FROM sys.dm_exec_query_stats

SELECT
total_elapsed_time, total_worker_time, total_logical_reads
, total_logical_writes, total_clr_time, execution_count
, statement_start_offset, statement_end_offset, sql_handle, plan_handle
INTO #postwork
FROM sys.dm_exec_query_stats

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid)

SELECT
SUM(p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0)) AS [TotalDuration]
, SUM(p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time on CPU]
, SUM((p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0))) AS [Total Time Waiting]
, SUM(p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0)) AS [TotalReads]
, SUM(p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0)) AS [TotalWrites]
, SUM(p2.total_clr_time – ISNULL(p1.total_clr_time, 0)) AS [Total CLR time]
, SUM(p2.execution_count – ISNULL(p1.execution_count, 0)) AS [Total Executions]
, DB_NAME(qt.dbid) AS DatabaseName
, qt.text AS [Parent Query]
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
GROUP BY DB_NAME(qt.dbid), qt.text
ORDER BY [TotalDuration] DESC

SELECT
p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0) AS [TotalDuration]
, p2.total_worker_time – ISNULL(p1.total_worker_time, 0) AS [Total Time on CPU]
, (p2.total_elapsed_time – ISNULL(p1.total_elapsed_time, 0))
– (p2.total_worker_time – ISNULL(p1.total_worker_time, 0)) AS [Total Time Waiting]
, p2.total_logical_reads – ISNULL(p1.total_logical_reads, 0) AS [TotalReads]
, p2.total_logical_writes – ISNULL(p1.total_logical_writes, 0) AS [TotalWrites]
, p2.total_clr_time – ISNULL(p1.total_clr_time, 0) AS [Total CLR time]
, p2.execution_count – ISNULL(p1.execution_count, 0) AS [Total Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END – p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #prework p1
RIGHT OUTER JOIN
#postwork p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [TotalDuration] DESC

DROP TABLE #prework
DROP TABLE #postwork

Anúncios

Habilitando notificação de mensagem 1205

Olá pessoal, férias acabando heim!! Espero que tenham descansado. Quero compartilhar com vocês uma solução interessante e simples sobre o não envio de notificação do evento de deadlock pelo database_email. Pra quem trabalha com gestão de ambiente de dados sabe que a ocorrência de deadlock gera a mensagem de erro 1205 no SQL Server. Recentemente recebi de um colega um email procurando saber porque o database_email não estava enviando as notificações de alerta de deadlocks para seu email. Inicialmente achei que o problema poderia ser na configuração database_email, no operador e em último caso, até no provedor do email dele. Verificada a configuração, e através de testes constatados que estava tudo bem configurado, iniciei uma pesquisa e encontrei um artigo bem legal de Michael K. Campbell que esclarece bem o fato e ensina como resolvermos o problema usando o comando abaixo :

EXEC master..sp_altermessage 1205, ‘WITH_LOG’, TRUE;

Mas nos perguntamos, pra que ser esse comando?  De acordo com a biblioteca da Microsoft “Altera o estado de mensagens definidas pelo usuário ou do sistema em uma instância do Mecanismo de Banco de Dados do SQL Server.” Porque devemos executá-lo para nossas notificações de mensagem 1205 possam chegar em nosso email? De acordo com a biblioteca da Microsoft “Se uma mensagem foi alterada para ser WITH_LOG, ela sempre será gravada no log de aplicativos do Windows, independentemente de como um usuário invocar o erro.”  Sendo gravada a mensagem no log do SQL Server é possível recebermos os alertas em nosso email.

Veja na figura abaixo a seleção da tabela de sistema sys.messages que mostra através da coluna is_event_logged a mensagem 1205 ativa.

Sem título

Só mais uma dica, é importante também verificar se o perfil de email está habilitado no sistema de alerta do SQL Agent, pois se essa opção não estiver habilitada as notifições de alerta não chegarão com êxito.

Bem pessoal, por hoje é só. Caso queiram conhecer um pouco mais sobre deadlock e os comandos usados é possível encontrar muitas informações na biblioteca da Microsoft e no de Michael K. Campbell nos links abaixo:

https://msdn.microsoft.com/pt-br/library/ms175094(v=sql.120).aspx https://technet.microsoft.com/pt-br/library/ms177433(v=sql.105).aspx http://sqlmag.com/blog/enabling-email-alerts-sql-server-deadlocks.

Até a próxima!

Exibindo espaço usado de uma base usando o conteúdo da procedure sp_spaceused

Olá galera, tudo bem?

Após alguns meses sem escrever nada aqui no blog, vou postar uma dica simples e bem legal. Necessitei recementemente coletar informações sobre o crescimento de uma base de dados de um ambiente. Geralmente a alternativa mais comum que a maioria dos profissionais de banco de dados usam é a procedure sp_spaceused, no qual de acordo com books online tem por objetivo: “Exibe o número de linhas, o espaço em disco reservado e o espaço em disco usado por uma tabela, exibição indexada ou fila Service Broker no banco de dados atual ou exibe o espaço em disco reservado e usado pelo banco de dados inteiro…”. É uma procedure formidável, porém quem já utilizou e utiliza sabe que o retorno são dois resultados com quantidade de colunas diferentes. A imagem abaixo explica melhor o retorno da consulta:

USE DBAdmin;
GO
EXEC sp_spaceused;
GO

resultado sp_spaceused

Figura 1. Resultado de procedure sp_spaceused.

Minha necessidade na coleta era de ter essas informações de forma individual, no qual pudesse também armazenar em tabelas para consultas posteriores. Então a ideia foi de subtrair o conteúdo da procedure e adaptar a minha realidade no momento. Utilizando a procedure sp_helptext foi possível obter o conteúdo e fazer as adaptações que seguem abaixo.

Script 1.:

set nocount on
declare
@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@pages bigint
,@objname nvarchar(776) = ‘DBAdmin’

select
@dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from
dbo.sysfiles

select
@reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from
sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select
[Banco] = @objname,
[Tamanho Banco MB] = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))* 8192 / 1048576,15,2)),
[Espaço Não Alocado MB] = ltrim(str((
case
when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) – convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2));
go

 

Resultado 1.:

seleção do espaço bd

Figura 2. Resultado de espaço utilizado do banco.

Script 2.:

set nocount on
declare
@id int
,@type character(2)
,@pages bigint
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
,@objname nvarchar(776) = ‘DBAdmin’

select
@reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from
sys.partitions p
join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
select
[Banco] = @objname,
[Reservado KB] = LTRIM (STR (@reservedpages * 8, 15, 0)),
[Dados KB] = LTRIM (STR (@pages * 8, 15, 0)),
[Tamanho Índice KB] = LTRIM (STR ((
CASE
WHEN @usedpages > @pages
THEN (@usedpages – @pages)
ELSE 0
END) * 8, 15, 0)),
[Não Usado KB] = LTRIM (STR ((
CASE
WHEN @reservedpages > @usedpages
THEN (@reservedpages – @usedpages)
ELSE 0
END) * 8, 15, 0));
go

Resultado 2.:

seleção do espaço indices e dados

Figura 3. Resultado de espaço dados e índices.

Bem pessoal, acho que por hoje é só. Caso queiram entender mais sobre a procedure sp_spaceused comentada neste post, é possível verificar na fonte abaixo. Lembrando que é possível adaptar os scripts para definir uma nova procedure, view, etc. Deixo essas alternativas de acordo com as necessidades de cada um.

Até a próxima!

(Visão geral sp_spaceused): https://msdn.microsoft.com/en-us/library/ms188776.aspx

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!

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!