Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Aktivität und Fortschritt einer Transaktion ermitteln

29. September 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Lange Transaktion wie umfangreiche Updates in einem Dataware House lässt man meist per Job über Nacht laufen, da sitzt man nicht daneben und muss waren, bis es fertig ist; Hauptsache es ist bis zum nächsten morgen fehlerfrei durchgelaufen.

Wenn man aber gerade dabei ist, ein System auszubauen, muss man auch schon mal einen Langläufer anstarten und dann sitzt man da: Wie lange dauert es denn noch, wie weit ist die Transaktion bereits und macht er überhaupt noch was? Da wäre es schön, mal in den Microsoft SQL Server hineinschauen zu können.

Während einer solchen Wartezeit habe ich mal versucht, über die DMV (Dynamic Management Views) den Fortschritt einer Transaktion zu ermitteln; bei der DMV  sys.dm_tran_database_transactions bin ich fündig geworden. Die View liefert einem zu einer Transaktion die Anzahl der Transaktionsprotokoll-Einträge sowie die reservierte und verwendet Größe des Logs.

Das ist recht interessant und leicht verwirrend zu beobachten. Ich habe mal auf einem MS SQL Server 2005 mit SP 3 (9.00.4035.00) folgendes ausgeführt:

 

 UPDATE Tabelle 
 SET Feld1 = Feld1 
 WHERE id <= 500000 

 

Id ist eine Identity, es werden also 500k Datensätze upgedatet, wobei sich eigentlich nicht ändert, da das Feld der aktuelle Wert zugewiesen wird. Aber das kennt man schon, das trotzdem ein Update und damit eine Protokollierung im LOG erfolgt.

Das untenstehende zeigt das auch schon auf, man kann zusehen, wie die benötigt Größe & Anzahl steigt. Nun das interessante und zwar start ich das gleiche Update noch einmal, nur mit der WHERE Klausel

 WHERE id <= 600000 

Es werden also noch mal die gleichen 500k + weitere 100k an Datensätze upgedatet.

Beobachtet man mit dem Script diese Transaktion, werden zunächst nur 2 Einträge im LOG erzeugt, dann geschieht eine Zeit lang erst mal nichts weiter, bis dann doch noch 100k Sätze ins Log geschrieben werden; wohl die für die erstmal upgedateten.

Da scheint doch zumindest etwas optimiert zu sein.

 

Ich habe auch schon versucht, die „Restlaufzeit“ aufgrund der Daten zu schätzen, aber aufgrund des zuvor genannten Verhaltens ist das nicht so einfach. Vielleicht hat da jemand anderes noch eine Lösung zu.

 

Hier nun das T-SQL Script dazu, viel Vergnügen beim der Trans-Beobachtung:

 

 -- Selektion der Transaktionen eines Prozesses mit den  
 -- Daten des Transaktionsprotokolles wie Größe und 
 -- Anzahl Log-Sätze 
 DECLARE @spid as int; 
 SET @spid = 57; 
   
 SELECT PRO.spid, TDT.transaction_id, 
  CASE TDT.database_transaction_type 
  WHEN 1 THEN 'Read/Write' 
  WHEN 2 THEN 'ReadOnly' 
  WHEN 3 THEN 'System'  
  END AS TransType, 
  CASE TDT.database_transaction_type 
  WHEN 1 THEN 'Not initialized' 
  WHEN 3 THEN 'Initialized, no LOG generated' 
   WHEN 4 THEN 'LOG generated' 
  WHEN 5 THEN 'Preparing trans' 
  WHEN 10 THEN 'Committed' 
  WHEN 11 THEN 'Rolling back' 
  WHEN 12 THEN 'Committted, LOG generated, but not persisted'  
  END AS TransState, 
  PRO.waittime, PRO.waitresource, PRO.last_batch, 
  TDT.database_transaction_begin_time AS TransBegin, 
  TDT.database_transaction_log_record_count AS LogRecordCount, 
  TDT.database_transaction_log_bytes_used / 1024 AS LogKBUsed, 
   TDT.database_transaction_log_bytes_reserved / 1024 AS LogKBReserved, 
  CONVERT(decimal(8, 2),  
  DATEDIFF(ss, TDT.database_transaction_begin_time, GetDate()) / 60.0) AS DurationMin 
 FROM sys.dm_tran_database_transactions AS TDT 
  INNER JOIN sys.dm_tran_session_transactions AS TST 
  ON TDT.transaction_id = TST.transaction_id 
  INNER JOIN sys.sysprocesses AS PRO 
  ON TST.session_id = PRO.spid 
 WHERE PRO.spid = @spid 
  AND NOT TDT.database_transaction_begin_time IS NULL 

Diesen Post teilen

Repost 0

Kommentiere diesen Post