Sinalizando eventos do SQL Server com o contador SQLServer:User Settable – Query

Bom Dia Pessoal,

Há muito tempo atrás, em um grupo de discussão de SQL Server alguém fez a seguinte pergunta: "Como saber em passo do DTS o SQL Server está ? E se dentro do passo tiver várias tarefas ? Como saber qual delas ele está executando ?". É sem dúvida uma pergunta muito interessante e na época algumas boas soluções foram sugeridas. Pois bem, hoje no fórum do MSDN vi uma pergunta análoga e intitulada como "Como mapear tempo de processos". Embora as perguntas possam não ter uma relação direta, elas esboçam uma necessidade comum: Como sinalizar para o SQL Server que alguma tarefa está concluída.

Essa é uma necessidade comum principalmente para rotinas com muito processamento que são executados de forma offline (backups, processos de ETL, etc). Há vários mecanismos para realmente saber "em que pé que está" e alguns incluem o SQL Profiler, as DMVs como sys.dm_exec_requests ou ainda algumas consultas na velha sysprocesses do SQL Server 2000. São todas alternativas válidas, mas se forem analisadas com cuidados, essas alternativas para funcionarem bem necessitam de uma certa dose de previsão e conhecimentos de processos. Se por exemplo o Profiler for utilizado para verificar se determinado processo acabou é preciso que ele seja iniciado antes que o processo acabe para que as instruções SQL possam ser capturadas. Se as DMVs forem utilizadas, o máximo que será possível é monitorar que instruções SQL estão sendo disparadas em um determinado momento.

O Profiler e as DMVs são ferramentas muito úteis para diagnosticar que comandos estão sendo repassados ao SQL Server, mas elas tem uma falha, ou melhor dizendo, uma limitação. Esse diagnóstico limita-se a comandos TSQL e eventos que ocorrem no SQL Server. Nem sempre esses comandos e eventos são necessariamente o que se deseja sinalizar. Se um determinado processo de folha de pagamento roda cinco pacotes SSIS, um script no SQLCMD e posteriormente um comando TSQL para popular algumas tabelas, o uso do Profiler e das DMVs será capaz de diagnosticar os comandos SQL que estão sendo executados, mas isso pode significar muito pouco em relação ao processo "Folha de Pagamento". Se uma consulta como "DELETE FROM #TMPRubricas WHERE Data <= DATEADD(D,-5,GETDATE())" aparecer, sabe-se que essa consulta está sendo executada, mas qual é a relação dessa consulta em relação a todo o processo ? Como saber em que pé do processo o SQL Server está ? Decorar as principais consultas ajuda, mas isso pode ser complicado para os novatos.

Para sair um pouco dessa abstração e ir rumo à prática nada melhor do que uma situação do cotidiano. Vamos supor que um determinado processo (possivelmente um job) é composto de um script com 5 tarefas:

  • Realizar o cálculo de atualização das parcelas em atraso
  • Atualizar as mercadorias conforme alterações previstas
  • Recalcular o estoque de mercadorias
  • Calcular as comissões devidas
  • Encerrar o movimento do dia

Agora vamos supor que cada uma dessas tarefas envolva cerca de 200 comandos TSQL cada uma. Se são 5 tarefas então teremos 100 comandos TSQL. O Profiler e as DMVs podem ajudar a descobrir qual comando está rodando, mas visualizar um comando em um universo de 1.000 não ajuda muito a descobrir em que ponto do processo o SQL Server está. Como descobrir em qual das tarefas o SQL Server está processando ? É esse o tipo de situação que o contador MSSQLServer:User Settable pode auxiliar.

O contador SQLServer:User Settable – Query é um contador publicado pelo SQL Server disponível no System Monitor (Windows 2003 e anteriores) e no Windows Reliability and Performance Monitor (Windows Vista e 2008). Esse contador é um contador que possui dez instâncias (User Counter) que podem ser utilizadas de forma personalizada através da Stored Procedure sp_user_counterX onde X é um número de 1 a 10. Vejamos como funciona:

— Inicia o processo
EXEC sp_user_counter1 @newvalue = 00

— Realizar o cálculo de atualização das parcelas em atraso
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 10

— Atualizar as mercadorias conforme alterações previstas
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 20

— Recalcular o estoque de mercadorias
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 30

— Calcular as comissões devidas
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 40

— Encerrar o movimento do dia
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 50

— Finaliza o processo
WAITFOR DELAY ’00:00:05′
EXEC sp_user_counter1 @newvalue = 00

O script acima listou as tarefas do processo mas não executa nada realmente. Levaria muito tempo para simular os 200 comandos que cada tarefa deveria executar então o script convenciona que cada tarefa irá levar aproximadamente 5 segundos através do comando WAITFOR DELAY (no mundo real ao invés desse comando, os 200 comandos TSQL seriam apresentados). Logo após o WAITFOR DELAY há uma chamada a stored procedure sp_user_counter1 incrementando o valor. Antes de executar o script é necessário abrir o System Monitor ou o Windows Reliability and Performance Monitor (ambos podem ser chamados através do perfmon.exe). Após abrí-lo é preciso adicionar o contador SQLServer:conforme a figura abaixo (lembre-se de adequar a instância utilizada, pois, pode não ser a mesma da figura):

Após adicionar o contador, retorne ao SQL Server Management Studio e rode o script previamente apresentado. Enquanto o script é executado, retorne ao System Monitor ou o Windows Reliability and Performance Monitor.

Durante a execução do script é possível observar que o contador evolui de 0 para 50 em etapas e que após atingir 50 declinou. É possível correlacionar a execução das tarefas com os valores do contador conforme o quadro abaixo:

Tarefa Valor (Antes) Valor (Depois)
Inicia o processo 00 00
Realizar o cálculo de atualização das parcelas em atraso 00 10
Atualizar as mercadorias conforme alterações previstas 10 20
Recalcular o estoque de mercadorias 20 30
Calcular as comissões devidas 30 40
Encerrar o movimento do dia 40 50
Finaliza o processo 50 00

Se observamos no contador um valor de 30 por exemplo, saberemos que a tarefa "Recalcular o estoque de mercadorias" foi concluída e que a tarefa "Calcular as comissões devidas" já iniciou sua execução. Essa visualização é muito mais simples do que utilizar o Profiler ou as DMVs para descobrir qual comando está sendo executado e tentar descobrir em qual tarefa o SQL Server está. Quando uma determinada tarefa envolve muitos passos, comandos e (ou) ferramentas diferentes, essa visualização torna-se ainda mais valiosa e sua informação bem mais simples de serem obtidas.

Mas por que utilizar o contador SQLServer:User Settable ?

Alguns invariavelmente irão perguntar porque utilizar uma abordagem desse tipo. Talvez fosse melhor construir uma tabela auxiliar a parte e fazer um INSERT para que se identifique em qual tarefa o SQL Server parou. Uma tabela a parte não só tornaria possível obter esse status, mas também seria útil para colocar outras características como hora de início, hora de fim, usuário executor, etc. Inquestionavelmente são pontos que tornam a tabela auxiliar uma solução potencialmente mais poderosa, mas isso não a torna melhor em todas as situações. O uso de contadores de desempenho representa uma especificação já conhecida de mercado e a partir do momento em que eles são utilizados é possível reaproveitá-los para outros fins. As ferramentas de monitoramento de mercado (Mom, NetIQ, Patrol, Quest, Nagios, etc) são capazes de capturar contadores de desempenho, o Performance Studio do SQL Server 2008 trabalha exaustivamente com contadores de desempenho (embora use scripts TSQL também), normalmente a equipe de produção trabalha observando contadores de desempenho, etc. Utilizar uma tabela auxiliar é uma possibilidade, mas que naturalmente demandará maior tempo de desenvolvimento além de um tempo adicional caso seja necessário fazer integrações com outras ferramentas. Utilizar o contador requer apenas que uma Stored Procedure seja chamada.

Um exemplo de integração

O código C# abaixo mostra como é possível capturar instruções dos contadores de desempenho do Windows (no nosso caso o SQLServer:User Settable – Query). Como estou utilizando uma instância nomeada (SQL2008) para que o código funcione pode ser necessário fazer alterações. Se a instância for DEFAULT, o valor da propriedade CategoryName deve ser "SQLServer:User Settable". Se a instância for nomeada, o valor dessa propriedade deve ser "MSSQL$NomeDaInstancia:User Settable".

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Diagnostics;
using System.Threading;

namespace MSUC
{
    class Program
    {
        static void Main(string[] args)
        {
            // Instancia um novo Objeto de desempenho
            PerformanceCounter pc = new PerformanceCounter();

            // Atribui as propriedades do objeto
            pc.CategoryName = @"MSSQL$SQL2008:User Settable";
            pc.CounterName = @"Query";
            pc.InstanceName = @"User Counter 1";

            // Captura o valor do contador
            long valor = pc.RawValue;

            // Dispara uma mensagem para a console
            Console.WriteLine("Iniciando monitoramento");

            // Enquanto o processo não finalizar (Valor = 50)
            while (valor != 50)
            {
                valor = pc.RawValue;
                Console.WriteLine("O valor atual do contador é: " + valor.ToString());

                // Descansa cinco segundos
                Thread.Sleep(5000);
            }

            Console.WriteLine("O processo foi concluído");
            Console.WriteLine("Pressione alguma tecla para finalizar");
            Console.ReadLine();
        }
    }
}

Não explicarei o código C# já que o mesmo é bem simples. Aos que desejarem compilá-lo e não tiverem o Visual Studio instalado, basta seguir os passos abaixo:

  • Copie e cole o código em um arquivo .cs (Ex: G:\Fontes\msuc.cs)
  • Abra um prompt de comando
  • Navegue até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rode o comando csc /out:msuc.exe G:\Fontes\msuc.cs (supondo que o arquivo esteja em G:\Fontes\)
  • Executar o arquivo msuc.exe localizado no diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou o utilizado anteriormente)

Enquanto o executável MSUC está sendo executado, rode o script TSQL apresentado anteriormente.

Enquanto o script roda (executando as hipotéticas tarefas de 200 comandos TSQL), o prompt monitora o status do contador SQLServer:User Settable – Query e da instância User Counter 1. Quando o valor desse contador atinge 50 concluí-se que todas as tarefas foram concluídas e que o processo foi finalizado. Essa integração exigiu o mínimo de código e leitura de um simples contador. A simplicidade se dá porque toda a inteligência de coleta está na stored procedure sp_user_counter1 e não no código. Se a implementação fosse através de tabelas auxiliares, o esforço seria consideravelmente maior além do que a integração com outras ferramentas de mercado seria mais difícil.

E quando não utilizar o contador SQLServer:User Settable ?

Como foi apresentado, a utilização de stored procedures em conjunto com o SQLServer:User Settable pode facilitar o desenvolvimento rápido de um monitoramento de execução de processos bem como auxiliar bastante a integração com outras ferramentas de monitoramento de mercado. Isso não significa simplesmente que essa solução deva proliferar todas as rotinas offline que são executadas em uma organização ou ainda uma forma de sinalizar eventos de aplicação de larga utilização (pedido rejeitado, tentativa de fraudes, etc). É preciso lembrar que a quantidade de instâncias desse contador é limitada a apenas 10 e que esse pequeno limite é bem fácil de alcançar. A idéia de utilizar esse contador para lançar o ID do pedido mais recente ou ainda alguma tentiva de fraude para monitoramento pode parecer tentadora, mas se o objetivo é utilizar esse mecanismo para sinalizar comportamentos de aplicação, vale a pena lembrar que o Windows permite a publicação de contadores próprios e sem a limitação de 10 instâncias. Situações como querer monitorar a quantidade de pedidos por segundos, o número de usuários conectados, etc são bem melhor resolvidas com outros mecanismos. A stored procedure sp_user_counter não deve ser utilizada para esse propósito.

Ao meu ver a aplicação dessa solução é ideal para processos administrativos relacionados ao banco de dados (backups, reindexação, shrink, etc) onde o limite de 10 não seja tão facilmente extrapolado e onde o DBA tenha mais controle caso opte por outra alternativa. A recomendação para processos offline também é bem vinda desde que os processos monitorados sejam poucos.

Em relação a tabelas auxilares, o uso dessse contador também apresenta desvantagens. Tabelas auxiliares podem conter diversas outras métricas como foi dito, mas a principal desvantagem do contador em relação a elas é que as tabelas auxiliares persistem o resultado e podem ser pesquisadas posteriormente enquanto o contador mostra um resultado bem volátil. É possível utilizar o mecanismo de coleta de logs de contadores de desempenho do Windows, mas certamente consultar uma tabela é bem mais direto do que trabalhar com eles.

Um bom feriado a todos…

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s