Automatizando a subida de arquivos do PERFMOM para o SQL server


Pessoal,

NO meu último post eu coloquei alguns dados coletados do PERFMOM do windows. Estes dados eram subidos pro SQL server de forma automatizada e como eu disse no post, vou colocar a maneira que faço. Eu a uso a muito tempo.

Existem melhores  maneiras de se fazer isso ? Com certeza. Esse procedimento eu fiz a muito tempo e como é simples e funcional e atende minhas necessidades, ainda não fui atrás de outros meios.

Mas vamos lá :

Primeiramente vamos criar no PERFMOM a coleta . Vou usar poucos contadores, mas você pode adequar a tua necessidade perfeitamente.

  • Criar um novo arquivo de log em “Novas Configurações de LOG”

image

  • Escolher os contadores

Usei os contadores

  • Physical Disk : % De leitura em Disco
  • SQL SERVER Buffer manager : Buffer Cache Rit Hatio

image

  • Configurar a Coleta

Aqui podemos colocar o intervalo de coleta. Eu costumo deixar de 15 em 15 s mesmo.

image

 

  • Agora vamos escolher o path ,nome do arquivo e extensão do arquivo.

image

Eu uso o arquivo de texto delimitado por virgulas, tiro o check de terminar arquivos com nnnnn. Porque não uso ?

Vamos ver mais pra frente que faço uma copia deste arquivo, e já deixo o nome pré configurado. Se por acaso esta coleta for parada por algum motivo e reiniciar ela não muda de nome.

Pronto. Com o Perfmom configurado, passemos para o SQL server.

Primeiramente vamos ver como ficou o .csv. Nele temos que ignorar as duas primeiras linhas :

image

Agora vamos criar tres tabelas no SQL server. Uma tabela “fria” , uma “quente” . As duas com os mesmo campos, mas com estruturas diferentes. Uma servirá para subir os dados brutos, e a outra para receber eles tratados. E uma para guardar a ultima data e hora da coleta.

Create table PerfmomFria (

Data varchar(100),
PhysicalDisk_C_PercDiskReadTime varchar(100),
SQLServerBUfferManager_BufferCacheHitRatio varchar(100)

)

–Agora a Tabela Quente, que receberá os dados tratados.

Create Table Perfmom (

Data smalldatetime default getdate(),
PhysicalDisk_C_PercDiskReadTime Float default 0,
SQLServerBUfferManager_BufferCacheHitRatio Float Default 0

)

–Agora uma tabelinha simples, somente para guardar a ultima data e hora da coleta
create table UltimaDataHoraPerfmom (
data smalldatetime
)

 

Agora, vamos criar a procedure para subir os dados :

 

