Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

SQL Server Profiler - zrzut śledzenia do pliku tekstowego


Konieczność wykonania śledzenia pewnego procesu - składającego się z wielu instrukcji -  po raz kolejny okazała się nazbyt uciążliwa. Wiedząc o tym, że śledzenie można zapisać do pliku XML, postanowiłem wykorzystać możliwości XML w SQL Server.

Najpierw trzeba śledzenie zapisać do pliku:



Przykładową zawartość tego pliku kopiujemy do zmiennej typu XML w New Query w SQL Server Management Studio:

declare @xml xml = N'<?xml version="1.0" encoding="utf-16"?>
<TraceData xmlns="http://tempuri.org/TracePersistence.xsd">
  <Header>
    <TraceProvider name="Microsoft SQL Server" MajorVersion="9" MinorVersion="0" BuildNumber="3042" />
    <ServerInformation name="SRV" />
    <ProfilerUI>
      <OrderedColumns>
        <ID>27</ID>
        <ID>1</ID>
        <ID>10</ID>
        <ID>6</ID>
        <ID>11</ID>
        <ID>18</ID>
        <ID>16</ID>
        <ID>17</ID>
        <ID>13</ID>
        <ID>9</ID>
        <ID>12</ID>
        <ID>14</ID>
        <ID>15</ID>
      </OrderedColumns>
      <TracedEvents>
        <Event id="196">
          <EventColumn id="12" />
          <EventColumn id="1" />
          <EventColumn id="9" />
          <EventColumn id="6" />
          <EventColumn id="10" />
          <EventColumn id="14" />
          <EventColumn id="11" />
        </Event>
        <Event id="12">
          <EventColumn id="15" />
          <EventColumn id="16" />
          <EventColumn id="1" />
          <EventColumn id="9" />
          <EventColumn id="17" />
          <EventColumn id="6" />
          <EventColumn id="10" />
          <EventColumn id="14" />
          <EventColumn id="18" />
          <EventColumn id="11" />
          <EventColumn id="12" />
          <EventColumn id="13" />
        </Event>
        <Event id="13">
          <EventColumn id="12" />
          <EventColumn id="1" />
          <EventColumn id="9" />
          <EventColumn id="6" />
          <EventColumn id="10" />
          <EventColumn id="14" />
          <EventColumn id="11" />
        </Event>
      </TracedEvents>
    </ProfilerUI>
  </Header>
  <Events>
    <Event id="65534" name="Trace Start">
      <Column id="14" name="StartTime">2010-11-11T14:04:17.687+01:00</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">
EXECUTE [dbo].[OrderAdd] </Column>
      <Column id="9" name="ClientProcessID">7396</Column>
      <Column id="11" name="LoginName">mpowichrowski</Column>
      <Column id="6" name="NTUserName">mpowichrowski</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>
      <Column id="12" name="SPID">69</Column>
      <Column id="14" name="StartTime">2010-11-11T14:04:22.56+01:00</Column>
    </Event>
<Event id="12" name="SQL:BatchCompleted">
      <Column id="11" name="LoginName">Sharepoint</Column>
      <Column id="15" name="EndTime">2010-11-11T14:20:45.95+01:00</Column>
      <Column id="6" name="NTUserName">Sharepoint</Column>
      <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
      <Column id="12" name="SPID">65</Column>
      <Column id="14" name="StartTime">2010-11-11T14:20:45.95+01:00</Column>
      <Column id="16" name="Reads">0</Column>
      <Column id="18" name="CPU">0</Column>
      <Column id="1" name="TextData">select collationname(0x0904100000)</Column>
      <Column id="9" name="ClientProcessID">2612</Column>
      <Column id="13" name="Duration">155</Column>
      <Column id="17" name="Writes">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">exec sp_oledb_ro_usrname</Column>
      <Column id="9" name="ClientProcessID">2612</Column>
      <Column id="11" name="LoginName">Sharepoint</Column>
      <Column id="6" name="NTUserName">Sharepoint</Column>
      <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
      <Column id="12" name="SPID">67</Column>
      <Column id="14" name="StartTime">2010-11-11T14:20:53.95+01:00</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="11" name="LoginName">Sharepoint</Column>
      <Column id="15" name="EndTime">2010-11-11T14:20:53.95+01:00</Column>
      <Column id="6" name="NTUserName">Sharepoint</Column>
      <Column id="10" name="ApplicationName">Windows SharePoint Services</Column>
      <Column id="12" name="SPID">67</Column>
      <Column id="14" name="StartTime">2010-11-11T14:20:53.95+01:00</Column>
      <Column id="16" name="Reads">4</Column>
      <Column id="18" name="CPU">0</Column>
      <Column id="1" name="TextData">exec sp_oledb_ro_usrname</Column>
      <Column id="9" name="ClientProcessID">2612</Column>
      <Column id="13" name="Duration">242</Column>
      <Column id="17" name="Writes">0</Column>
    </Event>   
  </Events>
