Lendo, Filtrando, Salvando numa tabela do SQL ou em XML…Sushi…Enfim..Fazendo peripécias com o arquivo de trace do SQL Server Profiler usando POWERSHELL!!!!


 

TechNet Scripting Guys Link

(english version  post below)

Requer Powershell V2.0

Eu tinha um grande problema para ser resolvido. Meus arquivos de trace do profiler eram separados por folder, servidor, nome do trace mas a grande maioria tinha o “enable file rollover” selecionado gerando varias divisões do mesmo arquivo.

Eramos em três turnos de DBA´s. Cada um gerava seus arquivos de trace conforme precisasse, mas várias vezes tive que acessar os traces gerados pelos outros dba´s juntando algumas colunas que com certeza não eram comuns entre os arquivos. Um era SP_locks por exemplo, o outro duration. Por que eu fazia isso ? Um duration alto que foi pego num trace, eu queria cruzar com o de locks do dia anterior para análise, entre vários outros cruzamentos.

Isto era realmente complicado de se fazer de uma forma automatizada, uniforme e repetitiva. Até mesmo juntar todos os arquivos do mesmo trace era um trabalho chato. Eu poderia usar a fn_trace_gettable  sem problema algum, mas quando falamos em automatizar este processo a coisa ficava grande em TSQL. Pior ainda se eu precisasse juntar, como eu disse, arquivos de traces diferentes.

Foi quando resolvi usar powershell

Um dos parâmetros da função quando comecei a pensar na solução foi gerar os arquivos em tabelas no SQL Server ($filetotable). Este parametro me fala pra pegar cada arquivo e gerar uma tabela. Mas e se eu quisesse todos em uma só tabela ?

Eu precisava centralizar em uma tabela somente e como eu disse poderiam ser vários arquivos e com eventos e colunas diferentes entre eles.

Isto começou a se tornar um problema quando eu vi o tamanho do código e das condições que estava ficando minha função. Na verdade eu estava pensando procedural, como eu montava minhas funções em Visual basic.

Eu tinha esquecido umas das principais caraterísticas do powershell : Consistência.

Para que eu ia fazer este esfardefuncio pandemonium de código, se a saída de um cmdlet é um objeto (TUDO NO POWERSHELL é objeto) e este pode ser a entrada de outro cmdlet ?

Vamos ao código e alguns exemplos :

Primeiramente os parâmetros : A função trabalha com 4 sendo que somente o primeiro é mandatório.

    $TraceFileName = Mandatório Path completo do Arquivo de trace "C:\Temp\Profiler.trc" ou "C:\temp\*.trc"

Este parâmetro informa qual arquivo será lido. Se for um coloque o nome, se for vários *.trc (podem ser varios arquivos de traces com eventos e colunas diferentes)

Ex C:\TEMP\TSQL.TRC or C:\TEMP\TEMP\ *.TRC

    $FileToTable = Opicional  Boolean Flag que avisa se o(s) traces(s) vão para tabela(s).

Cada arquivo de trace será criado com o nome de “POWERSHELLTRACETABLE_" seguido do nome do dito cujo. Veremos abaixo que podemos especificar o servidor e database. Se não for irformado pegará o default (servidor default e database tempdb)

    $ServerName = OPTIONAL Server Name String – If not especified and $FileToTable = true default server will be used

    $DatabaseName = OPTIONAL Database Name String – If not especified and $FileToTable = true TEMPDB will be used

 