create  procedure usp_SobePerfmom
as
begin
    declare @ultimadata smalldatetime
    begin try
        Truncate table PerfmomFria
        — 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 PerfmomFria
        from ‘c:\perflogs\subida.txt’
        with
          (
             fieldterminator =’,’,
             FIRSTROW = 3
          )
        — Vamos limpar os dados
        update PerfmomFria
            set        Data = REPLACE(Data,’"’,”),
                        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    @ultimadata = data
        from      UltimaDataHoraPerfmom
        –se for a null, pego a data de ontem           
        set @ultimadata = ISNULL(@ultimadata,GETDATE() -1)
        insert into Perfmom (    Data,
                                           PhysicalDisk_C_PercDiskReadTime,
                                           SQLServerBUfferManager_BufferCacheHitRatio
                            )
        select    SUBSTRING(data,1,14) + ’00’,
                    AVG(cast(PhysicalDisk_C_PercDiskReadTime as float)),
                    AVG(cast(SQLServerBUfferManager_BufferCacheHitRatio as float))
        from    PerfmomFria
        where    CAST(data as smalldatetime) > @ultimadata
        group by SUBSTRING(data,1,14) + ’00’
        — Agora vamos atualizar a tabelinha com a ultima data e hora da coleta
        select    @ultimadata = max(cast(data as smalldatetime))
        from    perfmomfria
        if exists    (    select    1
                            from    UltimaDataHoraPerfmom
                       )
            Update    UltimaDataHoraPerfmom
            set          data = @ultimadata
        else
            insert into UltimaDataHoraPerfmom (data) values (@ultimadata)
    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

 

Pronto, com tudo OK, agora vamos montar o JOB

Este Job terá tres passos :

  1. Copia via cmd o arquivo teste.csv para subida.txt
  2. Executa a procedure usp_sobeperfmom
  3. Exclui o arquivo subida.txt

image

O comando DOS é

copy /Y c:\perflogs\teste.csv c:\perflogs\subida.txt

O segundo step executa a proc :

image

E agora o terceiro step apaga o subida.txt

image

O comando DOS é :

Del c:\perflogs\subida.txt

Agora é só schedular de 1 em 1 hora e pronto. Temos a posição automatizada !!!!

Para vermos a media dos valores por dia usamos :

select    convert(char(10),data,103),
            AVG(PhysicalDisk_C_PercDiskReadTime ),
            AVG(SQLServerBUfferManager_BufferCacheHitRatio)
from    Perfmom
group by     convert(char(10),data,103)  

 

Agora vai da criatividade de cada um, para gerar um report no reporting services com gráficos ou no excel é muito simples.

Falowww !!!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

11 Responses to Automatizando a subida de arquivos do PERFMOM para o SQL server

  1. Paulo R. says:

    Boa tarde Laerte,Muito bom seu post! Normalmente eu fico só com o dados coletados no excel, mas agora vou pensar nesta alternativa para integrar com o SSRS.Até mais!Paulo R. Pereirahttp://www.sqlfromhell.com

  2. Fabrício says:

    Fala Laerte,Show de bola esse post! Segui o passo a passo aqui incrementando alguns contadores e funcionou perfeitamente… Muito simples e prático…Abraço!

  3. Laerte says:

    Valeu Fabrício !!! Intaum Paulo, você pode até expandir um pouco mais e transformar num dashboard reports e chamar de dentro do SSMS.

  4. Unknown says:

    o blog está ficando bom amigo!!!Sinal que fui um bom professor pra você 🙂

  5. Edilson says:

    Parabéns Laerte aqui no seu blog tem abordagens de otimos assuntos do dia a dia..Esse topico achei excepcional, pois agregou muito pra mim pois sempre tive duvidas como iniciar um baseline e esse tópico é um otimo começo pra mim ter a base que necessito.*To aguardando o post do dashboard a um tempão, pra conclusão da matéria do powershell como foi informado no inicio.. :(Valeu !

  6. Laerte says:

    Brigadão Edilson, é o que eu tento, colocar coisas que realmente aocntecem no meu dia dia.Cara..verdade…eu deixei meio de lado o Powershell, mas estou voltando com ele agora. Inclusive estou preparando um artigo que fiz numa solução usando Powershell para criar uma análise de fragmentação de índices e propor uma estratégia de reindexação para cada índice, com base em sua fragmentação.Montarei também um dashboard pra gente concluir o assunto..Valeu meu lembrar !!!!Abraços meu amig

  7. Edilson says:

    Valeu pelo feedback !Tipo pode ser em relação a essa materia mesmo, que já vai ajudar bastante.Abs

  8. José Marcelo says:

    Fala LaerteEu particularmente gravo os dados dos contadores diretamente em banco que criei para monitoração, as 0:00 e paro a contagem, distribuo os dados em suas respectivas tabelas (rede, processadores, etc..) onde os valores gravados são as médias por minuto de cada contador. Abraço

  9. Laerte says:

    Fala meu amigo !!! Também pode ser feito claro. O Problema é que se o monitoramento for remoto e por algum motivo estiver fora, você perde os dados. Desta maneira não subirá também, mas os dados estão lá no CSV. Quando voltar o link é só rodar o job.

  10. Junior says:

    Show de Bola Laerte…Veja abaixo meus resultados: Cache Ratio Leitura em Disco09/12/2009 99,8646999630974 19,330276224796910/12/2009 99,8651540533531 0,61801581089683211/12/2009 99,8456691738503 2,72198018428051Excelente artigo que auxilia na resolução de uam forma simples através da coleta de informções no BD de forma automatizada.Abraço

  11. Laerte says:

    Grande Junior…que bom que ajudou….e essa leitura de disco baixa de um dia pro outro ?..usou as missing indexes né heheheheAbraço !!!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s