</TraceData>'

 
I najważniejszy fragment, przetworzenie tego XML w w inny:

declare @LoginName varchar(100)
declare @DatabaseName varchar(100)
declare @ServerName varchar(100)
declare @ApplicationName varchar(100)
declare @Action varchar(100)

-- set @Action = 'Completed'    -- 'Started'

--set @LoginName = ''
--set @DatabaseName = ''
--set @ServerName = ''
--set @ApplicationName = ''

;with xmlnamespaces( default 'http://tempuri.org/TracePersistence.xsd')
select char(10)+
'----------------------------------------------'+char(10)+
'-- Action: '+Action+char(10)+
'-- Start time: '+isnull(convert(char(30),StartTime,120),'')+char(10)+
'--   End time: '+isnull(convert(char(30),EndTime,120),'')+char(10)+
'----------------------------------------------'+
char(10)+TextData+
char(10)+char(10)
from (
select *, row_number() over (order by StartTime) rn
from (
select    case when t.c.value('@name','nvarchar(100)') like '%Completed%' then 'Completed' else 'Started' end Action,
        t.c.value('(Column[@name="StartTime"])[1]','nvarchar(30)') StartTime,
        t.c.value('(Column[@name="EndTime"])[1]','nvarchar(30)') EndTime,
        t.c.value('(Column[@name="TextData"])[1]','nvarchar(max)') TextData
from @xml.nodes('/TraceData/Events/Event') t(c)
where    (@LoginName is null or t.c.value('(Column[@name="LoginName"])[1]','nvarchar(100)') = @LoginName) and
        (@ServerName is null or t.c.value('(Column[@name="ServerName"])[1]','nvarchar(100)') = @ServerName) and
        (@DatabaseName is null or t.c.value('(Column[@name="DatabaseName"])[1]','nvarchar(100)') = @DatabaseName) and
        (@ApplicationName is null or t.c.value('(Column[@name="ApplicationName"])[1]','nvarchar(100)') = @ApplicationName)
        ) q
) r
where @Action is null or Action = @Action
for xml path('')


I po uruchomieniu tego query



klikamy w link otrzymując przetworzony trace profilera w zapis w pliku tekstowym, który możemy wygodnie przeglądać i przeszukiwać.


----------------------------------------------
-- Action: Started
-- Start time: 2010-11-11T14:04:22.56+01:00 
--   End time:                              
----------------------------------------------

EXECUTE [dbo].[OrderAdd]


----------------------------------------------
-- Action: Completed
-- Start time: 2010-11-11T14:20:45.95+01:00 
--   End time: 2010-11-11T14:20:45.95+01:00 
----------------------------------------------
select collationname(0x0904100000)


----------------------------------------------
-- Action: Started
-- Start time: 2010-11-11T14:20:53.95+01:00 
--   End time:                              
----------------------------------------------
exec sp_oledb_ro_usrname


----------------------------------------------
-- Action: Completed
-- Start time: 2010-11-11T14:20:53.95+01:00 
--   End time: 2010-11-11T14:20:53.95+01:00 
----------------------------------------------
exec sp_oledb_ro_usrname



Odpowiednio ustawiając wartości zmiennych (których nazwy są bardzo czytelne) możemy wykonać odfiltrowania interesujących nas danych:
-- set @Action = 'Completed'    -- 'Started'

--set @LoginName = ''
--set @DatabaseName = ''
--set @ServerName = ''
--set @ApplicationName = ''

Pozdrawiam wszystkich, którzy będą śledzić przy pomocy tej metody.
Opublikowane 11 listopada 2010 14:40 przez marekpow
Filed under: , ,

Komentarze:

Brak komentarzy
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems