Simplicidade é complexidade resolvida.
Constantin Brancusi, escultor romeno.
Me lembro como se fosse hoje….
Eu e mais um amigo fomos assistir Star Trek The movie. Ele me disse Abriu uma escola de computador aqui .
Bom, em 1980 uma escola de computador numa cidade do interior. Fui o primeiro aluno, cinco anos após, eu já fazia meus programas na cidade em Clipper.
Laerte, não inventa. Faça da maneira simples.
Acho que meu amigo e mentor, Anselmo Tassára, foi um das mais importantes pessoas que tive no aprendizado da programação e principalmente que soluções simples são tão boas quanto as complexas.
Quer algo mais simples que o Kanban ? E controla uma complexidade alta.
Há alguns anos atrás eu e um amigo DBA fomos contratados para solucionar um problema de replicação que já durava 1 ano entre 10 servidores no SQL Server 2000.
Pelo projeto que nos foi passado, pensamos Uma replicação transacional atende, mas para nossa surpresa, quando chegamos lá nos foi passado uma estrutura de replicação que quando nós vimos um olhou para o outro e disse Nosssaaa !!!!.
O pessoal tinha criado uma estrutura de replicação própria. Eu fiquei uns dois dias pra entender qual era a tabela de contigência caso o link caísse e, percebi que não tinha. Realmente era uma estrutura digna de Star Trek, mas infelizmente não funcionava.
As X variantes que existem em qualquer projeto envolvendo empresas corporativas (link´s, concorrência, locks..etc) não foram levadas em conta. Em uma semana trocamos por uma replicação transacional do SQL SERVER com latência definida e, obvio depois de alguns bugs, estabilizou!
Eu sempre procuro a solução na simplicidade !!!
É por isso que vou compartilhar com vocês, caros amigos, uma solução SIMPLES de automatização de subida de arquivos do perfmom para o sql Server. Se você espera nessa artigo algo complexo, sugiro outra leitura, não perca seu tempo. Existem várias outras soluções na web escritas por pessoas que sabem muito de SQL Server.
Coletando os dados pelo Perfmom
Abrindo o Performance Monitor, na aba Performance Log and Events, criamos um arquivo de log novo.
Figura 1 Criando um novo arquivo de log
Escolha os contadores e o intervalo que deseja coletar. Aqui, como exemplo, usei somente dois contadores :
PhysicalDiskI_% Disk Read Time
SQL Server Buffer ManagerBuffer cache rit ratio
Você pode customizar a sua necessidade facilmente.
Figura 2 Escolhendo os contadores e o intervalo de coleta
Próximo passo é na aba Log Files deixe as configurações conforme figura 3.
Figura 3 Configurações do arquivo gerado
Nesta aba, as configurações ficam como :
Log File type
Text file (comma delimmited)
Uncheck End File Names
Eu coloquei o arquivo .csv em uma pasta fixa chamada Perflogs, mas caso você mude, não esqueça que terá que alterar na procedure que subirá estes dados. Agora é só startar o log.
Criando a estrutura de log,controle e exibição.
Teremos quatro Objetos.
Uma fria que receberá os dados não tratados.
Uma quente com a mesma estrutura da fria, mas com os datatypes corretos, recebendo os dados higienizados.
Uma de Controle da data e hora da ultima coleta.
Uma procedure que subirá os dados
Tabela Fria
Create table dbo.tblColdPerfmom
([Date] varchar(100),
PhysicalDisk_C_PercDiskReadTime varchar(100),
SQLServerBUfferManager_BufferCacheHitRatio varchar(100))
Tabela Quente
Create table dbo.tblHotPerfmom (
[Date] smalldetetime default getdate(),
PhysicalDisk_C_PercDiskReadTime float default 0,
SQLServerBUfferManager_BufferCacheHitRatio float default 0)
Tabela de Controle de data e Hora
create table LastDateTime
([Date] smalldatetime)
E finalmente a procedure para subir os dados.
create procedure usp_upperfmom
as
begin
declare @lastdate smalldatetime
begin try
Truncate table tblColdPerfmom
— reparem que mudei o nome para subida.txt, pois antes de rodar a proc
— terei um job para copiar o arquivo teste.csv para este nome
bulk insert tblColdPerfmom
from ‘c:/perflogs/perfup.txt’
with
(
fieldterminator =’,’,
FIRSTROW = 3
)
— Vamos limpar os dados
update tblColdPerfmom
set [date] = REPLACE([date],'”‘,”),
PhysicalDisk_C_PercDiskReadTime = REPLACE(PhysicalDisk_C_PercDiskReadTime,'”‘,”),
SQLServerBUfferManager_BufferCacheHitRatio = REPLACE(SQLServerBUfferManager_BufferCacheHitRatio,'”‘,”)
— Agora vamos passar os dados pra tabela quente, agrupando por hora e ja tirando a media.
— Mas somente os dados ate a ultima coleta
Select @lastdate = [Date]
from LastDatetime
–se for a null, pego a [Date] de 1 mes atras
set @lastdate = ISNULL(@lastdate,GETDATE() -30)
–dados agrupados de hora em hora
insert into tblHotPerfmom ( [date],
PhysicalDisk_C_PercDiskReadTime,
SQLServerBUfferManager_BufferCacheHitRatio
)
select SUBSTRING([Date],1,14) + ’00’,
AVG(cast(PhysicalDisk_C_PercDiskReadTime as float)),
AVG(cast(SQLServerBUfferManager_BufferCacheHitRatio as float))
from tblColdPerfmom
where CAST([Date] as smalldatetime) > @lastdate
group by SUBSTRING([Date],1,14) + ’00’
— Agora vamos atualizar a tabelinha com a ultima [Date] e hora da coleta
select @lastdate = max(cast([Date] as smalldatetime))
from tblColdPerfmom
if exists ( select 1
from LastDatetime
)
Update LastDatetime
set [Date] = @lastdate
else
insert into LastDatetime ([Date]) values (@lastdate)
end try
begin catch
declare @errormessage nvarchar(4000);
declare @errorseverity int;
declare @errorstate int;
select
@errormessage = error_message(),
@errorseverity = error_severity(),
@errorstate = error_state();
raiserror (@errormessage,
@errorseverity,
@errorstate )
end catch
end
Reparem que o arquivo que é subido pelo Bulk Insert chama-se perfup.txt (há, não esqueça de alterar a barra ( / ) para a barra de separação de diretório), no entanto, veremos também logo abaixo que eu troco o nome dele no JOB do SQL Server.
Finalizando, vamos montar o JOB que subirá estes dados.
Este job terá 3 steps:
Copia via cmd o arquivo test.csv para perfup.txt
Executa a procedure usp_upperfmom
Exclui via cmd o arquivo perfup.txt
Crie um Job
Figura 4 Criando um job
Primeiro Step
Copia via cmd o arquivo test.csv para perfup.txt
Figura 5 Criando o primeiro step
Segundo Step
Executa a procedure usp_upperfmom
Figura 6 Criando o segundo step
Terceiro Step
Exclui via cmd o arquivo perfup.txt
Figura 7 Criando o terceiro Step
E Finalmente um Scheduler de uma em uma hora…
Figura 8 Schedule
Com tudo pronto, basta executar a query abaixo para obtermos a média dos valores por dia:
SELECT CONVERT(char(10),[date],103),
AVG(PhysicalDisk_C_PercDiskReadTime ),
AVG(SQLServerBUfferManager_BufferCacheHitRatio)
FROM tblhotPerfmom
GROUP BY CONVERT(char(10),[date],103)
Este exemplo eu montei para um servidor. Para múltiplos servidores, logo estarei compartilhando como fazemos com o Powershell.
Considerações de Segurança
Como estamos trabalhando com jobs cmdexec e a procedure com Bulk Insert, os arquivos que ele copia e exclui são recursos do Sistema Operacional e não do SQL SERVER. Com base nisso o contexto de autenticação e autorização passa a ser no Windows.
Simplificando :
Owner do JOB é Sysadmin ?
Contexto de segurança é a conta de serviço SQL Server Agent. Isso quer dizer que esta conta tem que ter permissão na pasta que está o arquivo .csv (C:/perflogs)
Owner do Job não é Sysadmin, mas é um login do Windows ?
Contexto de segurança é a da conta deste owner. Isso quer dizer que esta conta tem que ter permissão na pasta que está o arquivo .csv (C:/perflogs)
Owner do Job não é Sysadmin e não é uma conta do Windows ?
Há a necessidade de criação de uma credencial (proxy) e aplicar as permissões nesta conta proxy. Isso quer dizer que esta conta tem que ter permissão na pasta que está o csv (C:/perflogs)
É isso pessoal, espero que gostem do artigo e sempre pensem em soluções simples, tanto em TI como na vida. Não confudam com pensar simples.
Um grande abraço a todos !!!!
Laerte Junior