Sql Serverda TSQL Sorgusu ile Trace Başlatmak

SQL Server Profiler ile yaptığımız tuning işlemini sorgular ile yapmaya çalışacağız.
Bu işlem için iki stored procedure yazdık.

• Birincisi aktif olan traceleri durdurup veritabanından ve diskten temizliyor.
• İkincisi Yeni bir trace açıp belirli bir dosya boyutunda ve süresince veritabanında trace başlatıyor.

Sorgu 1

Aşağıdaki sp ile çalıştırdığımız traceleri temizleyin. Aktif trace bulamaz ise işlem yapmaz:

 exec clearTraces

Sorgu 2

Bu sorgu ile yeni bir trace başlatıyoruz.

• İlk parametre olarak trace dosyası ismi veriyoruz. Bu dosyanın sonuna diskte trc uzantısı ile bir dosya oluşturur. Daha Önce aynı isimde dosya var ise hata verir. Sorgu 1 çalıştı ise hata ile karşılaşmazsınız.
• İkinci parametre mb cinsinden dosya büyüklüğünü gösteriyor.
• Ücüncü parametre ile trace'in kaç dakika süreceğini belirliyoruz.

  exec dbo.StartTrace 
        'c:\temp\Ogr001'--trace dosya adı.
        ,1000--mb
        ,10--dakika

Sorgu 3

Aşağıdaki sorgu ile trace dosyasındaki verileri bir temp tabloya atıyor. Burada temp tabloya atarken where koşulundaki hostname ve Databese name gibi parametreleri düzenleyebilirsiniz.
Sorgunun ikinci kısmında from ve where koşulları arasına göre sorguları grupluyor. Böylece parametreleri kaldırarak tabloya kaçkere sorgu attığını görebiliyoruz.

İlk Kısım

declare @traceFile varchar(216)
set @traceFile='c:\temp\Ogr001.trc'
SELECT * into #sorgular
FROM fn_trace_gettable(@tracefile  ,1)
where DatabaseName like '%DB%'
and ApplicationName='.Net SqlClient Data Provider'
and HostName='HOSTNAME-01'
and CAST(TextData as varchar(max))<>'exec sp_reset_connection '

İkinci Kısım

SELECT
 SUBSTRING(cast(TextData as varchar(max)), CHARINDEX('FROM',cast(TextData as varchar(max))),
 CASE WHEN  CHARINDEX('WHERE',cast(TextData as varchar(max)))-CHARINDEX('FROM',cast(TextData as varchar(max))) <=0 THEN 10000 ELSE
 CHARINDEX('WHERE',cast(TextData as varchar(max)))-CHARINDEX('FROM',cast(TextData as varchar(max)))end
  )
  ,COUNT(*)
 
   FROM #sorgular
  GROUP BY SUBSTRING(cast(TextData as varchar(max)), CHARINDEX('FROM',cast(TextData as varchar(max))),
 CASE WHEN  CHARINDEX('WHERE',cast(TextData as varchar(max)))-CHARINDEX('FROM',cast(TextData as varchar(max))) <=0 THEN 10000 ELSE
 CHARINDEX('WHERE',cast(TextData as varchar(max)))-CHARINDEX('FROM',cast(TextData as varchar(max)))end
  )
  ORDER BY 2 desc

Stored Procedureler:

ClearTraces

  create procedure clearTraces as
  declare @id int
  declare @path varchar(256)
 
  declare crTraces cursor for
  SELECT id,path FROM   sys.traces
  where path like 'c:\temp%'
  open crTraces
  fetch next from crTraces into @id,@path
  WHILE @@FETCH_STATUS =0
    BEGIN
    print 'Trace durdurulup siliniyor:'+@path
   
     EXEC sp_trace_setstatus @id ,@status = 0 ;-- stop trace
     EXEC sp_trace_setstatus @id ,@status = 2 ;-- delete trace
     fetch next from crTraces into @id,@path
    end
    close crTraces
    deallocate crTraces
exec xp_cmdshell 'del c:\temp\*.* /q'

xm_cmdshell 

komutu için izin vermeyi unutmayın:


---- To allow advanced options to be changed.
EXEC sp_configure ‘show advanced options’, 1
GO
—- To update the currently configured value for advanced options.
RECONFIGURE
GO
—- To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
—- To update the currently configured value for this feature.
RECONFIGURE
GO 

StartTrace

create Procedure StartTrace(@tracefile nvarchar(256),
@MaxSize bigint,@StopTime int)
as

DECLARE @TraceID INT
--DECLARE @MaxSize bigint   

 --SET @MaxSize = 15    
DECLARE @stopDate datetime
select @stopDate =dateadd(minute,@StopTime,getdate())

 EXECUTE sp_trace_create @TraceID output,
 @options = 2,  --//rollover the file when max size is reached
 @tracefile=@tracefile , --//trace file name
 @maxfilesize  = @MaxSize, --//maximum file size   
 @stoptime= @stopDate

 SELECT 'Oluşan TraceId:'+cast(@TraceID as varchar(max))  --//output the traceid create for viewing


 DECLARE @On BIT
 SET @On = 1
 EXECUTE sp_trace_setevent @TraceID, 10, 15, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 16, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 1, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 9, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 17, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 6, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 10, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 14, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 18, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 11, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 12, @On
 EXECUTE sp_trace_setevent @TraceID, 10, 13, @On    
 EXECUTE sp_trace_setevent @TraceID, 10, 35, @On --DatabaseName
 EXECUTE sp_trace_setevent @TraceID, 10, 8, @On  --clientHostname   
   

 EXECUTE sp_trace_setevent @TraceID, 12, 15, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 16, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 1, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 9, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 17, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 6, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 10, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 14, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 18, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 11, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 12, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 13, @On
 EXECUTE sp_trace_setevent @TraceID, 12, 35, @On --DatabaseName
 EXECUTE sp_trace_setevent @TraceID, 12, 8, @On  --clientHostname   

EXECUTE sp_trace_setstatus @TraceID, 1
--I can verify that the trace is running properly with a couple of system function calls. To view all of the traces running in the system, run the following: