Criando um arquivo de trace com PowerShell via SMO

Não muito diferente do artigo original com .NET, com PowerShell também conseguimos criar um script simples para gerar um arquivo de trace de uma determinada instancia do SQL Server.

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfoExtended.dll")

function Get-Date2(){
    [System.DateTime]::UtcNow
}

$template = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Tuning.tdf"

$date = Get-Date

$file = "C:\Temp\Trace$($date.Year)-$($date.Month)-$($date.Day) $($date.Hour)-$($date.Minute)-$($date.Second).trc"

$conn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo

$conn.ServerName = "Dalilah"

$traceReader = new-object Microsoft.SqlServer.Management.Trace.TraceServer

$traceReader.InitializeAsReader($conn, $template)

$traceFile = new-object Microsoft.SqlServer.Management.Trace.TraceFile

$traceFile.InitializeAsWriter($traceReader, $file)

$timeout = (Get-Date2).AddSeconds(30)

while ($traceFile.Write() -eq $TRUE -and $timeout -gt (Get-Date2)) { }

$traceFile.Close()

$traceReader.Close()

E o resultado:

Arquivo de trace gerado com PowerShell via SMO

Anúncios

Criando um arquivo de trace com .NET via SMO

Uma prática rápida para otimizar um ou vários bancos de dados é a criação de um arquivo de Trace por meio do SQL Server Profiler e utilizar este arquivo como carga para o Database Engine Tuning Advisor (DTA). O DTA, por sua vez, gerará uma relação de índices e estatísticas para os bancos de dados baseado no arquivo utilizado como carga.

Para a criação deste arquivo de trace, se utiliza normalmente o SQL Server Profiler com o template Tuning. Mas também podemos utilizar as bibliotecas do SMO para gerar este arquivo trace, como veremos a seguir.

Em relação a utilização do SMO, da mesma forma que utilizamos um objeto TraceServer como para capturar as consultas executadas no SQL Server, este objeto também será utilizado neste exemplo para o mesmo fim, só que em conjunto de um objeto TraceFile que escreverá a captura em um arquivo trace.

Então vamos ao código:

using System;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace DemoSmo
{
    static class DemoTrace2
    {
        // Caminho do template de Tuning
        private const string TuningFile =
            @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Tuning.tdf";
        
        /// <summary>
        /// Ref.: http://technet.microsoft.com/en-US/library/ms345134.aspx
        /// </summary>
        public static void Run()
        {
            // Caminho onde será salvo o arquivo de trace
            var file = string.Format(@"C:\Temp\Trace{0:yyyy-MM-dd HH-mm}.trc", DateTime.Now);

            var conn = new SqlConnectionInfo { ServerName = @"DALILAH" };

            // Criando o objeto TraceServer
            var traceReader = new TraceServer();

            // Iniciando a leitura do trace a partir do template Tuning do SQL Server Profiler
            traceReader.InitializeAsReader(conn, TuningFile);

            // Criando o objeto TraceFile
            var traceFile = new TraceFile();

            // Iniciando a escrita da captura no arquivo de trace
            traceFile.InitializeAsWriter(traceReader, file);

            // Executando a escrita dos traces
            var ts = new Thread(() => { while (traceFile.Write()) { } });

            ts.Start();

            // Esperar 30 segundos
            Thread.Sleep(30000);

            // Terminando a escrita do trace
            traceFile.Close();

            // Terminando a captura do trace
            traceReader.Close();
        }
        
    }
}

E o nosso resultado:

Arquivo de trace gerado com .NET via SMO

Capturando consultas executadas no SQL Server (Trace) com .NET via SMO

Como visto na semana passada, é possível utilizar o SQL Server Profiler para identificar as consultas que são executadas em uma instancia do SQL Server, além de outros dados, como o tempo de execução, quem esta executando e etc…

Mas seria possível automatizar este processo com SMO?

A resposta a esta pergunta é “Sim”, mas para um primeiro exemplo, veremos como criar e exibir Traces com o SMO, e na próxima semana veremos como automatizar o processo de criação de um Trace para Tuning (ótimo para quem vai utilizar o Database Engine Tuning Advisor) gerando um arquivo de trace compatível com o SQL Server Profiler.

Então vamos ao exemplo!

As DLLs responsáveis para a utilização das bibliotecas de trace são:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.ConnectionInfoExtended.dll

Para este exemplo utilizei o Template Standard do SQL Server Profiler, mas é possível utilizar outros templates ou até mesmo criar novos templates pelo SQL Server Profiler.

E abaixo nosso código de utilização de trace:

using System;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace DemoSmo
{
    static class DemoTrace1
    {
        private const string StandardFile =
            @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf";

        public static void Run()
        {
            var conn = new SqlConnectionInfo { ServerName = @".\SQLEXPRESS" };

            // Criando o objeto TraceServer
            var traceReader = new TraceServer();

            // Iniciando a leitura do trace a partir do template Standard do SQL Server Profiler
            traceReader.InitializeAsReader(conn, StandardFile);

            // Criando uma Thread para a leitura do trace
            var ts =
                new Thread(
                    () =>
                    {
                        while (traceReader.Read())
                        {
                            // Identificando o tipo de evento
                            var name = traceReader.GetValue(traceReader.GetOrdinal("EventClass"));

                            if (!name.Equals("SQL:BatchCompleted") && !name.Equals("RPC:Completed")) continue;

                            // Identificando o SPID que esta executando o comando
                            var spid = traceReader.GetValue(traceReader.GetOrdinal("SPID"));

                            // Identificando informações do que é executado
                            var query = traceReader.GetValue(traceReader.GetOrdinal("TextData"));

                            // .. duração da execução
                            var duration = traceReader.GetValue(traceReader.GetOrdinal("Duration"));

                            Console.WriteLine("--SPID: {0} | Duration: {1}", spid, duration);

                            Console.WriteLine();

                            Console.WriteLine(query);

                            Console.WriteLine();
                        }
                    });

            // Iniciando a Thread
            ts.Start();

            // Esperando 30 segundos
            Thread.Sleep(30000);

            // Fechando o trace de leitura
            traceReader.Close();

            // Fechando a Thread
            ts.Join();
        }
    }
}

E por fim, o resultado da execução deste código:

Desculpe para quem não “manja” de Thread no C#, mas não conheço maneira melhor de criar este exemplo sem Thread.

Abaixo o artigo do Slavik Krassovsky, que utilizei para entender o funcionamento dos objetos de Trace do SMO:
http://technet.microsoft.com/en-US/library/ms345134.aspx

Identificando consultas demoradas com o SQL Server Profiler

Antes de publicar os artigos sobre eventos com SMO (Trace e Events) que serão os temas a serem trabalhados nas próximas semanas, identifiquei a necessidade de mostrar o funcionamento do SQL Server Profiler, antes que alguém tente criar um SQL Server Profiler com o SMO.

O SQL Server Profiler é uma ferramenta normalmente utilizada por DBAs para identificar consultas lentas, diagnosticar problemas com deadlock e desempenho, e gerar cargas de consultas com o Database Engine Tuning Advisor.

Caso você nunca tenha falado de SQL Server Profiler, fique tranquilo, só pense que ele permite capturar eventos do SQL Server, como no caso, execução de consultas e procedures.

Para um exemplo bem simples, vamos executar o “Performance Tools\SQL Server Profiler”:

Ir em “File/New Trace…”:

Conectar a um servidor SQL Server:

E vamos nos deparar com a seguinte tela:

Especifique nesta tela um nome para seu Trace e o template a ser utilizado, no meu caso, o “TSQL_Duration”:

E você também pode definir um arquivo ou tabela para salvar o trace, exemplo:

Na aba “Events Selecion”, será possível definir filtros dos eventos a serem monitorados, assim como adicionar/remover eventos e especificar os dados a serem capturados de cada evento:

Prosseguindo com o botão “Run”, será possível ver em tempo real o que esta sendo executado no SQL Server e o tempo de execução de cada evento:

Ferramenta interessante, não? Então após se divertir com ela e conhecer um pouco mais de cada template, procure aprender como utilizar filtros e ordenação, criar seus próprios templates, assim como identificar recursos desta ferramenta para diagnosticar gargalos de desempenho e consultas lentas, e como trabalhar com ele junto com o Perfmon e o Database Engine Tuning Advisor.

Após utilizar o SQL Server Profiler, não se esqueça de parar o Trace, para não o deixar rodando sem necessidade no seu SQL Server.