Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Haziran 2017  >>
PaSaÇaPeCuCuPa
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Keywords

23 Mayıs Pazartesi günü yapacağım webcast’in duyurusunu yapmak istiyorum.

SQL Server 2008 Performance Data Collection ile Veritabanı Sunucularınızı İzleyin başlıklı webcast’im 23.05.2011 Pazartesi günü 21:00-22:00 saatleri arasında gerçekleşecek.

Aşağıdaki url’i kullanarak vereceğim bu webcast’e katılabilirsiniz.

https://www.livemeeting.com/cc/mvp/join?id=7Z3BS9&role=attend&pw=w%27%257%265Rnd

Ajandamız şu şekilde olacak;

  • Performance Data Collection (PDC) Nedir ?
  • Datawarehouse Kurulumu
  • Default Collection Set’lerin Kurulması
  • Default Collection Set’lerin Raporlanması
  • PDC’da Server Side Trace Kullanımı
  • Bloklanan Sorguların PDC ile Toplanıp Raporlanması
  • PDC ile Deadlock Monitoring
  • Troubleshoot
  • Soru – Cevap

Not:Katılım url’i webcast başlamadan yarım saat önce aktif hale gelecektir.

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server’daki Locking işlemlerinden en can sıkıcı olan deadlock’tır. Ölümcül kitlenme olarak nitelendirdiğimiz deadlock’ta birbirlerinin kaynaklarını kilitleyen 2 session sonunda ilerleyemez duruma gelir ve SQL Server bu session’dan rollback maliyeti en az olanı victim seçerek rollback yapar ve diğer session’ın işine devam etmesini sağlar.

Bugünkü makalemizde sistemimizde oluşan deadlock’ları Data Collector ile nasıl monitor edebileceğimizi görüyor olacağız.

[more]

Bu yazımda deadlock’ın detaylarını inmiyorum. Belki bu konuda daha sonra bir makale yayınlarım. Bugünkü ana konumu oluşan deadlock’ları nasıl izleyeceğimiz.

Bunun için Data Collector yapısını kullanacağız. ServerSide trace kullanarak deadlock işlemlerini data collector’un datawarehouse’unda toplayacağız ve buradan raporlayacağız.

Daha önce Data Collector’de serverside trace nasıl tanımlanır konulu makalemi incelemenizi öneririm. Çünkü bugün çok fazla detaya girmeyeceğiz.
http://www.turgaysahtiyan.com/post/SQL-Server-2008-e28093-Performance-Data-Collection.aspx
http://www.turgaysahtiyan.com/post/SQL-Server-e28093-Trace-Collection-Set-ile-Server-Side-Trace-Kullanc4b1mc4b1.aspx

İlk olarak SQL Server Profiler üzerinden trace file’ımızı oluşturalım. Daha sonra bu sql dosyayı data collector sisteminde execute edeceğiz ve Deadlock Collection Set’ini create etmiş olacağız.

SQL Server Profiler’ı açıp Events Selection kısmından aşağıdaki seçenekleri seçiyoruz.

1

Daha sonra bu trace’i data collection set’i için SQL Script olarak kaydetmek için File >> Export >> Script Trace Definition >> For SQL Trace Collection Set kısmından bir isim vererek kaydediyoruz.

2

Kaydettiğimiz script aşağıdaki gibi bir yapıda olacaktır.

USE msdb
GO

BEGIN TRANSACTION
BEGIN TRY

-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the following script
-- *** with the name you want to use for the collection set.
-- ***
DECLARE @collection_set_id int;
EXEC [dbo].[sp_syscollector_create_collection_set]
    @name = N'SqlTrace Collection Set Name Here',
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_mode = 0, -- cached mode needed for Trace collections
    @logging_level = 0, -- minimum logging
    @days_until_expiration = 5,
    @description = N'Collection set generated by SQL Server Profiler',
    @collection_set_id = @collection_set_id output;
SELECT @collection_set_id;

-- Define input and output varaibles for the collection item
DECLARE @trace_definition xml;
DECLARE @collection_item_id int;

-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(xml, 
N'

  
    
    
    
  


  
</FILTERS>

');

-- Retrieve the collector type GUID for the trace collector type
DECLARE @collector_type_GUID uniqueidentifier;
SELECT @collector_type_GUID = collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name = N'Generic SQL Trace Collector Type';

-- Create the trace collection item
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in the following script
-- *** with the name you want to use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item]
   @collection_set_id = @collection_set_id,
   @collector_type_uid = @collector_type_GUID,
   @name = N'SqlTrace Collection Item Name Here',
   @frequency = 900, -- specified the frequency for checking to see if trace is still running
   @parameters = @trace_definition,
   @collection_item_id = @collection_item_id output;
SELECT @collection_item_id;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorNumber int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
END CATCH;
GO

 

Kaydettiğimiz bu dosyayı deadlock monitoring yapacağımız sunucuda execute ediyoruz ve Data Collection Set’in oluşmasını sağlıyoruz.

Oluşan Data Collection seti start ediyoruz.

3

Tanımlamalarımız tamam. Şimdi bir deadlock oluşturup bunu veri topladığımız yani datawarehouse’un olduğu sunucuda raporlamaya çalışacağız.

Deadlock oluşturmak için bir tablo create ediyoruz ve 2 farklı session’da sırası ile şu işlemleri yapıyoruz.

CREATE TABLE lockDeneme (a varchar(1))
GO

 

Script çalıştırma adımları

  1. 1.Sessionda
    begin tran
    insert lockdeneme select 'x'
  2. 2.Sessionda
    begin tran
    insert lockdeneme2 select 'y'
  3. 1.Sessionda
    select * from lockdeneme2
  4. 2.Sessionda
    select * from lockdeneme

 

4.adımı uyguladığımızda Database Engine belli bir süre bekleyecek ve daha sonra 2.Session’ı victim seçerek rollback edecektir.

Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 273) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Deadlock oluştu. Şimdi bu oluşan deadlock bilgisini Collection Set’i tetikleyerek datawarehouse’a gönderelim.

4

Job’lar işlerini tamamladıktan sonra oluşan bu deadlock bilgisi Datawarehouse’a gitmiş olacaktır.

Şimdi bu datewarehouse’u sorgulayarak deadlock bilgilerini görmeye çalışalım.

select convert(xml,TextData),duration,td.trace_info_id,ti.source_id,td.*,ti.*,si.* 
from core.source_info_internal si
left join snapshots.trace_info ti on ti.source_id=si.source_id
left join snapshots.trace_data td on td.trace_info_id=ti.trace_info_id and td.snapshot_id=ti.last_snapshot_id
where si.instance_name = 'EBTCLUSCBSQL01'
and textdata like '%deadlock%'
order by starttime desc

 

5

Gördüğünüz gibi deadlock bilgileri toplanmış. İlk kolon olan TextDataXML’de bilgisine tıklayarak XML içeriği görüntüleyelim.

<deadlock-list>
  <deadlock victim="process9045048">
    <process-list>
      <process id="process9045048" taskpriority="0" logused="236" waitresource="RID: 23:1:115:2" waittime="4234" ownerId="1662737575" transactionname="user_transaction" lasttranstarted="2010-09-07T10:40:35.470" XDES="0x4fa2af970" lockMode="S" schedulerid="7" kpid="18048" status="suspended" spid="273" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2010-09-07T10:40:40.100" lastbatchcompleted="2010-09-07T10:40:35.470" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SAHTIYAN_T" hostpid="6792" loginname="EBT\U02664" isolationlevel="read committed (2)" xactid="1662737575" currentdb="23" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="adhoc" line="1" sqlhandle="0x020000001e3eb416128ab587ebf6de659c302be988772bd6">
select * from lockdeneme     </frame>
        </executionStack>
        <inputbuf>
select * from lockdeneme
    </inputbuf>
      </process>
      <process id="process90274c8" taskpriority="0" logused="444" waitresource="RID: 23:1:1561:0" waittime="6392" ownerId="1613777247" transactionname="user_transaction" lasttranstarted="2010-09-06T17:37:07.733" XDES="0x2e687e3b0" lockMode="S" schedulerid="4" kpid="4912" status="suspended" spid="515" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2010-09-07T10:40:37.950" lastbatchcompleted="2010-09-07T10:40:33.303" clientapp="Microsoft SQL Server Management Studio - Query" hostname="SAHTIYAN_T" hostpid="6792" loginname="EBT\U02664" isolationlevel="read committed (2)" xactid="1613777247" currentdb="23" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
        <executionStack>
          <frame procname="adhoc" line="1" sqlhandle="0x020000002e50cb2999e2e5110a2e4c518d57e2fc6611f33f">
select * from lockdeneme2     </frame>
        </executionStack>
        <inputbuf>
select * from lockdeneme2    </inputbuf>
      </process>
    </process-list>
    <resource-list>
      <ridlock fileid="1" pageid="115" dbid="23" objectname="EBTDBA.dbo.lockDeneme" id="lock3cf165780" mode="X" associatedObjectId="72057594043170816">
        <owner-list>
          <owner id="process90274c8" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process9045048" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
      <ridlock fileid="1" pageid="1561" dbid="23" objectname="EBTDBA.dbo.lockdeneme2" id="locka4cba80" mode="X" associatedObjectId="72057594043236352">
        <owner-list>
          <owner id="process9045048" mode="X" />
        </owner-list>
        <waiter-list>
          <waiter id="process90274c8" mode="S" requestType="wait" />
        </waiter-list>
      </ridlock>
    </resource-list>
  </deadlock>
</deadlock-list>

 

Sarı işaretli satırlarda gördüğünüz gibi session’ların çalıştırdıkları ve deadlock’a sebep olan query’leri görebiliyoruz.

Son olarak deadlock graph’ı nasıl çıkartabileceğimize bakarak yazımızı sonlandıralım.

Bunun için az önce baktığımız xml çıktıyı xdl uzantısı ile kaydediyoruz.

Kaydettiğimiz dosyayı çift tıklayıp açtığımızda deadlock graph’a erişmiş olacağız.

6

7

 

Sizde sisteminizde oluşan deadlock’ları bu yöntemle serverside olarak toplayıp monitor edebilirsiniz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Locking işlemlerini nasıl trace edebileceğimizi SQL Server Profiler ile Bloklanan İşlem Raporu (Blocked Prosess Report) adlı yazımda görmüştük. Bu trace tanımlamasını yaptıktan sonra bu işlemi Data Collector’etoplatmak için Server Side Trace tanımlaması yapmaya başladım. Lakin işlemi yaparken bir hata aldım. Bugünkü yazımda bu hatayı ve çözümünün ne olduğunu görüyor olacağız.

[more]

Trace’i server side olarak toplatmak için aşağıdaki ilgili script’i execute ettiğimizde

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 03/09/2010  17:17:40
/*************************************************************/

USE msdb
GO

BEGIN TRANSACTION
BEGIN TRY

-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the following script
-- *** with the name you want to use for the collection set.
-- ***
DECLARE @collection_set_id int;
EXEC [dbo].[sp_syscollector_create_collection_set]
    @name = N'ServerSideLockTrace',
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_mode = 0, -- cached mode needed for Trace collections
    @logging_level = 0, -- minimum logging
    @days_until_expiration = 5,
    @description = N'Collection set generated by SQL Server Profiler',
    @collection_set_id = @collection_set_id output;
SELECT @collection_set_id;

-- Define input and output varaibles for the collection item
DECLARE @trace_definition xml;
DECLARE @collection_item_id int;

-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(xml,
N'<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">
<Events>
  <EventType name="Errors and Warnings">
    <Event id="137" name="Blocked process report" columnslist="3,15,51,4,12,24,32,60,64,1,13,41,14,22,26" />
  </EventType>
</Events>
</ns:SqlTraceCollector>
');

-- Retrieve the collector type GUID for the trace collector type
DECLARE @collector_type_GUID uniqueidentifier;
SELECT @collector_type_GUID = collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name = N'Generic SQL Trace Collector Type';

-- Create the trace collection item
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in the following script
-- *** with the name you want to use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item]
   @collection_set_id = @collection_set_id,
   @collector_type_uid = @collector_type_GUID,
   @name = N'ServerSideLockTrace',
   @frequency = 900, -- specified the frequency for checking to see if trace is still running
   @parameters = @trace_definition,
   @collection_item_id = @collection_item_id output;
SELECT @collection_item_id;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorNumber int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
END CATCH;
GO

 

Aşağıdaki gibi bir hata almaktayız.

Msg 3903, Level 16, State 1, Line 58
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 14684, Level 16, State 1, Line 71
Caught error#: 14684, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_item, Line: 144, with Message: Caught error#: 6908, Level: 16, State: 1, in Procedure: -, Line: 1, with Message: XML Validation: Invalid content. Expected element(s): 'Filters'. Location: /*:SqlTraceCollector[1]

Hatanın sebebi data collector setini tanımlayacak olan SP’nin gelen XML kod’da Filter bekliyor olması. Yani biz trace i hazırlarken filter verdiğimizde herhangi bir hata ile karşılaşmadan collection seti hazırlayabiliyorken, filter vermezsek yukarıdaki gibi bir hata ile kaşılaşıyoruz.

