Acho que meu amigo e mentor, Anselmo Tassára, foi um das pessoas mais importantes que tive no aprendizado da programação. Uma das principais lições que ele me ensinou é que soluções simples são tão boas quanto as complexas.

Há alguns anos 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”. Para nossa surpresa, quando chegamos lá nos foi passada 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, óbvio, depois de alguns bugs, estabilizou!

Eu sempre procuro a solução na simplicidade! É por isso que hoje eu quero compartilhar aqui uma solução simples de automatização de subida de arquivos do perfmom para o 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 Manager\Buffer cache rit ratio

Você pode customizar a sua necessidade facilmente. 

 
Figura 2 – Escolhendo os contadores e o intervalo de coleta 

O 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.:

1. Uma fria, que receberá os dados não tratados.
2. Uma quente, com a mesma estrutura da fria, mas com os datatypes corretos, recebendo os dados higienizados.
3. Uma de controle da data e hora da ultima coleta.
4. 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:

Procedure

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.

Veremos 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:

1. Copiar via cmd o arquivo test.csv para perfup.txt
2. Executar a procedure usp_upperfmom
3. Excluir via cmd o arquivo perfup.txt

Crie um Job

Figura 4 – Criando um job 

Primeiro Step

Copiar via cmd o arquivo test.csv para perfup.txt

Figura 5 – Criando o primeiro step

O comando DOS é :

copy /Y c:\perflogs\test.csv c:\perflogs\perfup.txt

Segundo Step

Executar a procedure usp_upperfmom

Figura 6 –  Criando o segundo step

Terceiro Step

Excluir via cmd o arquivo perfup.txt

Figura 7 – Criando o terceiro Step

O comando DOS é :

Del c:\perflogs\perfup.txt

E, finalmente, um scheduler de uma em uma hora

Figura 8 – Scheduler

Com tudo pronto, para vermos 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. Em outro artigo vou mostrar como fazer isso para múltiplos servidores, 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 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 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 confundam com pensar de forma simplória!

Chaplin já dizia :

“Quando me amei de verdade, deixei de temer meu tempo livre e desisti de fazer grandes planos, abandonei os projetos megalômanos de futuro. Hoje faço o que acho certo, o que gosto, quando quero e no meu próprio ritmo. Hoje sei que isso é…SIMPLICIDADE!!!”

 
Um grande abraço a todos!