USANDO O SSRS PARA MONITORAR CONTENÇÃO DE I/O NO SQL SERVER

Olá Pessoal!

Feliz Ano Novo…Para começar bem o ano de 2018, vou compartilhar um relatório muito útil no trabalho do dia a dia de monitoramento do ambiente, um dashboard de monitoramento das contenções de I/O das instâncias SQL Server, com comparação de quantidade de notificações por semana.

Para criação do relatório foi utilizado o Reporting Services 2016 e consultas na tabela do error log para filtrar as informações. No ambiente que foi implantado essa solução, temos uma instância que extrai as informações necessárias para relatórios de todas as demais instâncias e dessa forma é possível consolidar os dados em apenas um repositório, utilizando para consultas, nesse caso os dados da tabela de error log.

Siga as etapas abaixo:

1 – Nas instâncias que deseja coletar as informações, a consulta abaixo deve ser utilizada em cada arquivo do error log.

INSERT INTO tb_IOWarningResults
EXEC xp_readerrorlog 0, 1, N'taking longer than 15 seconds';

No exemplo acima estou inserindo os dados de apenas um arquivo do error log em determinada tabela, sendo que, a busca filtra apenas a string com a mensagem enviada ao SQL Server. A mensagem que está sendo buscada é a seguinte:

Exemplo:

SQL Server has encountered x occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\MSSQL\MSSQL.1\MSSQL\Data\data.mdf] in database [database]. The OS file handle is 0x00000000. The offset of the latest long I/O is: 0x00000000000000

Não vou me aprofundar no assunto, mas basicamente a mensagem informa que o subsistema de disco não está operando de forma satisfatória e alguma operação está sendo impactada devido ao tempo de resposta de IO acima de 15 segundos.

O artigo a seguir auxilia no troubleshooting e entendimento dessa contenção de IO: https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/09/troubleshooting-sql-server-io-requests-taking-longer-than-15-seconds-io-stalls-disk-latency/

2 – Uma vez que os dados foram inseridos em tabelas (temporárias ou não) em cada instância, seja através de uma chamada remota ou agendamento (job), é necessário fazer a leitura das tabelas e para essa tarefa eu utilizei uma View intermediária para consolidar todas as consultas com Linked Server, veja.

CREATE VIEW [dbo].[VW_MONITOR_LOG_EVENT]
AS

SELECT 'HOST1' AS SERVER,LogDate AS LogDate, LogText COLLATE Latin1_General_CI_AI AS LogText
FROM [HOST1].master.dbo.tb_IOWarningResults

UNION ALL

SELECT 'HOST2' AS SERVER,LogDate AS LogDate, LogText COLLATE Latin1_General_CI_AI AS LogText
FROM [HOST2\MSSQLSERVER01].master.dbo.tb_IOWarningResults

3 – Após consolidar os resultados em um grande “tabelão”, apliquei alguns tratamentos nos dados criando uma nova view, veja.

CREATE VIEW [dbo].[VW_VIEW_LOG_EVENT_INFORMATION]
AS
SELECT [SERVER],convert(date,[LogDate]) as [LogDate],
substring(logtext,charindex('I/O',logtext,1),54) as [LogText],
CONVERT(BIGINT,LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1)) as QTD_OCORRENCIAS
FROM (
SELECT [SERVER],convert(date,[LogDate]) as [LogDate],[LogText],subsrt = SUBSTRING(logtext, pos, LEN(logtext))
FROM (
SELECT [SERVER],convert(date,[LogDate]) as [LogDate],[LogText], pos = PATINDEX('%[0-9]%', logtext)
FROM VW_MONITOR_LOG_EVENT with (nolock)
) d
) t

4 – Com o conjunto de dados obtido é possível montar uma série de relatórios diferentes, mas eu optei por fazer uma comparação entre a semana atual e semana anterior, agregando os valores por instância, sendo assim, a consulta abaixo foi utilizada.

SELECT VW.[SERVER],CONVERT(VARCHAR(92),VW.[LogText]) AS [LogText],
SUM(VW.QTD_OCORRENCIAS) AS QTD_OCORRENCIAS_ULTIMOS_7_DIAS,
VW2.QTD_OCORRENCIAS_SEMANA_ANTERIOR
FROM MASTER.DBO.VW_VIEW_LOG_EVENT_INFORMATION AS VW
JOIN (SELECT [SERVER],SUM(QTD_OCORRENCIAS) AS QTD_OCORRENCIAS_SEMANA_ANTERIOR
FROM MASTER.DBO.VW_VIEW_LOG_EVENT_INFORMATION
WHERE LogDate > convert(date,getdate()-14)
and LogDate convert(date,getdate()-7)
GROUP BY VW.[SERVER],VW.[LogText],VW2.QTD_OCORRENCIAS_SEMANA_ANTERIOR

O resultado esperado deve ser como nesse exemplo abaixo.

No próximo passo iniciarão etapas a serem realizadas no Reporting Services.

5 – Vou pular as etapas de configuração inicial do Reporting Services e avançar para criação de um novo Data Source dentro do Reporting Services que irá conectar na instância “central” que contém as consultas montadas anteriormente. Veja o exemplo abaixo, onde uma simples conexão com o SQL Server é criada.

6 – Deve ser criado na sequencia o Data Set que irá conter a consulta com o conjunto de dados que será utilizado relatório. Através da pagina web do Reporting Services o Report Builder será iniciado, como pode ser visto na imagem a seguir.

7 – Em vez de um tradicional relatório paginado, criaremos um Mobile Report. Para aqueles que não conhecem esse recurso, ele é otimizado para dispositivos móveis e conectados a dados locais, com uma variedade de visualizações de dados.

8 – Com o SQL Server Mobile Report Publisher aberto podemos fazer finalmente a criação do dashboard, mas primeiro temos que nos conectar ao Data Set criado anteriormente. Navegue até a aba de dados e selecione opção de adição de dados, veja abaixo.

A imagem acima foi capturada a partir da edição do relatório já montado.

9 – Uma vez conectado, iniciaremos o layout do dashboard. Utilizaremos 3 layouts no painel, sendo o primeiro o que está marcado na imagem abaixo. Arraste e o coloque na mesma posição, utilizando a metade horizontal do painel, sem modificar a quantidade de linhas e colunas na grade.

10 – Em seguida arraste 2x o gráfico de pizza utilizando o espaço restante no painel, veja na imagem abaixo.

11 – Volte para a aba de configuração de dados para que cada elemento do relatório possa ser configurado. Para o primeiro elemento, siga conforme a imagem abaixo a configuração das propriedades. Deve ser alterado a fonte de dados e adicionado uma nova coluna para que seja possível fazer uma comparação de quantidade de dados entre períodos, os demais ajustes já devem vir preenchidos.

12 – Os ajustes finais na primeira parte do painel deve ser feita na aba de layout, o modelo sugerido deve ser como na imagem abaixo.

13 – Ainda na aba de layout, os gráficos de pizza devem ser alterados para apresentar a estrutura em linhas, as alterações na fonte de dados deve ser feita na aba de dados. Como cada gráfico irá apresentar a somatória de ocorrências da mensagem do error log, será feito a comparação entre os valores da semana atual e anterior. Repare nos campos destacados a opção que deve ser selecionado na imagem abaixo, para o segundo gráfico de pizza deve ser o inverso a seleção da coluna.

14 – Volte na aba de design e faça qualquer ajuste final pendente, o modelo do relatório construído deve estar conforme a imagem abaixo.

15 – Uma vez concluído a construção do relatório é o necessário fazer a publicação para dentro da estrutura do Reporting Services. Esse processo é relativamente fácil e intuitivo, deve ser selecionado a opção para salvar e conectar ao host do Reporting Services, em seguida navegar nos diretórios. Depois de salvo o relatório mobile pode ser carregado pelo navegador, veja na imagem abaixo.

Pronto, concluímos o nosso relatório mobile e ele está pronto para ser acessado através de diversas plataformas. Além do acesso tradicional pelo endereço do relatório no Reporting Services, é possível acessá-lo pelo aplicativo do Power BI que pode ter seu download feito na Apple Store e Google Play. O aplicativo é grátis e dentre suas funcionalidades, é possível conectar ao seu servidor do Reporting Services localmente e consultar os relatórios mobile disponíveis, veja na imagem abaixo.

Caso tenham sugestões para otimização do processo que coleta os dados ou até no design do relatório, por favor, deixem nos comentários.

Obs: Alguns dados utilizados para realização dessa demonstração de criação de relatórios tiveram que ser ofuscados.

Até a próxima!

Anúncios

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s