Hatayı çözmek için yapacağımız işlem basit. XML kod’da filter varmış gibi göstermek için şu şekilde bir şekilde değişiklik yapacağız.

SELECT @trace_definition = convert(xml,
N'<ns:SqlTraceCollector xmlns:ns="DataCollectorType" use_default="0">
<Events>
  <EventType name="Errors and Warnings">
    <Event id="137" name="Blocked process report" columnslist="3,15,51,4,12,24,32,60,64,1,13,41,14,22,26" />
  </EventType>
</Events>
<Filters>
</Filters>
</ns:SqlTraceCollector>
');

Bu değişiklik yaptıktan sonra query’i tekrar execute ettiğimizde Collection Set’in hatasız oluşturulduğunu göreceğiz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server’da yazılan uygulamalarda en çok canımızı sıkan noktalardan biride locking yani kilitlenme durumlarıdır. Kullanıcı tablonun birine transaction açar ve update-insert işlemine başlar, diğer kullanıcı bu tabloya erişmek istediğinde ilk kullanıcının işlemini bitirip COMMIT etmesine bekler. İşte buna locking denir. Hatta bu 2 kullanıcı birbirlerine lock’larsa olay deadlock yani ölümcül kitlenmeye kadar gidebilirki bu durumda SQL Server kullanıcılardan birini victim seçer ve rollback yapar.

Isolation Level yada nolock kullanımı ile en azından select işlemlerinde locking i çözmek mümkündür. Fakat bu durumda dirty yani kirli data okunma durumu ortaya çıkar.

Biz bugünkü yazımızda bu yaşanan locking işlemlerini nasıl monitör edip yorum yapabileceğimizi görüyor olacağız.

[more]

Monitor işlemini SQL Server Profiler kullanarak yapacağız. Ve kullanacağımız Event Blocked Prosess Report. Bu event kaç sn den fazla süren blocking leri raporlayacağını server parametrelerinden biri olan blocked process threshold dan almaktadır. Bu parametrenin default değeri 0 sn dir. Yani ne kadar sürdüğüne bakılmaksızın her locking işlemini loglar.

Microsoft best practise olarak bu değeri 5 sn’ye set etmemizi öneriyor. 5 sn den fazla süren lock işlemleri performans problemi çıkartabilir diye threshold u 5 sn yapıyoruz.

Bu değişikliği sp_configure sistem prosedürüne kullanarak yapıyoruz.

sp_configure 'show advanced options',1 ; 
GO 
RECONFIGURE; 
GO 
sp_configure 'blocked process threshold',5 ; 
GO 
RECONFIGURE; 
GO

 

Şimdi gelin locking loglamayı yapacak trace’i hazırlayalım.

Daha öncede bahsettiğim gibi bu loglama için Blocked Prosess Report event’ini kullanacağız. Parametremiz 5 sn olduğu için 5 sn den uzun süren locking işlemlerini loglamasını bekliyoruz.

SQL Server Profiler’ı çalıştırıp Event kısmından Blocked Process Report ‘ u seçiyoruz. Show all columns kısmını tıklayıp bütün kolonların bilgisini toplamak için işaretliyoruz.

1

Bu SQL Server Profiler’ı aşağıdaki script ile de oluşturabilirsiniz. Bu script C:\LockingTrace.trn file’ını oluşturacaktır.

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'C:\LockingTrace.trn', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 51, @on
exec sp_trace_setevent @TraceID, 137, 4, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 24, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 60, @on
exec sp_trace_setevent @TraceID, 137, 64, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 41, @on
exec sp_trace_setevent @TraceID, 137, 14, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 137, 26, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

 

Threshold değerimiz 5 sn olduğu için 5 sn’den fazla sürecek lock lar trace file a düşecektir.

Şimdi 5 sn den fazla sürecek bir lock oluşturalım. Yapacağımız adımlar şunlar

  1. Deneme yapmak için bir table create ediyoruz.
    create table lockDeneme(a varchar(1))
  2. 1. Query ekranında begin tran ile bir insert işlemi başlatıp commit etmeyerek table’ı lock’lı bırakıyoruz.
    begin tran
    insert lockDeneme select '1'
  3. 2.Query ekranında da select çekmeye çalışacağız ve lock lı olduğu için sonucu alamayacağız. Ve bunun da profiler’e düşmesini bekliyoruz.
    select * from lockdeneme

Adımları uyguladıktan sonra profiler’a geçiyoruz ve belli bir süre bekliyoruz.5 sn dolduktan sonra her 5 saniyede bir blocking report’un geldiğini göreceksiniz.

2

Text data kısmında bu block işlemi ile alakalı detaylar tamamını bulabiliriz.

3

Blocked-process ve blocking-prosess diye 2 başlık göreceksiniz. Bunlar block ‘ a sebep olan ve block’a maruz kalan işlemlerin detaylarını içermekte. Her 2 işleminde session id lerini görmemiz mümkün. Bunun ötesinde en güzeli her 2 işleminde query detaylarını görebilmekteyiz.

 

Locking işlemlerini SQL Server Profiler üzerinden monitor edip aksiyon olmak DBA olarak ana görevlerimizden birisi. Sizde bu makaledeki adımları uygulayarak uygun bir Profiler hazırlayabilirsiniz. Ayrıca bu profiler script ini Data Collector üzerinden Server Side Trace olarak tutabilmekte mümkün. Bu işlemin nasıl yapılacağı ile alakalı daha önce yazdığım aşağıdaki makaleyi inceleyebilirsiniz.

http://turgaysahtiyan.com/post/SQL-Server-e28093-Trace-Collection-Set-ile-Server-Side-Trace-Kullanc4b1mc4b1.aspx

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Instance ım birinde çalışan Data Collector Disk Usage Data Collection Set’inde aşağıdaki gibi bir hata almaktaydım.

SSIS error. Component name: DFT - Collect Query 0, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Raw File Destination" (48) failed with error code 0xC020205D while processing input "Raw File Destination Input" (53). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Hata ile ilgili yaptığım araştırmalar sonucunda hatanın Cache Directory’de tutulan cache dosyalarının bozulmasından kaynaklandığı sonucuna ulaştım.

[more]

Bu bozulma şu durumlarda gerçekleşebilir;

  • Data Collector bir exception ile karşılaştığında
  • Data Collector cache file a yazarken disk’in dolması
  • Bir firmware yada driver problemi

Bu hata için çözümümüz ise Cache dosyaları cache’lendikleri klasörden silmek. Bu silme işleminide aşağıdaki adımlar eşliğinde yapabiliriz.

  1. Management >> Data Collection item’ına sağ tıklayıp Properties’i açıyoruz. 
    1
  2. Cache Directory kısmından cache dosyaların nerede tutulduğunu öğreniyoruz. 
    2
  3. Bu directory’e gidip cache dosyaları siliyoruz. 
    3
  4. Disk Usage collection Set’ini Collect and UploadNow işlemi ile tekrar çalıştırıyoruz ve hata almadan job’ların problemsiz çalıştığını görüyoruz. 
    4

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server Data Collector Hata - ODS - Get current snapshot of dm_exec_query_stats yazımda Data Collector de bulunan Query Statistics collection set inin verdiği “arithmetic overflow” hatasından bahsetmiş ve Microsoft’un bu hata çözümü için önerdiği yöntemin cumulative update 5 i yapmak olduğuna değinmiştim.

Cumulative update geniş ölçekli yapılarda evet diyilince yapılacak bir işlem değildir. Bu tarz güncelleştirmeler için sizin de firmanızda bazı prosedure ler bulunabilir. Ayrıca Microsoft dahi “ölümcül” bir hata olmadığı sürece cumulative update in production ortamına yapılmasını önermemektedir.

Bu yüzden bugünkü yazımda bahsi geçen hatayı cumulative update yapmadan “unsupported” bir yöntemle nasıl aşacağımızı anlatacağım. Unsported diyorum çünkü normalde bu hatanın çözümü için Microsoft cumulative update 5 in yapılmasını önermekte ve biz collection set in SSIS paketiyle oynayacağımız için bu durum unsupported bir durum oluşturmaktadır.

[more]

Bu kadar giriş cümlesinden sonra şimdi işleme dönelim. Dediğim gibi Query Statistics collection set inin SSIS paketinde oynama yapacağız ve hata veren kısmı çözmeye çalışacağız.

Bu collection set in upload işleminde hata olduğunu biliyorum. Dolayısıyla değişiklik yapacağım SSIS paket upload SSIS paketi olacak. Pakette değişiklik yapmak için Microsoft Visual Studio yu çalıştırıyorum.

File>>New>>Project>>Integration Service Project kısmından yeni bir Integration Service projesi başlatıyorum.

Clipboard01

SSIS Packages yazısına sağ tıklayıp Add Existing Package a tıklayalım.

Clipboard02

Gelen ekranda Server Name bilgisini yazarak ve Authentication Type ı seçerek Package Path in yanında bulunan butona basalım.

Clipboard03

Clipboard04

Değiştirmek istediğimiz paket olan QueryActivityUpload u seçerek OK e basalım. Bir önceki ekrandada OK e basalım ve ana ekrana düşelim.

Şimdi Solution Explorer kısmından bu paketi çift tıklayalım ve açalım.

Bizim hatamız DFT - Create Interesting Queries Upload Batch isimli Data Flow Task ta.

Clipboard05

Bu task ı çift tıklayarak Data Flow kısmına geçelim. Bu kısımda ise hata veren adım ODS - Get current snapshot of dm_exec_query_stats isimli OleDB Source ta.

Clipboard06

Bu objeyi çift tıklayarak properties ine erişelim.

Clipboard07

Şimdi can alıcı noktaya geliyoruz. Bu ekrandaki SQL Command ı alıp SSMS ten çalıştırdığınızda “arithmetic overflow” hatası alacaksınız. Dediğim gibi değişiklik yapacağımız kısım burası.

Hatanın sebebide 4 farklı satırda geçen aşağıdakine benzer çarpımlar.

r.total_elapsed_time * 1000 AS total_elapsed_time

 

Bu çarpım “arithmetic overflow” hatası vermekte. Cast kullanarak ufak bir trik le hatayı çözmeye çalışacağız.

cast(r.total_elapsed_time as float) * 1000 AS max_elapsed_time

 

Script in son hali aşağıdaki gibi olmalı.

SET NOCOUNT ON
DECLARE @p1 datetime
SET @p1 = GETDATE()

SELECT 
    [sql_handle],
    statement_start_offset,
    statement_end_offset,
    -- Use ISNULL here and in other columns to handle in-progress queries that are not yet in sys.dm_exec_query_stats.  
    -- These values only come from sys.dm_exec_query_stats. If the plan does not show up in sys.dm_exec_query_stats 
    -- (first execution of a still-in-progress query, visible in sys.dm_exec_requests), these values will be NULL. 
    MAX (plan_generation_num) AS plan_generation_num,
    plan_handle,
    MIN (creation_time) AS creation_time, 
    MAX (last_execution_time) AS last_execution_time,
    SUM (execution_count) AS execution_count,
    SUM (total_worker_time) AS total_worker_time,
    MIN (min_worker_time) AS min_worker_time,           -- NULLable
    MAX (max_worker_time) AS max_worker_time,
    SUM (total_physical_reads) AS total_physical_reads,
    MIN (min_physical_reads) AS min_physical_reads,     -- NULLable
    MAX (max_physical_reads) AS max_physical_reads,
    SUM (total_logical_writes) AS total_logical_writes,
    MIN (min_logical_writes) AS min_logical_writes,     -- NULLable
    MAX (max_logical_writes) AS max_logical_writes,
    SUM (total_logical_reads) AS total_logical_reads,
    MIN (min_logical_reads) AS min_logical_reads,       -- NULLable
    MAX (max_logical_reads) AS max_logical_reads,
    SUM (total_clr_time) AS total_clr_time,
    MIN (min_clr_time) AS min_clr_time,                 -- NULLable
    MAX (max_clr_time) AS max_clr_time,
    SUM (total_elapsed_time) AS total_elapsed_time,
    MIN (min_elapsed_time) AS min_elapsed_time,         -- NULLable
    MAX (max_elapsed_time) AS max_elapsed_time,
    @p1 AS collection_time
FROM
(
    SELECT  
        [sql_handle],
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        plan_handle,
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time,
        min_worker_time,
        max_worker_time,
        total_physical_reads,
        min_physical_reads,
        max_physical_reads,
        total_logical_writes,
        min_logical_writes,
        max_logical_writes,
        total_logical_reads,
        min_logical_reads,
        max_logical_reads,
        total_clr_time,
        min_clr_time,
        max_clr_time,
        total_elapsed_time,
        min_elapsed_time,
        max_elapsed_time 
    FROM sys.dm_exec_query_stats AS q
    -- Temporary workaround for VSTS #91422.  This should be removed if/when sys.dm_exec_query_stats reflects in-progress queries. 
    UNION ALL 
    SELECT 
        r.[sql_handle],
        r.statement_start_offset,
        r.statement_end_offset,
        ISNULL (qs.plan_generation_num, 0) AS plan_generation_num,
        r.plan_handle,
        ISNULL (qs.creation_time, r.start_time) AS creation_time,
        r.start_time AS last_execution_time,
        1 AS execution_count,
        -- dm_exec_requests shows CPU time as ms, while dm_exec_query_stats 
        -- uses microseconds.  Convert ms to us. 
        --r.cpu_time * 1000 
        cast(r.cpu_time as float) * 1000 AS total_worker_time,
        qs.min_worker_time,     -- min should not be influenced by in-progress queries
        --r.cpu_time * 1000 
        cast(r.cpu_time as float) * 1000 AS max_worker_time,
        r.reads AS total_physical_reads,
        qs.min_physical_reads,  -- min should not be influenced by in-progress queries
        r.reads AS max_physical_reads,
        r.writes AS total_logical_writes,
        qs.min_logical_writes,  -- min should not be influenced by in-progress queries
        r.writes AS max_logical_writes,
        r.logical_reads AS total_logical_reads,
        qs.min_logical_reads,   -- min should not be influenced by in-progress queries
        r.logical_reads AS max_logical_reads,
        qs.total_clr_time,      -- CLR time is not available in dm_exec_requests
        qs.min_clr_time,        -- CLR time is not available in dm_exec_requests
        qs.max_clr_time,        -- CLR time is not available in dm_exec_requests
        -- dm_exec_requests shows elapsed time as ms, while dm_exec_query_stats 
        -- uses microseconds.  Convert ms to us. 
        --r.total_elapsed_time * 1000 
        cast(r.total_elapsed_time as float) * 1000 AS total_elapsed_time,
        qs.min_elapsed_time ,    -- min should not be influenced by in-progress queries
        --r.total_elapsed_time * 1000 
        cast(r.total_elapsed_time as float) * 1000 AS max_elapsed_time
    FROM sys.dm_exec_requests AS r 
    LEFT OUTER JOIN sys.dm_exec_query_stats AS qs ON r.plan_handle = qs.plan_handle AND r.statement_start_offset = qs.statement_start_offset 
        AND r.statement_end_offset = qs.statement_end_offset 
    WHERE r.sql_handle IS NOT NULL 
) AS query_stats 
OUTER APPLY sys.dm_exec_sql_text (sql_handle) AS sql
GROUP BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset 
ORDER BY [sql_handle], plan_handle, statement_start_offset, statement_end_offset

 

İşimiz bitti. Bu ekranı OK diyerek kapatabiliriz. Daha sonrada değişiklik yaptığımız paketi save ediyoruz.

Şimdiki adımımız bu paketi hata aldığımız sunucuya deploy etmek.

Bunun için Integration Server a SSMS kullanarak bağlanıyoruz.

Clipboard08

Stored Packages >> MSDB >> Data Collector yazısına sağ tıklayıp Import Package ı seçiyoruz.

Clipboard09

Gerekli Import bilgilerini aşağıdaki gibi dolduruyorum ve paketi import ediyorum.

Clipboard10

Yapacaklarımız bu kadar :) Bende bu işlemlerden sonra collection set im şıkır şıkır çalışmaya başladı. Darısı sizin başınıza. :)

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server Data Collector collection setlerinden Query Statistics server larımdan birinde aşağıdaki hatayı vermekteydi.

SSIS error. Component name: DFT - Create Interesting Queries Upload Batch, Code: -1073450982, Subcomponent: SSIS.Pipeline, Description: component "ODS - Get current snapshot of dm_exec_query_stats" (16412) failed the pre-execute phase and returned error code 0xC0202009.

[more]

Hata ile alakalı google da arama yaptığımda aşağıdaki KB article a eriştim.

http://support.microsoft.com/kb/975915

Gerçekten de makaledeki script i server ımda uyguladığımda “arithmetic overflow” hatası gelmekte.

Microsoft bu hatanın giderilmesi için Cumulative Update 5 in yapılması gerektiğini söylemekte. Benim daha yapmaya fırsatım olmadı, belki siz o cesareti bulur yaparsınız :)

 

Ekleme (18.08.2010) : Bu hatanın unsupported çözümü için lütfen aşağıdaki makaleye bakınız.

http://www.turgaysahtiyan.com/post/SQL-Server-Data-Collector-Query-Statistisc-Collection-Set-indeki-Hatanc4b1n-Cozumu.aspx

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server Data Collector ın rutin izlemelerim sırasında bütün server larımda birden down olduğunu gördüm. 4 Collection setim inde job ları aşağıdaki hatayı vererek işlemleri sonlandırıyordu.

Executed as user: XXX\YYYYY. The step did not generate any output. Process Exit Code 5. The step failed.