Get-SQLProfiler Script Code  

  1: Function Get-SQLProfiler () 
  2: <#
  3: ----------------------------------------------------------
  4: Load SQL SERVER Profiler Traces Files. (.trc)
  5: ----------------------------------------------------------
  6: Version 1.0
  7: Laerte Poltronieri Junior
  8: www.laertejuniordba.spaces.live.com
  9: 
 10: $TraceFileName   = MANDATORY String Full SQL SERVER Trace File Path  "C:\Temp\Profiler.trc" or "C:\temp\*.trc"   
 11: $FileToTable = OPTIONAL Boolean Flag to insert all data into SQL tables, divided by .trc file
 12: $ServerName = OPTIONAL Server Name String - If not especified and  $FileToTable = true default server will be used
 13: $DatabaseNe = OPTIONAL Database Name String - If not especified and  $FileToTable = true TEMPDB will be used
 14: 
 15: #>
 16: 
 17: 
 18: {
 19: 
 20:  [CmdletBinding()]
 21:  
 22:  PARAM(
 23:    [Parameter(Position=1,Mandatory=$true, ValueFromPipelineByPropertyName=$true,HelpMessage="SQL Server Profiler Trace File")]
 24:    [Alias("FullName")]
 25:    [ValidateScript({$_ -match ".TRC"})]
 26:    [String] $TraceFileName,
 27:    
 28:    [Parameter(Position=2,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Flag to insert into SQL Table. Default False")]
 29:    [Alias("InsertFile")]
 30:    [switch] $FileToTable = $false,
 31: 
 32: 
 33:    [Parameter(Position=4,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Server Name Default Localhost")]
 34:    [Alias("SvrName")]
 35:    [String] $ServerName = $env:COMPUTERNAME,
 36: 
 37:    [Parameter(Position=5,Mandatory=$false, ValueFromPipelineByPropertyName=$true,HelpMessage="Database Name Default TEMPDB")]
 38:    [Alias("DbName")]
 39:    [String] $DatabaseName = "TEMPDB"
 40: 
 41:    
 42:   ) 
 43:   
 44:  
 45:  begin
 46:     {
 47:  
 48:    
 49:   $verbosePreference="continue" 
 50:   if ($fileToTable -AND $servername -eq $env:COMPUTERNAME -and $DatabaseName -eq  "TEMPDB" ) { 
 51:    $msg = "Server and Database parameters are not informed default values will be used : Server " + $env:COMPUTERNAME + " Database : TEMPDB"
 52:    write-warning $msg
 53:   }  
 54: 
 55:   
 56:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfoExtended") | out-null     
 57:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | out-null     
 58:   [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 
 59:  }
 60:  process 
 61:  {
 62: 
 63:   try 
 64:   {
 65:    
 66:    # Verify if was passed multples .trc
 67:    $MultipleFiles = ($TraceFileName.substring($TraceFileName.length  - 5, 5) -eq "*.trc")
 68:    
 69:    #Setup Final Result and line number
 70:    $LineNumber = 1
 71:    $FinalResult = @()
 72:    
 73:      
 74:    # Get All .trc files (one or various)
 75:    foreach ($TraceFilePath in Get-ChildItem $TraceFileName -ErrorAction Stop ) {
 76:    
 77:     try 
 78:     {
 79:    
 80:   
 81:      #get trace name to create table
 82:      $TraceFileNameTRC = ($TraceFilePath.PSChildName).trim()
 83:      $TraceFileNameTRC = $TraceFileNameTRC.Trim()
 84: 
 85:      [String] $TraceFilePathString = $TraceFilePath
 86: 
 87:      $TableName = "PowershellTraceTable_" + $TraceFileNameTRC.substring(0,$TraceFileNameTRC.length -4)
 88:      
 89:      $TraceFileReader = New-Object Microsoft.SqlServer.Management.Trace.TraceFile
 90:      $TraceFileReader.InitializeAsReader($TraceFilePathString) 
 91:      
 92:      if ($TraceFileReader.Read()-eq $true) 
 93:      {
 94:      
 95:       while ($TraceFileReader.Read())
 96:       {
 97:        
 98:       
 99:        $ObjectTrace = New-Object PSObject
100:        
101:       
102:        $ObjectTrace | add-member Noteproperty LineNumber   $LineNumber   
103:        $ObjectTrace | add-member Noteproperty TraceFile   $TraceFileNameTRC  
104: 
105:        
106:        $TotalFields = ($TraceFileReader.FieldCount) -1
107: 
108:        for($Count = 0;$Count -le $TotalFields;$Count++)
109:        {
110:         $FieldName = $TraceFileReader.GetName($Count)
111:         $FieldValue = $TraceFileReader.GetValue($TraceFileReader.GetOrdinal($FieldName))
112:         if ($FieldValue -eq $Null){ $FieldValue = ""}
113:          
114:         $ObjectTrace | add-member Noteproperty  $FieldName  $FieldValue
115:        }
116:        
117:        $FinalResult += $ObjectTrace
118:        $LineNumber ++ 
119:       
120:       }
121:       if ($FileToTable)
122:       {
123:       
124:        try {
125:         $SQLConnection = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo
126:         $SQLConnection.ServerName = $ServerName
127:         $SQLConnection.DatabaseName = $DatabaseName
128:         
129:         
130:         $TraceFileWriter = New-Object Microsoft.SqlServer.Management.Trace.TraceTable
131:         
132:                
133:         $TraceFileReader.InitializeAsReader($TraceFilePathString)
134:         $TraceFileWriter.InitializeAsWriter($TraceFileReader,$SQLConnection,$TableName) 
135:        
136:         while ( $TraceFileWriter.Write()) {}
137:        } 
138:        Catch {
139:          $msg = $error[0]
140:          write-warning $msg
141:        }  
142:        Finally {
143:         $TraceFileWriter.close()
144:        } 
145:        
146: 
147:       }
148: 
149:      } 
150:      
151:      
152:     } Catch {
153:         $msg = $error[0]
154:         write-warning $msg  
155:       } Finally {
156:         $TraceFileReader.close() 
157:     }
158:   
159:    } 
160: 
161:    Write-Output $FinalResult   
162:    
163:    
164:   } Catch {
165:      $msg = $error[0]
166:      write-warning $msg  
167:   } Finally {
168:       $TraceFileReader.Dispose 
169:       $TraceFileWriter.Dispose
170:       $SQLConnection.Dispose
171:   }
172:  }  
173:  
174: }
175: 
176: 

Download script : i6

Vamos fazer alguns testes :

Eu criei um powershell módulo de função (.psm1) contendo somente este arquivo, para nossos testes correrem bem.

No seu Powershell Profiler digite ?

Import-Module -Name C:\temp\Get-SQLProfiler.psm1 -WarningAction SilentlyContinue  -ErrorAction Stop 

 

   Lendo um arquivo de trace a não inserindo numa tabela do SQL .

Eu tenho este arquivo de trace C:\Temp\ProfilerTraces\TSQL.trc

2copy_thumb1

O arquivo tem as colunas e eventos do SQL Server Profiler template TSQL.

Execute :

Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC"

 

E o resultado será :

3_thumb2

As propriedades  LineNumber e TraceFile sempre estarão em qualquer resultado desta função.

LineNumber é um incremento e TraceFile é o nome do arquivo de trace.

Lembre-se que todo resultado será com base nas colunas do tracem independente dos arquivos terem colunas diferentes.

Vamos ver o mesmo resultado só que selecionando as propriedades LineNumber, TraceFile ,EventClass and TextData, filtrando pelo Linenumber menor que 5.

Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC"  | whereobject {$_.LineNumber -le 5} | select LineNumber, TraceFile ,EventClass ,TextData | format-table

 

4_thumb 

   Lendo um arquivo de trace e inserindo na tabela.

Vamos usar o exemplo enterior só que  $FiletoTable agora é $true, isso quer dizer que teremos uma tabela no SQL.

Se não for informado  server e database, uma mensagem de warning será exibida dizendo que os valores default serão usados.

6copy_thumb

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\TSQL.TRC" $true | where-object {$_.LineNumber -le 5} | select LineNumber, TraceFile ,EventClass ,TextData | format-table

E a tabela no SQL SERVER

7_thumb2

   Lendo Múltiplos Trace Files e Inserindo numa tabela SQL .

Digamos que temos varios árquivos SQL Server Profiler trace com rollover. Se quisermos juntá-los simplesmente passe o path dos traces .

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\Rollover\*.TRC" $true |  select LineNumber, TraceFile ,EventClass ,TextData | format-table

A figura abaixo mostra quando o arquivo muda  :

8_thumb

Serão criadas uma tabela para cada arquivo. Se você quiser todos os arquivos em uma tabela somente, simplesmente salve esta saida num XML e importe depois (mais 2 linhas). Veremos abaixo como fazer isso .

   Lendo Múltiplos Trace Files com Diferente Eventos e Colunas

Podemos também juntar arquivos de trace com diferente colunas e eventos. No exemplo abaixo eu juntei um trace  TSQL_SP com TSQL_Locks.

9_thumb

Bom Laerte mas se eu juntar vários arquivos com colunas diferentes como eu vou saber as propriedades que estão nele.

Simples, se você não quiser usar o get-member, ponha um  filtro com linenumber = 1

LINENUMBER e TRACEFILE sempre serão retorno na  Get-SQLProfiler.

  1: Get-SQLProfiler "C:\TEMP\ProfilerFiles\Merge\*.TRC" | where-object {$_.LineNumber -le 1} | format-table

10_thumb

Now just get the list, choose the fields and put in | select.

Lembre-se que com a opção $FiletoTable true, serão criadas tabelas para cada arquivo de trace.

“Ha Laerte, mas eu quero centralizar todos meus traces em uma tabela somente”. Eu confesso que primeiramente eu queria fazer isso por parâmetro, mas para que se posso com mais duas linhas fazer isso. Powershell resolve isso  para mim e eu não preciso “Engessar” minha função.

Primeirament eu crio uma tabela com coluna XML

USE [tempdb]
GO

/****** Object:  Table [dbo].[PowershellProfileTable_XML]    Script Date: 11/18/2009 02:27:39 ******/
If  EXISTS (Select * FROM sys.objects WHERE Object_id = Object_ID(N'[dbo].[PowershellProfileTable_XML]’) And type In (N’U’))
DROP TABLE [dbo].[PowershellProfileTable_XML]
GO

USE [tempdb]
GO

/****** Object:  Table [dbo].[PowershellProfileTable_XML]    Script Date: 11/18/2009 02:27:42 ******/
Set ANSI_NULLS On
GO

Set QUOTED_IDENTIFIER On
GO

CREATE TABLE [dbo].[PowershellProfileTable_XML](
[XML] [xml] NULL
) On [PRIMARY]

GO

 

Agora o powershell vai trabalhar. Somente salvamos em XML…e podemos escolher quais propriedades (colunas).

(Get-SQLProfiler "C:\TEMP\*.TRC" | Select LIneNumber,EventClass,TextData,NTUserName,LoginName | ConvertTo-Xml -NoTypeInformation).save("c:\temp\teste.xml")
$XML= "insert into PowershellProfileTable_XML values (‘" + ((Get-Content "C:\Temp\teste.xml") -replace "’", "”")  + "’)" 
invoke-sqlcmd -ServerInstance $env:COMPUTERNAME -database "master" -query $xml

 

E com umas simples Xquery tiramos os dados do campo XML :

Select t2.Col1.value(‘(Property[@Name="LineNumber"]/text())[1]’, ‘int’) LineNumber,
    t2.Col1.value(‘(Property[@Name="EventClass"]/text())[1]’, ‘nvarchar(255)’) EventClass,
    t2.Col1.value(‘(Property[@Name="TextData"]/text())[1]’, ‘nvarchar(max)’) TextData,
    t2.Col1.value(‘(Property[@Name="NTUserName"]/text())[1]’, ‘nvarchar(255)’) NtUserName,
    t2.Col1.value(‘(Property[@Name="LoginName"]/text())[1]’, ‘nvarchar(255)’) LoginName
FROM dbo.testeXML
CROSS APPLY xml.nodes(‘/Objects/Object‘) As t2(Col1)
where t2.Col1.value(‘(Property[@Name="LoginName"]/text())[1]’, ‘nvarchar(255)’) Like ‘%laerte%’

 

 

11_thumb2

Eu não preciso criar mais um parâmetro para fazer esta operação. A característica de CONSISTÊNCIA do powershell faz isso pra mim.

Eu simplesmente coloco o pipe (|) e posso gravar em XML, filtrar por qualquer propriedade , importar pra tabela SQL, exportar pra TXT..e porque não SUSHI..hehehe.

Muito obrigado ao meu amigo Shay Levy que sempre tem tempo para me passar seus grandes conhecimentos. Jeffrey Hicks também, com várias dicas de como usar mandatory parameters,meu amigo e companheiro de  SQL SERVER DAY Felipe Ferreira no XQuery e  óbvio o mestre do POWERSHELL com SQL SERVER Chad Miller.

Bom são 2 da manhã e eu estou ouvindo um som fantástico ( dica de Thomas – @doctordns)

POWERSHELL ROCKS !!!!!

“Hey, comin’ out around the world be ready for a brand new beat
Oh, summer’s here and the time is right, oh, for dancing in thestreet
Dancing in Chicago (dancing in the street)
Down in New Orleans (dancing in the street)
In New York City (dancing in the street)

All we need is music, sweet music (sweet, sweet music)
They’ll be music everywhere (everywhere)
They’ll be laughing, singing, music swinging and dancing in thestreet

It doesn’t matter what you wear just as long as you are there
They’re dancing (dancing in the street) oh oh

This is an invitation across the nation the chance for folks tomeet
There’ll be swinging, swaying, music playing and dancing in thestreet

Philadelphia, PA now (dancing in the street)
Baltimore and D.C. (dancing in the street)
Can’t forget them other cities (dancing in the street)”

Dancing In The Street

Grateful Dead

  • New blog post: http://tinyurl.com/yhbzuzd – Read, Filter,Save SQL Table, XML.. Sushi.Finally FUN SQL Server Profiler Traces in #POWERSHELL

Follow me on Twitter

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 Powershell. Bookmark the permalink.

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