quinta-feira, 28 de julho de 2016

Problemas com o xp_sendmail

Hoje, deparei com um problema corriqueiro em um dos servidores SQL Server 2005 de um cliente.

xp_sendmail: failed with mail error 0x80040115

xp_sendmail: failed with mail error 0x80040000

Vamos lá!

Como ninguém sabe tudo precisei recorrer ao Books online da Microsoft e encontrei as referências [1] [2] que auxiliaram no entendimento e consequentemente na solução do problema.

[1] https://support.microsoft.com/en-us/kb/555180

[2] https://support.microsoft.com/en-us/kb/833045

Funcionamento

O SQL Server implementa o serviço de SQL Mail. E para cada sessão aberta do cliente os componentes MAPI façam o logon no servidor de email, conforme a configuração estipulada no profile do SQL Mail.

Quando são identificadas falhas no envio das mensagens é comum que os DBAs menos atualizados reiniciem o serviço do SQL Server, sem necessidade. É claro que após a reinicialização do serviço vai funcionar. Como ninguém sabe tudo precisei recorrer ao Books online da Microsoft e encontrei as referências [1] [2] que auxiliaram no entendimento e consequentemente na solução do problema.

Esse recurso que utilizei será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam para os novos recursos implementados pela Microsoft no SQL Server.

Solução simples:

exec master.dbo.xp_stopmail

EXEC master.dbo.xp_sendmail

@recipients=’andersonabreu@gmail.com’,

@message=’Corpo do email teste’,

@subject=’Assunto: teste de envio de email’

Ao realizar o teste, houve um momento que foi necessário iniciar o serviço.

Se você usar xp_startmail para iniciar as sessões de e-mail, poderá fornecer opcionalmente seu nome de login e senha para que não tenha de digitá-los no prompt de comando. Entretanto, o SQL Mail não será executado em uma sessão MAPI existente se houver uma em execução.

exec master.dbo.xp_startmail

EXEC master.dbo.xp_sendmail

@recipients=’andersonabreu@gmail.com’,

@message=’Corpo do email teste’,

@subject=’Assunto: teste de envio de email’

quarta-feira, 20 de julho de 2016

Indexação e Fragmentação (SQLServer)

Índices

Hoje, abordarei sobre um tema que para muitos parece ser um mito e algo temoroso. Contudo, tentarei abordar de forma simples

Não é difícil nos depararmos com estruturas de tabelas mal definidas, com tipos de dados que podem ser melhor definidos, tabelas sem índices e consultas SQLs mal escritas.

Um bom time de DBAs tem como uma de suas premissas a execução de procedimentos rotineiras, a maioria deles agendado, para uma boa saúde do banco de dados. Um destes procedimentos é a indexação/desfragmentação de índices.

Os índices são utilizados para potencializar as operações de buscas em banco de dados. Índices bem definidos podem melhorar de forma exponencial o acesso aos dados gravados em disco, reduzindo drasticamente a varredura desnecessária dentro do disco e da estrutura de arquivos e páginas dos blocos de índice.

Recentemente, fui solicitado em uma consultoria para melhorar o desempenho de um GED.

Vamos lá!

As atividades foram executadas em um servidor com Microsoft SQL Server 2008 R2.

Procedimentos

Resolvi analisar o percentual de fragmentação das tabelas.

Observei diversos índices com um alto percentual de fragmentação. Para analisar a fragmentação criei uma stored procedure para auxiliar nos dados, conforme estrutura a seguir:

Stored Procedure utilizada para analisar o percentual de fragmentação:

CREATE PROCEDURE SP_ESTATISTICA_FRAGMENTACAO

BEGIN

CREATE TABLE #fragmentacao(

object_id integer,

tabela VARCHAR(128),

index_id integer,

nome_indice VARCHAR(500),

percentual_fragmentacao numeric(18,6))

DECLARE @Tabela VARCHAR(128)

DECLARE @catalogo VARCHAR(128)

DECLARE Tabelas CURSOR FAST_FORWARD FOR

SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

OPEN Tabelas

FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #fragmentacao

SELECT b.object_id, @tabela, a.index_id, b.name nome_indice, avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (DB_ID(@catalogo), OBJECT_ID(@tabela), NULL, NULL, NULL) AS a

JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela

END

CLOSE Tabelas

DEALLOCATE Tabelas

SELECT * FROM #fragmentacao

DROP TABLE #fragmentacao

END

exec SP_ESTATISTICA_FRAGMENTACAO

Resolvi aprofundar um pouco mais nas consultas e recuperei para cada tabela o espaço consumido por Dados, Índices, Reservado, Não utilizado e o Número de tuplas.

Para isso fiz a seguinte view:

View para análise de dados das tabelas:

CREATE VIEW VW_ESTATISTICA_TABELA

SELECT OBJECT_NAME(object_id) As Tabela, Rows As Linhas,

SUM(Total_Pages * 8) As Reservado,

SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados,

SUM(Used_Pages * 8) -

SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice,

SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado

FROM

sys.partitions As P

INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id

where OBJECT_NAME(object_id) not like 'sys%' and

OBJECT_NAME(object_id) not like 'ifts%' and

OBJECT_NAME(object_id) not like 'queue%' and

OBJECT_NAME(object_id) not like 'fulltext%' and

OBJECT_NAME(object_id) not like 'frag%' and

OBJECT_NAME(object_id) not like 'file%'

GROUP BY OBJECT_NAME(object_id), Rows

SELECT * FROM VW_ESTATISTICA_TABELA ORDER BY linhas desc

Feitas as devidas verificações passei para o passo seguinte que foi gerar os dados do antes da desfragmentação e reindexação das tabelas.

Em algumas tabelas só a desfragmentação não foi suficiente e foi necessária a reindexação.

Veja os códigos utilizados:

-- COMANDO UTILIZADO PARA REINDEXAR A TABELA

DBCC DBREINDEX ('nome_tabela');

-- COMANDO PARA RECONSTRUIR O INDICIE

ALTER INDEX nome_indice ON nome_tabela REBUILD;

-- COMANDO PARA DESFRAGMENTAR TODOS OS INDICES DA TABELA

ALTER INDEX ALL ON ACS_CIDADE REORGANIZE ;

-- COMANDO PARA DESFRAGMENTAR INDIVIDUALMENTE CADA ÍNDICE

ALTER INDEX idx_acsCidade_idEstado ON ACS_CIDADE REORGANIZE ;

Monitoramento PostgreSQL

Sabemos que existem diferentes formas de monitorar as atividades no SGBD PostgreSQL.

Recentemente, fui solicitado em uma consultoria de PostgreSQL, para uma aplicação escrita em java utilizando hibernate (com muuuiiiittttooooos HQLs), foi solicitado que verificasse os possíveis pontos de gargalo no servidor de banco de dados.

Afinal, “o problema sempre (ou quase) é no banco de dados” (Pensamento popular quando o banco de dados “aparentemente” está lento).

Vamos lá!

Verifiquei os parâmetros de configuração do postgresql.conf em conjunto com os parâmetros de configuração do Sistema Operacional (Redhat 7).

“Excelentes” DBAs já haviam feito um “excelente” trabalho de tuning e comprovei que os parâmetros de configuração estavam ajustados para a melhor performance de utilização do SGBD, pelo menos para aquela realidade.

Procedimentos

Investigando mais a fundo o motivo, identificamos que algumas transações, oriundas da aplicação, estavam dentro de transações que aguardavam muito tempo por uma ação do usuário, isso quando não perdiam a sessão e a aplicação simplesmente não fechava a conexão. Com isso, foi inevitável o aumento de requisições para novas conexões ativas no SGBD.

Bingo! Problema na aplicação que foi mal escrita.

Os procedimentos a seguir foram realizados em um servidor Postgres 9.3.10, em Julho/2016, em dois dias de monitoramento.

É claro que existem outras formas de realizar esse monitoramento!!!

Ao consultar a view pg_stat_activity, observei que haviam muitas requisições cujo estado era idle_in_transaction.

O que precisava ser feito? Capturar essas queries e reportar para que a equipe de desenvolvimento pudesse agir pontualmente e buscar uma solução definitiva para o problema.

Consulta utilizada para buscar atividades no SGBD com o estado idle_in_transaction:

SELECT pid, datname, usename, client_addr, query_start, state_change, state, query

FROM pg_stat_activity

WHERE state = 'idle_in_transaction';

Criação de estrutura no banco de dados para coleta dos dados:

-bash-4.1$ psql

Password:

psql.bin (9.2.4)

Type "help" for help.

[?1034hpostgres=#

postgres=#

postgres=# CREATE ROLE usr_monitoramento LOGIN ENCRYPTED PASSWORD 'md53fed9f02a9f95c16b62bf2f718ce74b0' VALID UNTIL 'infinity';

CREATE ROLE

postgres=#

postgres=# CREATE DATABASE bd_monitoramento WITH ENCODING='UTF8' OWNER=usr_monitoramento CONNECTION LIMIT=-1;

CREATE DATABASE

postgres=#

postgres=# CREATE TABLE atividades (

postgres=# pid integer,

postgres=# datname name,

postgres=# usename name,

postgres=# client_addr inet,

postgres=# query_start timestamp with time zone,

postgres=# state_change timestamp with time zone,

postgres=# state text,

postgres=# query text

postgres=#);

CREATE TABLE

postgres=#

postgres=# ALTER TABLE atividades OWNER TO usr_monitoramento;

ALTER TABLE

postgres=#

postgres=# CREATE TABLE bloqueio (

postgres=# hora_atual timestamp with time zone,

postgres=# pid_bloqueado integer,

postgres=# hora_inicio_bloqueada timestamp with time zone,

postgres=# hora_mudanca_estado_bloqueada timestamp with time zone,

postgres=# usuario_bloqueado name,

postgres=# querie_bloqueada text,

postgres=# aplicacao_bloqueada text,

postgres=# pid_bloqueio integer,

postgres=# hora_inicio_bloqueio timestamp with time zone,

postgres=# hora_mudanca_estado_bloqueio timestamp with time zone,

postgres=# usuario_bloqueio name,

postgres=# querie_bloqueio text,

postgres=# aplicacao_bloqueio text

postgres=#);

CREATE TABLE

postgres=#

postgres=# ALTER TABLE bloqueio OWNER TO usr_monitoramento;

ALTER TABLE

Com a consulta a seguir, pude extrair as queries mais frequentes com estado idle_in_transaction:

SELECT pid, query, COUNT(0) qtde INTO atividades_agrupadas FROM atividades GROUP BY pid, query ORDER BY 3 DESC;

Consulta para capturar as possíveis requisições que estão bloqueando e que são bloqueadas:

SELECT

now() hora_atual,

atividade_bloqueada.pid pid_bloqueado,

atividade_bloqueada.query_start hora_incio_bloqueada,

atividade_bloqueada.state_change hora_mudanca_estado_bloqueada,

atividade_bloqueada.usename usuario_bloqueado,

atividade_bloqueada.query querie_bloqueada,

atividade_bloqueada.application_name aplicacao_bloqueada,

bloqueio.pid pid_bloqueio,

atividade_bloqueio.query_start hora_incio_bloqueio,

atividade_bloqueio.state_change hora_mudanca_estado_bloqueio,

atividade_bloqueio.usename usuario_bloqueio,

atividade_bloqueio.query querie_bloqueio,

atividade_bloqueio.application_name aplicacao_bloqueio

FROM pg_catalog.pg_locks bloqueados

JOIN pg_catalog.pg_stat_activity atividade_bloqueada ON atividade_bloqueada.pid = bloqueados.pid

JOIN pg_catalog.pg_locks bloqueio

ON bloqueio.locktype = bloqueados.locktype

AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE

AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation

AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page

AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple

AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid

AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid

AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid

AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid

AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid

AND bloqueio.pid != bloqueados.pid

JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid

WHERE NOT bloqueados.granted;

Criação do arquivo monitoramento.sh:

#!/bin/sh

# Coleta de dados PG_STAT_ACTIVITY e BLOQUEIO

# Data de criacao: 2016-07-01

# Criado por: Anderson Abreu

# E-mail: andersonabreu@gmail.com

export PGPASSWORD="edb2013"

INICIO="Inicio: "

FIM="Final: "

D=$(date '+%d/%m/%Y %R:%S')

echo $INICIO$D >> /tmp/log_atividades.txt

/opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into atividades select pid, datname, usename, client_addr, query_start, state_change, state, query from pg_stat_activity where state = 'idle_in_transaction';" >> /tmp/log_atividades.txt

/opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into bloqueio SELECT

now() hora_atual,

atividade_bloqueada.pid pid_bloqueado,

atividade_bloqueada.query_start hora_incio_bloqueada,

atividade_bloqueada.state_change hora_mudanca_estado_bloqueada,

atividade_bloqueada.usename usuario_bloqueado,

atividade_bloqueada.query querie_bloqueada,

atividade_bloqueada.application_name aplicacao_bloqueada,

bloqueio.pid pid_bloqueio,

atividade_bloqueio.query_start hora_incio_bloqueio,

atividade_bloqueio.state_change hora_mudanca_estado_bloqueio,

atividade_bloqueio.usename usuario_bloqueio,

atividade_bloqueio.query querie_bloqueio,

atividade_bloqueio.application_name aplicacao_bloqueio

FROM pg_catalog.pg_locks bloqueados

JOIN pg_catalog.pg_stat_activity atividade_bloqueada ON atividade_bloqueada.pid = bloqueados.pid

JOIN pg_catalog.pg_locks bloqueio

ON bloqueio.locktype = bloqueados.locktype

AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE

AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation

AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page

AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple

AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid

AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid

AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid

AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid

AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid

AND bloqueio.pid != bloqueados.pid

JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid

WHERE NOT bloqueados.granted;" >> /tmp/log_atividades.txt

D=$(date '+%d/%m/%Y %R:%S')

echo $FIM$D >> /tmp/log_atividades.txt

Agendamento da rotina:

Agendei a execução para ocorrer a cada minuto para termos o máximo de dados e suas possíveis repetições.

# crontab -e

* * * * * /tmp/monitoramento.sh

Report para equipe de desenvolvimento

Feita a coleta de dados, foi realizada a exportação dos dados para um arquivo no formato CSV para que fosse possível a análise da equipe de desenvolvimento.

copy atividades_agrupadas to '/tmp/atividades_agrupadas_idle_in_transaction.csv' DELIMITER ';' CSV HEADER;

copy bloqueio to '/tmp/bloqueio.csv' DELIMITER ';' CSV HEADER;