[more]

image

Daha detaylı bakmak için execution log u sorguladığımda

use msdb
select * from syscollector_execution_log
where collection_set_id=1
order by start_time desc

 

aşağıdaki hata mesajına eriştim.

SSIS error. Component name: SQL - Get SnapshotID, Code: -1073548784, Subcomponent: Execute SQL Task, Description: Executing the query "{call core.sp_create_snapshot (?, ?, ?, ?, ?, ?)}" failed with the following error: "The specified @collector_type_uid (302E93D1-3424-4BE7-AA8E-84813ECF2419) is not valid in this data warehouse.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Bu hata mesajıyla alakalı araştırma yapmaya devam ederken gün içinde SQL Server DataWarehouse DB sini temizlediğim aklıma geldi J Temizlik yaparken fazladan data silmiş olabileceğimi değerlendirdim.

Gerçekten de DataWarehouse yani dataların toplandığı DB de dbo.syscollector_collector_types tablosunu yanlışlıkla temizlediğimi gördüm. Oysaki burada data collection type larının bulunması gerekiyor. Ve bu tabloya SP de select çekerken boş değer döndüğü için hata alıyor.

Bu table ı doldurmak için Data Collector kurulu bir sistemden Insert sorgularını oluşturdum. Bu sorgularda aşağıdaki gibi.

insert core.supported_collector_types_internal    
	select '302E93D1-3424-4BE7-AA8E-84813ECF2419' 
insert core.supported_collector_types_internal    
	select '0E218CF8-ECB5-417B-B533-D851C0251271' 
insert core.supported_collector_types_internal    
	select '14AF3C12-38E6-4155-BD29-F33E7966BA23' 
insert core.supported_collector_types_internal    
	select '294605DD-21DE-40B2-B20F-F3E170EA1EC3'

 

Sizde aynı hatayla karşılaşırsanız DataWarehouse DB sinde yukarıdaki insert leri uygulamanız yeterli olacaktır.

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Gün geçmiyorki SQL Server Data Collector ün bir hatasıyla daha karşılaşmayayım J

Bugünkü hatamız, Server Side Trace Collection set inin upload unda karşılaştığım aşağıdaki hata.

SSIS error. Component name: SQL - Get trace info, Code: -1073548535, Subcomponent: Execute SQL Task, Description: An error occurred while assigning a value to variable "TraceStartTime": "Single Row result set is specified, but no rows were returned.".

[more]

SQL Server Collector job larını rutin olarak izlediğim bir anda ServerSide Trace ile alakalı olan collection ın upload job ının hata verdiğini gördüm.

Aşağıdaki sorgu ile hata detayını sorguladığımda;

select failure_message,* from syscollector_execution_log 
where collection_set_id=4 
order by start_time desc

 

failure_message kısmında aşağıda hataya eriştim.

SSIS error. Component name: SQL - Get trace info, Code: -1073548535, Subcomponent: Execute SQL Task, Description: An error occurred while assigning a value to variable "TraceStartTime": "Single Row result set is specified, but no rows were returned.".

İlk bakışta da anlayacağınız gibi hata SSIS paketi ile alakalı. Data Collector collection setler için SSIS paketi kullanmakta ve demekki bu hata bu paketlerden birinden gelmekte. Bizim hatamız Server Side Trace Colletion set inin upload unda olduğu için bu paketi server da bulup içini incelemeye koyuluyoruz.

res1

Hata detayında task Result set inin Single Row olduğu sonucuna varıyoruz. Aynı zamanda bu task ta TraceStartTime variable ı var. Bu 2 ipucunu kullanarak hata veren task ın SQL – Get Trace Info olduğu sonucuna varıyoruz. Bu Task ın detayını incelediğimizde

res2

Dikkatimizi hemen SQL Statement çekiyor. Demek ki bu statement çalıştığında sonuç döndüremiyor ve task failure oluyor. O zaman sıradaki işlemimiz bu statement daki SP yi yani dbo.sp_syscollector_get_trace_info SP sini incelemek.

SP oldukça basit.

CREATE PROCEDURE [dbo].[sp_syscollector_get_trace_info]  
    @trace_path  nvarchar(512),  
    @use_default int  
AS  
BEGIN  
    SELECT   
        CONVERT(nvarchar(30), t.start_time, 126) as start_time,  
        CASE t.status   
            WHEN 1 THEN 1   
            ELSE 0   
        END AS is_running,   
        ISNULL(t.dropped_event_count,0) as dropped_event_count,  
        t.id  
    FROM sys.traces t  
    WHERE (@use_default=1 and t.is_default=1)  
          OR (@use_default=0 AND t.path LIKE (@trace_path + N'%.trc'))  
END

 

BU SP nin yaptığı gelen parametreler ile sys.traces i sorgulayıp eşleşen trace i yakalamak.

Benim case imde sys.traces sadece default trace i döndürmekteydi. Yani bu collection ın kullandığı yada kullanması gerektiği trace uçmuş :) Dolayısıyla SP nin sonucu task a boş gelmekte oda Single Row özelliğinden dolayı fail edip akabinde de job fail etmekte. Çok zincirleme oldu değil mi :)

Çözüm olarak Server Side trace tanımlama işlemini tekrar gerçekleştirdim. Şu anda herhangi bir problem vermeden çalışmakta.

Bir sonraki Data Collector hatasında görüşmek üzere :)

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Hepimiz en az bir kez instance ımızda olan biteni trace edebilmek için SQL Server Profiler kullanmışızdır. Peki size, topladığımız bu profiler verilerini bir veritabanında saklayabileceğimizi ve istediğimiz an sorgulayabileceğimizi söylesem.?

SQL Server 2008 den önce bunu yapabilmek için özel application lar kullanmak gerekiyordu. Makina restart larında bu application ları tekrar çalıştırmak bazen sıkıntı olabiliyordu. Ama SQL Server 2008 ile beraber gelen Data Collection yapısı sayesinde çok büyük bir efor harcamadan SQL Server Profiler sonuçlarını istediğimiz bir veritabanında toplamamız mümkün.

Bugünkü yazıma geçmeden önce daha önce Data Collection ile alakalı makalemi okumanızı tavsiye ederim. Çünkü bugünkü yazımı o yapının üzerine kurgulayacağım. Yazıya aşağıdaki linkten ulaşabilirsiniz.
http://www.turgaysahtiyan.com/post/SQL-Server-2008-e28093-Performance-Data-Collection.aspx


Evet şimdi bugünkü yazımızın konusuna dönelim. Ne dedik? Server larımızdan trace verileri toplayacağız ve bunu bir veritabanına yazacağız. Bunun içinde Data Collection Trace Collection Set kullanacağız.


Trace Collection Set in Hazırlanması


Trace Collection Set , Trace dataları toplayıp belirttiğimiz DB ye export edip yazacak olan set tir. Bu seti SQL Server Profiler üzerinden oluşturacağız.

Ben örneğimde 1 sn den fazla süren RPC:Completed ve SQL:BatchCompleted ları toplayacağım.

Şimdi bu seti nasıl oluşturacağımıza adım adım bakalım.

  1. SQL Server Profiler ı çalıştırın. 
    sst1
  2. Toplamak istediğiniz verileri seçin. Ben 1 sn den fazla süren RPC:Completed ve SQL:BatchCompleted ları toplayacağım. Siz toplamak istediğiniz ek bilgileri ekleyebilirsiniz. 
    sst2
  3. 1 sn den fazla sürenler dediğimiz için Filter vermemiz lazım. Bunun için Column Filters ı tıklayıp Duration kriteri 1000ms olarak ayarlıyoruz. Ve Ok e basıp Filter ekranını kapatıyoruz.
    sst3
  4. Run a basarak trace i başlatıyoruz.
  5. Şu an da Profiler 1 sn den fazla süren verileri toplamaya başladı. Ama bizim amacımız bunu Collection Set olarak kaydedip Data Collection da kullanmak. Bu trace i collection set olarak kaydetmek için File >> Export >> Script Trace Definition >> For SQL Trace Collection Set menüsünden trace file a bir isim vererek kaydete basıyoruz.
    sst4

    Collection Set imiz hazır. Bir sonraki adımda bu collection set i Data Collection ına set olarak create edeceğiz.

 


Trace Collection Set in Data Collection a Create Edilmesi

Bir önceki adımda Trace Collection Set i hazırlamıştır. Şimdi ise bu collection set i Data Collection a set olarak create edeceğiz.

Bunun için Trace bilgisi toplamak istediğimiz server a SSMS ile bağlanalım ve save ettiğimiz trace file ı open file menüsünden bulup açalım.

Açılan trace script i aşağıdakine benzer bir yapıda olacaktır.

/*************************************************************/
-- SQL Trace collection set generated from SQL Server Profiler
-- Date: 27/06/2010  15:16:34
/*************************************************************/

USE msdb
GO

BEGIN TRANSACTION
BEGIN TRY

-- Define collection set
-- ***
-- *** Replace 'SqlTrace Collection Set Name Here' in the following script
-- *** with the name you want to use for the collection set.
-- ***
DECLARE @collection_set_id int;
EXEC [dbo].[sp_syscollector_create_collection_set]
    @name = N'SqlTrace Collection Set Name Here',
    @schedule_name = N'CollectorSchedule_Every_15min',
    @collection_mode = 0, -- cached mode needed for Trace collections
    @logging_level = 0, -- minimum logging
    @days_until_expiration = 5,
    @description = N'Collection set generated by SQL Server Profiler',
    @collection_set_id = @collection_set_id output;
SELECT @collection_set_id;

-- Define input and output varaibles for the collection item
DECLARE @trace_definition xml;
DECLARE @collection_item_id int;

-- Define the trace parameters as an XML variable
SELECT @trace_definition = convert(xml, 
N'

</FILTERS>

');

-- Retrieve the collector type GUID for the trace collector type
DECLARE @collector_type_GUID uniqueidentifier;
SELECT @collector_type_GUID = collector_type_uid FROM [dbo].[syscollector_collector_types] WHERE name = N'Generic SQL Trace Collector Type';

-- Create the trace collection item
-- ***
-- *** Replace 'SqlTrace Collection Item Name Here' in the following script
-- *** with the name you want to use for the collection item.
-- ***
EXEC [dbo].[sp_syscollector_create_collection_item]
   @collection_set_id = @collection_set_id,
   @collector_type_uid = @collector_type_GUID,
   @name = N'SqlTrace Collection Item Name Here',
   @frequency = 900, -- specified the frequency for checking to see if trace is still running
   @parameters = @trace_definition,
   @collection_item_id = @collection_item_id output;
SELECT @collection_item_id;

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @ErrorNumber int;
DECLARE @ErrorLine int;
DECLARE @ErrorProcedure nvarchar(200);
SELECT @ErrorLine = ERROR_LINE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
END CATCH;
GO


Bu collection set te isim gibi bazı alanları değiştirmemiz mümkün ama biz bu örneğimiz için herhangi bir değişiklik yapmadan F5 yada execute basarak script i çalıştıralım.

Eğer herhangi bir hata vermediyse tanımlama işlemlerimizi bitirmiş oluyoruz. Yeni oluşturduğumuz bu collection set i Data Collection altında görmemiz mümkün.

sst5

Gördüğünüz gibi Collection Set şu anda stop durumda. Enable etmek için sağ tıklayıp çıkan menüde Start Data Collection Set yazısını tıklayalım.

sst6

 

Trace Bilgisi Toplama ve Sorgulama


Tanımlamalarız bitti. Artık Data Collection yapımız 1 sn den fazla süren sorguları toplamak üzere ayarlandı. Şimdi bir örnek yapalım ve gerçekten trace bilgisini toplayıp toplamadığına bakalım.

1 sn den fazla süren sorgular dediğimiz için aşağıdaki gibi bir sorguyu SSMS te çalıştıralım. Bu sorgu 5 sn bekledikten sonra select i çekecektir.

WAITFOR DELAY '00:00:05'
Select * from sys.Databases

 

Normal şartlarda Collection Set 15 dk da bir verileri toplayıp DB ye yazacaktır. Biz bu süreyi beklememek için Collection Set i manuel olarak çalıştıralım.

Bunun için Trace Collection set imize sağ tıklayıp Collect and Upload Now yazısını tıklayalım.

sst7

Job ın görevini bitirmesini bekledikten sonra verilerin toplandığı DataCollectionDW DB sine bağlanalım ve aşağıdaki sorguyu çalıştıralım.

select * from snapshots.trace_data


sst8

Ola. Gördüğünüz gibi bizim sorgumuzu trace lemiş ve DB ye yazmış :)

Bu şekilde istediğiniz bütün trace bilgilerini DB de toplayabilir ve daha sonra sorgulayabilirsiniz. Geriye dönük yapılan işlemleri görmenin, performans değerlendirmelerinin önemli olduğu durumlarda bu yapı bize oldukça güzel faydalar sağlayacaktır.

Bugünkü yazımız bu kadar. Anlaşılmayan noktalarda yorum bölümünü kullanabilirsiniz.

 

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Performance Data Collection SQL Server 2008 ile gelen bir feature dır. Express sürümü hariç bütün Edition larda bulunmaktadır.

Adından da anlaşılacağı üzere DB ler için performance dataları toplamaya ve toplanan bu dataları raporlamaya yarayan bir yapıdır.

Özel bir veritabanı data warehouse olarak kullanılmakta ve toplanan datalar bu veritabanına yazılmaktadır.

Periyodik(Default u 15 dakika) olarak bilgileri topladığı için geriye dönük olarak belirli bir zaman aralığı için rapor alınabilmektedir.

Örneğin Disk Usage Report, Query Report veya server side trace bilgileri raporlanabilmektedir.

Örneğimizi tapmaya başlayalım. Ben bu örneğimde aşağıdaki adımlar üzerinde duracağım.

  • Data Warehouse un kurulumu.
  • Windows login in create edilmesi.
  • SQL Agent Service Account ların değiştirilmesi
  • Collection Set lerin ayarlanması
  • Raporların İzlenmesi

 

Data Warehouse Kurulumu


Daha öncede bahsettiğim gibi toplanan datalar ayrı bir veritabanında tutulmaktadır. Bu veritabanı verinin toplandığı server ın üzerinde olabileceği gibi başka bir server da da bulunabilmektedir. Biz yapımızı geniş kapsamlı düşüneceğiz. Örneğin sisteminizde ki bütün server lardan veri toplayıp farklı bir serverdaki datawarehouse a yazdığımızı düşünelim.

Örneğimizde S10Ent instance ından verileri toplayıp TS2008_1 instance ında ki datawarehouse a yazacağız.

dc1

TS2008_1 instance ındaki datewarehouse kurulumuna geçelim.

Management >> Data Collection a sağ tıklayıp Configure Management Datawarehouse yazısını tıklayalım ve çıkan ekrana next diyerek bir sonraki ekrana geçelim.

Gelen ekranda Create or upgrade a management data warehouse seçeneği seçerek next e basalım.

dc2

Gelen ekranda new butonuna basarak create edeceğimiz data warehouse için bir isim girelim. Örneğin DataCollectionDW ismini kullanalım ve OK e basarak bir önceki ekrana geçelim.

dc3

Bu ekranda Next e basarak user seçimini yapacağımız ekrana geçelim.

dc4

Bu ekranda NT AUTHORITY\SYSTEM account unu seçip next e basalım. Siz diğer hesaplardan kullanacaklarınızıda seçebilirsiniz.

Finish e basarak işlemi sonlandıralım.

TS2008_1 instance de data warehouse hazırlıkları tamamlanmış oldu.

dc5

 


Windows Login Create Edilmesi


Farklı serverlardan veri toplayıp bunları tek bir data warehouse server ına yazacağımız için SQL Server Agent service account u olarak kullanmak üzere bir windows yada domain account u oluşturmanızı tavsiye ederim.

Oluşturacağınız bu account administrator haklarına sahip olursa ekstra bir ayar yapmanıza gerek kalmayacaktır. Ama administrator olmaz ise Logon Account as a Service e bu account ın her data toplanan server için eklenmesi gerekmektedir. Ayrıca her serverda açılan, collect edilen dataların tutuldukları folder larda bu account un yetkilendirilmesi gerekmektedir.

Biz bu örneğimizde administrator haklarına sahip bir windows user kullanacağız. UserDataCollect isimli bir windows user create ederek bir sonraki adıma geçiyoruz.

 

SQL Agent Service Account ların Değiştirilmesi


Bir önceki adımda dediğim gibi SQL Server Service account ların yeni tanımladığımız windows user olan UserDataCollect ile değiştireceğiz.

Bunun için SQL Server Configuration Manager daki S10Ent ve TS2008_1 instance larında ki SQL Server Agent service lerinde değişiklik yapacağız.

SQL Server Configuration Manager ı açıyoruz ve değiştirmek istediğimiz service ın üzerine sağ tık yapıp properties ekranına geçiyoruz.

dc6

Gelen ekranı resimde görüldüğü gibi düzenliyoruz yani service account olarak UserDataCollect windows account unu kullanıyoruz.

dc7

Aynı işlemi diğer service yada servis ler içinde yapıyoruz. Dediğim gibi bütün data toplanacak server ların agent larında ve data warehouse un olduğu server ın agent ında bu service account değişikliğini yapmamız gerekmekte.

Ayrıca bu windows user ı server larda sysAdmin yapmanızda gerekecektir. Bu noktaya da dikkat etmenizi öneririm.

 

Collection Setlerin Ayarlanması


Artık Data toplanacak server larda tanımlamaları yapma vakti geldi.

Bunun için data toplanacak server da(S10ENT instance ı) Management >> Data Collection a sağ tıklayıp Configure Management Data Warehouse yazısını tıklayalım.

dc8

Gelen ekrana next diyelim ve bir sonraki ekrana geçelim. Bu ekranda bu sefer Set up Data Collection yazısını seçeceğiz. Çünkü artık data toplama ayarlarını yapmaya başlıyoruz.

dc9

Next e basarak bir sonraki ekrana geçelim. Bu ekranda Data Warehouse için ayarladığımız server için bilgiler gireceğiz.ServerName ve Database name seçimini yapalım. Bu bilgiler Data Warehouse un bulunduğu server ve DB adı. Yani bizim örneğimiz için TS2008_1 instance ında DataCollectionDW database i.

Cache Directory kısmında da toplanan verilen yazılması için bir folder seçeceğiz. Bu folder data toplanan server da yani S10ENT instance ının çalıştığı serverda olacak.

C:\ de yeni bir klasor açalım. DataCollectTemp adını verelim ve Cache Directory kısmına C:\DataCollectTemp yazıp next e basalım.

Bilgileri doldurduktan sonra ekran aşağıdaki ekrana benzer olacaktır.

dc10

Not: Daha öncede söylediğim gibi; eğer SQL Server Agent Service Account u olarak bir administrator account u kullanmazsanız, bu account u bu folder ı yetkilendirmeniz gerekmektedir.

Finish e basarak işlemi sonlandıralım.

İşlem tamamlandıktan sonra Data Collection altında aşağıda ki resimde görüldüğü gibi Data Collection Set ler oluşacaktır.

dc11

Aynı zamanda Data Collection setler için oluşan jobları SQL Server Agent >> Jobs un altında görebilirsiniz.

 

Raporların İzlenmesi


Dediğimiz gibi Data Collector default olarak 15 dakikada bir server dan bilgileri toplayacak ve data warehouse a yazacaktır. Peki biz bu bilgileri nasıl raporlayacağız.

Bunun için Data toplanan server da (S10ENT) Management >> Data Collection >> Sağ Tık >> Reports >> Management Data Warehouse kısmından istediğimiz report u seçebiliriz.

dc12

Disk Usage Report

Örneğin Disk Usage Report a bakalım. Management >> Data Collection >> Sağ Tık >> Reports >> Management Data Warehouse >> Disk Usage Report u tıklayalım.

Gelen rapor da server da bulunan DB lerin disk tekapladıkları alanların detayı görülmekte.

Database bazında, data file için başlangıç boyutu, büyüme trend i, şu anki boyutu ve günlük büyüme değerlerini görebiliriz. Aynı değerleri log file ların içinde alabiliriz.

dc13

Bu raporda AdventureWorks DB sine tıklayalım. Gelen raporda AdventureWorks DB sinin data ve log file larının hangi bileşenlerden oluştuğu görülmekte.

dc14

Aynı raporda Data/Log Files Autogrow/Autoshrink Events kısmına dikkatinizi çekmek istiyorum. Bu kısım data ve log file larının auto growth olduğu zamanları ve ne kadar sürdüğünü göstermekte. Eğer çok sık auto growth oluyorsa auto growth değerini performans amaçlı büyütmekte fayda var. bu işlemin nasıl yapıldığı Database ve Database File lar makalemizde inceleyeceğiz.

Server Activity Report


Bu raporda ise Server ın aktivitelerini görüyoruz. CPU, Memory, Disk ve Network kullanım grafikleri ile beraber beklemelerin nereden kaynaklandığını tarih aralığı vererek raporlayabiliriz.

 dc15


Query Statistics History


Son olarak Query İstatistik Tarihçesi raporuna bakalım. Bu rapor vasıtasıyla da CPU, Duration, Total I/O, Physical Reads, Logical Writes bazından bize en pahalı 10 queryi görebiliriz.

dc16

ÖZET


Data Collection SQL Server 2008 ile gelen güzel özelliklerden biri. Server lardaki aktiviteleri raporlayabilir ve performans sıkıntıları çözümünda kullanabiliriz.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan