t-sql

DECLARE @sql nvarchar(max);

-- Abfrage auf System Views.
SET @sql =
N'SELECT *
  FROM sys.tables AS TBL
       INNER JOIN
       sys.columns AS COL
           ON TBL.object_id = COL.object_id';
SELECT FRS.column_ordinal
      ,FRS.system_type_name
      ,FRS.source_schema
      ,FRS.source_table
      ,FRS.source_column
      ,FRS.name AS finalname
      ,FRS.source_database
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS FRS;

hier sieht man sogar, aus welcher Systemtabelle die Daten kommen:

Umrechnungskurse für Währungen werden in vielen Fachbereichen und Anwendungsfällen benötigt. Der Börsenbroker benötigt sie im Sekundentakt für die Aktienbewertung, beim Verkauf von Import-Artikel mit Tagespreisen wird der aktuelle Tageskurs benötigt und die Buchhaltung verwendet für die Fremdwährung OP Bewertung einen Monats-Durchschnittskurs.

Die EZB Europäische Zentralbank veröffentlich die Tages-Umrechnungskurse seit Anfang 1999 als XML Datei im Internet. Man kann eine Datei mit den Kursen des letzten Banktages (TARGET Tag), der letzten 90 Tagen und die gesamte Aufstellung erhalten.

Diese Xml Daten bieten sich natürlich dazu an, sie einzulesen und per T-SQL weiter zu verarbeiten, um so eine Referenztabelle mit Umrechnungskursen zu pflegen. Etwas gekürzt sieht der Inhalt der Xml Datei so aus:

 <?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" 
 xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
 <gesmes:subject>Reference rates</gesmes:subject>
 <gesmes:Sender>
 <gesmes:name>European Central Bank</gesmes:name>
 </gesmes:Sender>
 <Cube>
 <Cube time='2011-04-18'>
 <Cube currency='USD' rate='1.4275'/>
 <Cube currency='JPY' rate='118.25'/>
 <Cube currency='BGN' rate='1.9558'/>
 <Cube currency='CZK' rate='24.188'/>
 </Cube>
 </Cube>
</gesmes:Envelope> 

Grundsätzlich ist das Verarbeiten kein Problem, allerdings muss man, um die Xml Daten überhaupt lesen zu können, zunächst die verwendete Namespace mittels WITH XmlNameSpaces deklarieren, hier gesmes:Envelope. Will man zugleich auch noch CTE (Common Table Expressions) verwenden, muss man nur berücksichtigen, das XmlNameSpaces an erste Stelle der WITH Anweisung gesetzt wird, die CTE’s dürfen erst darauf folgen.

Hat man das Namespace erst einmal deklariert, erfolgt das weitere Auslesen der Daten wie bei allen anderen Xml Daten auch. Das ganz Statement für eine Tabelle, das Einlesen und Umwandeln sieht dann so aus:

  -- ECB Währungskurse

 -- Tabelle für die Tageskurse anlegen, sofern noch nicht vorhanden. 

 IF OBJECT_ID('[dbo].[EcbDailyExchangeRates]', 'U ') IS NULL
 CREATE TABLE [dbo].[EcbDailyExchangeRates]
 ( [ExcDate] date NOT NULL
 ,[Currency] char(3) NOT NULL
 ,[Rate] decimal(19, 6) NOT NULL
 ,CONSTRAINT [PK_EcbDailyExchangeRates] PRIMARY KEY CLUSTERED
 ( [ExcDate] ASC
  ,[Currency] ASC
 ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF 

  ,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
 );
GO 

 -- Sourcen der Ecb Xml Dateien:
-- Letzter Tag: http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
-- Letzten 90 Tage: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml
-- Komplett: http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml

 

 -- Laden der Xml Datei in eine Variable 

 DECLARE @ecb XML;
SET @ecb = (SELECT CONVERT(xml, EcbSrc.BulkColumn) AS XmlRates
 FROM OPENRowsET( BULK N'D:\eurofxref-hist.xml'
 ,SINGLE_BLOB) AS EcbSrc);
  

 -- Selektion der Kurse und Einfügen von fehlenden Werten 
;WITH XMLNAMESPACES
 ( 'http://www.gesmes.org/xml/2002-08-01' as gesmes
 ,DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref')

 

 INSERT INTO [dbo].[EcbDailyExchangeRates]
 ( [ExcDate], [Currency], [Rate])
SELECT Cubes.Rows.value('@time', 'date') AS [ExcDate]
 ,Nodes.Rows.value('@Currency', 'char(3)') AS [Currency]
 ,Nodes.Rows.value('@Rate', 'decimal(19, 6)') AS [Rate]
FROM @ecb.nodes('/gesmes:Envelope/Cube/*') AS Cubes(Rows)
 CROSS APPLY
 Cubes.Rows.nodes('Cube') as Nodes(Rows)
 LEFT JOIN
 EcbDailyExchangeRates AS Dst
 ON Dst.ExcDate = Cubes.Rows.value('@time', 'date')
 AND Dst.Currency = Nodes.Rows.value('@Currency', 'char(3)')
WHERE Dst.ExcDate IS NULL
 AND NOT Nodes.Rows.value('@Currency', 'char(3)') IS NULL; 

Die DMV sys.dm_os_ring_buffers, die mit Microsoft SQL Server Version 2005 eingeführt wurde, gehört zu den wenigen, die offiziell nicht dokumentiert ist und in der BOL zu den DMVs zum Betriebssystem steht „..not supported …only for information … not guaranteed …“. Nun, für Applikationen würde ich die DMV als Informationsquelle nicht verwenden, aber ein Blick schadet nicht, vielleicht liefert sie doch interessante Informationen.

Die View selbst ist sehr „flach“, nur 4 Felder:

-       ring_buffer_address varbinary(8): Mit der kann ich noch nichts anfangen

-       ring_buffer_type nvarchar(60): Eine Typebezeichnung in Klartext

-       timestamp bigint: Weder datetime noch rowversion, sondern reine Zahlen

-       record nvarchar(2048): Die Daten sehen aus wie XML, sind nur nicht so deklariert

Der Inhalt von „timestamp“ war schnell geklärt, es sind die Ticks (= Millisekunden) seit dem Rechnerneustart, hat also nichts mit @@TIMETICKS zu tun. Über das Feld ms_ticks aus dem DMV sys.dm_os_sys_info kann man den aktuellen Ticks-Stand ermitteln und darüber auf den eigentlichen Zeitpunkt rückrechnen.

Welche Type es (aktuell) gibt, kann mit einer einfachen Abfrage ermitteln:

 SELECT ring_buffer_type 

  ,COUNT(*) AS Occured 

 FROM master.sys.dm_os_ring_buffers 

 GROUP BY ring_buffer_type 

 ORDER BY ring_buffer_type 

Ergebnis (auf Laptop, der kurz vorher gestartet wurde):

Na, das erste, was hier mein Interesse weckt, ist natürlich RING_BUFFER_RESOURCE_MONITOR. Wie schon erwähnt, sehen die Daten in „record“ wie XML aus und in den Datentypen kann man es auch konvertieren. Ein solcher „record“ für einen Resource_Monitor Eintrag sieht so aus:

 <Record id="0" type="RING_BUFFER_RESOURCE_MONITOR" time="1261574341"> 

  <ResourceMonitor> 

  <Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification> 

  <IndicatorsProcess>0</IndicatorsProcess> 

  <IndicatorsSystem>1</IndicatorsSystem> 

  <NodeId>0</NodeId> 

  <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect> 

  <Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="0">0</Effect> 

  <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect> 

  </ResourceMonitor> 

  <MemoryNode id="0"> 

  <ReservedMemory>1671680</ReservedMemory> 

  <CommittedMemory>16636</CommittedMemory> 

  <SharedMemory>0</SharedMemory> 

  <AWEMemory>0</AWEMemory> 

  <SinglePagesMemory>2496</SinglePagesMemory> 

  <MultiplePagesMemory>10232</MultiplePagesMemory> 

  </MemoryNode> 

  <MemoryRecord> 

  <MemoryUtilization>100</MemoryUtilization> 

  <TotalPhysicalMemory>2086596</TotalPhysicalMemory> 

  <AvailablePhysicalMemory>832044</AvailablePhysicalMemory> 

  <TotalPageFile>4416176</TotalPageFile> 

  <AvailablePageFile>2442676</AvailablePageFile> 

  <TotalVirtualAddressSpace>2097024</TotalVirtualAddressSpace> 

  <AvailableVirtualAddressSpace>326988</AvailableVirtualAddressSpace> 

  <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace> 

  </MemoryRecord> 

 </Record> 

Die Namen der Tags sind wie die aus den Speicher Performance Counters von Windows. XML lässt sich in SQL Server ab 2005 prima abfragen und das machen wir mal.

Auf einem Produktiv-System ausgeführt gibt es einige „RESOURCE_MEMPHYSICAL_HIGH“ Einträge; was für einen Server mit laufenden MS SQL Server aber auch nichts Ungewöhnliches ist.

Aber so kann man mit einfachen Mitteln sein System etwas monitoren und das auch noch rückwirkend.

 DECLARE @actualTS BIGINT 

 SET @actualTS = (SELECT ms_ticks FROM sys.dm_os_sys_info) 

 PRINT @actualTS 

 SELECT ORB.EventTime 

  ,ORB.Notif 

  ,ORB.AvailPhyMem 

  ,ROUND(CONVERT(float, 100 * AvailPhyMem) / TotalPhyMem, 3) AS PercentAvailable 

  ,ORB.MemUtil 

  ,ORB.ResvMem 

  ,ORB.CommMem 

  ,ORB.recordXml 

 FROM (SELECT DATEADD(s, EventTs, GETDATE()) AS EventTime 

  ,ORB.recordXml.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(50)') AS Notif 

  ,ORB.recordXml.value('(./Record/MemoryNode/ReservedMemory)[1]', 'int') AS ResvMem 

  ,ORB.recordXml.value('(./Record/MemoryNode/CommittedMemory)[1]', 'int') AS CommMem 

  ,ORB.recordXml.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS MemUtil 

  ,ORB.recordXml.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'int') AS TotalPhyMem 

  ,ORB.recordXml.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS AvailPhyMem 

  ,recordXml 

  FROM (SELECT CONVERT(int, (ORB.timestamp - @actualTS) / 1000) AS EventTs 

  ,CONVERT(xml, record) AS RecordXml 

  FROM master.sys.dm_os_ring_buffers AS ORB 

            WHERE ORB.ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'

            ) AS ORB

      ) AS ORB

ORDER BY EventTime desc

SQL FileStream zum effektiven Speichern von BLOBs = Binary Large Objects ist im Microsoft SQL Server ab Version 2008 neu hinzugekommen. Es kombinierte die Vorteile der Ablage im SQL Server als VarBinary Daten mit der Möglichkeit der transaktionellen Verarbeitung und der Ablage im File-System mit der performanten Zugriffsmöglichkeit über Windows Streaming / API Zugriff.

Es gibt ein wirklich sehr gutes und umfangreiches Whitepaper „FILESTREAM Storage in SQL Server 2008“ im MSDN Bereich von Paul S. Randall, das alle Aspekte rund um FileStream beleuchtet. Was soll ich dem hinzufügen?

Ich habe nun auch mal etwas experimentiert und ein paar erste Erfahrungen gesammelt:

·         Mein erster Test war unter Windows XP mit MS SQL Server 2008 Express Edition ohne SP, der unter dem Account „NetworkServices“ lief. Die Anlage der Dateigruppe für FileStream schlug mit einer nicht gerade aussagekräftigen Fehlermeldung fehl wie
Unable to open the physical file "c:\Program Files\Microsoft SQL Server\...". Operating system error -2147024891: "0x80070005(Access is denied.)"
 Eine kurze Recherche ergab, dass der Dienst unter einem Windows Account laufen muss, damit es funktioniert. Einen entsprechenden Eintrag gibt es bereits bei MS Connect. Mit meiner Developer Edition unter Windows Vista mit Dienstkonto „Local System“ hingegen funktionierte es ohne Änderungen.

·         Änderungen an FileStream Daten, also UPDATE und DELETE, werden nicht direkt im Transaktionsprotokoll protokolliert, sondern in Form von revisionierten Dateien. Diese verbleiben im Dateisystem bis zur nächsten Voll- bzw. Log-Sicherung; das muss man bei der Planung des Speicherplatzes beachten.

 USE [master] 

 GO 

 EXEC sp_configure filestream_access_level, 2; 

 GO 

 RECONFIGURE; 

 GO 

 ALTER database AdventureWorks 

 ADD FILEGROUP [FileStreamFG] 

 CONTAINS FILESTREAM 

 GO 

 ALTER database AdventureWorks 

 ADD FILE 

 ( 

  NAME= 'FileStream', 

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL10DEV\FG' 

 ) 

 TO FILEGROUP [FileStreamFG] 

 GO 

 USE [AdventureWorks] 

 GO 

 CREATE TABLE dbo.FileStreamStorage 

  ( 

  [Id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY, 

  [Guid] uniqueidentifier NOT NULL UNIQUE ROWGUIDCOL DEFAULT NEWID(), 

  [FileName] varchar(255) NOT NULL, 

  [FileStreamData] varbinary(MAX) FILESTREAM NULL  

  ) ON [PRIMARY] 

  FILESTREAM_ON [FileStreamFG] 

 GO 

 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 

 VALUES ('Test1.txt', CONVERT(varbinary(max), N'Hello World')); 

 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 

 VALUES ('Test2.txt', CONVERT(varbinary(max), N'Hello World again')); 

 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 

 VALUES ('Test3.txt', CONVERT(varbinary(max), N'Hello World again')); 

 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 

 VALUES ('Test4.txt', CONVERT(varbinary(max), 'Hello World again')); 

 GO 

 DELETE FROM dbo.FileStreamStorage 

 WHERE [Id] <= 4; 

Gute, weil vollständige und korrekte Referenzdaten, sind schwierig zu finden; in der Regel kann man sie nur bei kommerziellen Anbieter für einiges an Geld beziehen.

Referenzdaten, die dann auch noch regelmäßig aktualisiert werden und zu allem Überfluss auch noch frei verfügbar sind, sind eine Seltenheit.

Das Verzeichnis für deutsche Bankleitzahlen ist so eine Seltenheit. Sie wird von der Deutschen Bundesbank herausgegeben, ist auf deren Seite frei verfügbar und wird auch quartalsweise aktualisiert. Eine Dokumentation des Dateiformates gibt es auch.

Das Dateiformat ist einfach gehalten, es eine Textdatei und die Spalten haben jeweils eine fester Breite. In dem Fall ist die einfachste Möglichkeit, die Daten zu importieren, mit OpenRowSet und der Bulk Option zu arbeiten. Als ich mir die Dokumentation dazu durchgelesen habe, fiel mir auf, dass zwar ausführlich die Anwendung für zeichengetrennte Formate beschrieben ist inklusive Beispiele, aber in keiner Weise, wie man Daten mit fester Spaltenbreite handhabt. Also Mut zur Lücke, eine Formatdatei definiert ohne Trennzeichen und die Spaltenbreite angegeben; die maximal Breite muss man eh festlegen und siehe da, der Import per BULK funktioniert einwandfrei.

Kleines Problem ist, das der Dateiname „versioniert“ wird, also der Dateiname das Datum der Veröffentlichung beinhaltet. Zwar gibt es fest definierte Regeln, wann das neue Verzeichnis veröffentlicht wird, man kann das Datum errechnen, aber das erschwert den Automatismus, um die Datei per SSIS über FTP / http herunter zu laden, um sie dann weitere zu verarbeiten.

Hier nun der Aufbau der Format-Datei und das Statement für den Bulk – Import; die gesamte SSMS Solution gibt es auch zum Download, sie beinhaltet auch das Tabellen Design.

Format Datei BLZ_Format.txt:

 8.0 

 13 

 1 SQLCHAR 0 8 "" 1 Blz "" 

 2 SQLCHAR 0 1 "" 2 BlzFuehrend "" 

 3 SQLCHAR 0 58 "" 3 Bezeichnung "" 

 4 SQLCHAR 0 5 "" 4 Plz "" 

 5 SQLCHAR 0 35 "" 5 Ort "" 

 6 SQLCHAR 0 27 "" 6 Kurzbezeichnung "" 

 7 SQLCHAR 0 5 "" 7 InstitutsnummerPAN "" 

 8 SQLCHAR 0 11 "" 8 Bic "" 

 9 SQLCHAR 0 2 "" 9 PruefzifferMethode "" 

 10 SQLCHAR 0 6 "" 10 Nummer "" 

 11 SQLCHAR 0 1 "" 11 Aenderung "" 

 12 SQLCHAR 0 1 "" 12 Loeschung "" 

 13 SQLCHAR 0 8 "\n" 13 NachfolgerBlz "" 

Das SQL Statement für den Import über OpenRowSet.

 -- Daten einfügen 

 INSERT INTO [BLZ].[dbo].[Blz] 

  ([Blz] 

  ,[BlzFuehrend] 

  ,[Bezeichnung] 

  ,[Plz] 

  ,[Ort] 

  ,[Kurzbezeichnung] 

  ,[InstitutsnummerPAN] 

  ,[Bic] 

  ,[PruefzifferMethode] 

  ,[Nummer] 

  ,[Aenderung] 

   ,[Loeschung] 

  ,[NachfolgerBlz])  

 SELECT [Blz] 

  ,CONVERT(tinyint, [BlzFuehrend]) AS BlzFuehrend 

  ,RTRIM([Bezeichnung]) AS [Bezeichnung] 

  ,[Plz] 

  ,RTRIM([Ort]) AS [Ort] 

  ,RTRIM([Kurzbezeichnung]) AS [Kurzbezeichnung] 

  ,CONVERT(int, CASE WHEN [InstitutsnummerPAN] = '' 

  THEN NULL 

  ELSE [InstitutsnummerPAN] 

  END) AS [InstitutsnummerPAN] 

  ,CASE WHEN [Bic] = ''  

   THEN NULL 

  ELSE [Bic] END AS [Bic] 

  ,[PruefzifferMethode] 

  ,CONVERT(int, [Nummer]) AS [Nummer] 

  ,[Aenderung] 

  ,CONVERT(bit, [Loeschung]) AS [Loeschung] 

  ,[NachfolgerBlz] 

 FROM OPENROWSET(BULK N'D:\Projekte\Blz\blz_20100308.txt' 

  ,FORMATFILE = N'D:\Projekte\Blz\blz_format.txt' 

  ,CODEPAGE = 'OEM'  

  ,ERRORFILE = N'D:\Projekte\Blz\blz_error.txt' 

  ) AS Blz 

 GO 

Das Boardmittel der Dateigruppen gibt es schon ewig im Microsoft SQL Server und das in allen Editionen, einschließlich der Express Editon (ok, in der Compact Edition gibt es das nicht). Je datenspeicherndem Objekt, also für Tabellen, Indizes, separate je Tabelle für BLOB Felder und auch für Partitionen, kann man festlegen, in welcher Dateigruppe es gespeichert werden soll. Einzige Ausnahme sind die PrimaryKey-Indizes, die werden immer in der gleichen Dateigruppe gespeichert; logischerweise, da sie physikalisch zusammen hängen.

Zum Organisieren von großen (in Hinsicht von Speicherplatz) Datenbanken ist das ein gutes und einfaches Mittel, um den verwendeten Speicherplatz über mehrere Dateien und damit Laufwerke hinweg zu verteilen. Zudem kann man Dateigruppen einzeln Sichern & Wiederherstellen. Last but not least kann man einzelne Dateigruppen auf „ReadOnly“ setzten, um so zum Beispiel Archive-Daten zuverlässig vor Änderungen zu schützen (inkl. durch dem DBA; also einem Selbst,). Folglich braucht man die Daten auch nur einmalig sichern; das spart Backup-Ressourcen.

Wenn man bei der Anlage von Objekte konsequent die richtige Dateigruppe berücksichtigt, ist ja auch alles in Ordnung. Aber wie findet man heraus, welches Objekt in welcher Dateigruppe angelegt wurde. Hinzu kommt evtl. noch, dass man Tabellen partitioniert hat und die Partitionen über mehrere Dateigruppen verteilt sind.

Der SQL Server weiß es natürlich und über die System-Views kann man die Daten auch abfragen; hier das T-SQL Statement dazu:

 -- Auflistung der Objekte + Indizes  

 -- je Dateigruppe / Partion 

 SELECT DS.name AS DataSpaceName 

  ,AU.type_desc AS AllocationDesc 

  ,AU.total_pages / 128 AS TotalSizeMB 

  ,AU.used_pages / 128 AS UsedSizeMB 

   ,AU.data_pages / 128 AS DataSizeMB 

  ,SCH.name AS SchemaName 

  ,OBJ.type_desc AS ObjectType  

  ,OBJ.name AS ObjectName 

  ,IDX.type_desc AS IndexType 

  ,IDX.name AS IndexName 

 FROM sys.data_spaces AS DS 

  INNER JOIN  

  sys.allocation_units AS AU 

  ON DS.data_space_id = AU.data_space_id 

  INNER JOIN sys.partitions AS PA 

  ON (AU.type IN (1, 3)  

  AND AU.container_id = PA.hobt_id) 

  OR 

  (AU.type = 2 

  AND AU.container_id = PA.partition_id) 

  INNER JOIN sys.objects AS OBJ 

  ON PA.object_id = OBJ.object_id 

  INNER JOIN sys.schemas AS SCH 

  ON OBJ.schema_id = SCH.schema_id 

  LEFT JOIN sys.indexes AS IDX 

  ON PA.object_id = IDX.object_id 

  AND PA.index_id = IDX.index_id 

 ORDER BY DS.name 

  ,SCH.name 

  ,OBJ.name
 ,IDX.name 

Jede Datenbank im produktiven Betrieb wird etwas anders verwendet. In die eine wird meistens in zyklischen Abständen geschrieben und nur selten mal gelesen; das wären zum Beispiel Archive-Datenbanken für Alt-Daten. Dann gibt es Datenbanken, in die im laufenden Betrieb in erster Linie die aktuellen Transaktionen mit hohem Detailgrad geschrieben werden, sogenannte OLTP Systeme. Die Daten hier raus werden dann leicht desnormalisiert & voraggregiert in Dataware-House System für weitere Auswertungen überführt; in ein OLAP System. Aus diesen wird naturgemäß mehr gelesen als geschrieben.

Als DBA sollte man die Verwendung seiner Datenbanken kennen, um Speicherplatz entsprechend planen zu können. Archive-Datenbanken ändern sich kaum, werden sowieso gesichert und kaum abgefragt, also kann man sie auf ein einfaches (günstigen) Festplattensystem ablegen. OLAP Datenbanken beruhen auf den Produktiv-Datenbanken, mal von Sicherungen abgesehen, können sie eh wieder neu aufgebaut werden; da sind vor allem schnelle Systeme gefragt wie RAID0, um Abfragen fürs Reporting schnell abzuarbeiten. OLTP hingegen sind das Herzstück der Datenhaltung, hier sollte man immer in ein sicheres sowie auch schnelles Speichersystem investieren.

Im Microsoft SQL Server 2005 (und auch 2008) kann man in einem gewissen Rahmen über die DMV (Dynamic Management Views) Statistiken die Anzahl von gelesenen und geschriebenen Seiten je Datenbank ermitteln. Ausgangspunkt ist dabei die DMV sys.dm_exec_query_stats, die schon für die häufigsten verwendeten Abfragen verwendet wurde und die benötigen Statistiken bereit hält.

Setzt allerdings voraus, das der Server schon eine Zeit lang läuft und die Statistiken aufgebaut sind und wie schon erwähnt mit parametrisierten Abfragen gearbeitet wird, sonst ist jedes (dynamisch generiertes) Statement ein eigenständiges. Nicht immer steht der Datenbank-Kontext für ein Statement fest, diese Queries werden dann unter „AdHoc Queries“ aufgeführt.

Grob aus Erfahrungswerten raus geschätzt, ist in „normalen“ ERP Datenbanken das Verhältnis von Write – Read ~ 1:20. Wie gesagt, das variiert je nach Verwendung.

Mit dem folgenden Statement kann man sich also eine Übersicht verschaffen, wichtig sind hier die LogicalReads und LogicalWrites:

 -- Übersicht der Logical Reads & Writes je Datenbank 

 -- Beruht auf den DMV Statistiken. 

 SELECT ISNULL(DB.name, 'AdHoc Queries') AS DatabaseName 

  ,SUM(EQS.execution_count) AS ExecutionCounts 

  ,SUM(EQS.total_worker_time) AS WorkerTime 

  ,SUM(EQS.total_physical_reads) AS PhysicalReads 

  ,SUM(EQS.total_logical_reads) AS LogicalReads 

  ,SUM(EQS.total_logical_writes ) AS LogicalWrites 

 FROM sys.dm_exec_query_stats AS EQS 

  CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) as EST 

  LEFT JOIN sys.databases AS DB 

  ON EST.dbid = DB.database_id 

 GROUP BY ISNULL(DB.name, 'AdHoc Queries')
ORDER BY LogicalReads DESC, LogicalWrites DESC 

Es ist eine häufige Anforderung, Werte von mehreren Datensätze aus einem MS Sql Server per T-SQL als eine Coma Separated List (csv) auszugeben, z.B. im Reporting.

Analog dazu gibt es die Anforderung zum Umkehrschluß: Man hat einen csv-String und möchte das als RecordSet erhalt, um z.B. als Referenz darauf zu JOINen.

Wie geht’s? Mit ein paar kleinen Tricks recht einfach.

Zunächst DataSet => csv

Hier gibt es wie immer mehrere Varianten, ich zeige 2. Die eine kumuliert die Wert über eine Variable und gibt die dann aus, die andere verwendet die XML Funktion (ab Version 2005) und ersetzt für das Ergebnis die Tags.

 SET NOCOUNT ON; 

 -- Werte anlegen 

 CREATE TABLE #csv 

  (value varchar(3)); 

 INSERT INTO #csv VALUES ('Jan'); 

 INSERT INTO #csv VALUES ('Feb'); 

 INSERT INTO #csv VALUES ('Mar'); 

 INSERT INTO #csv VALUES ('Apr'); 

 INSERT INTO #csv VALUES ('May'); 

 INSERT INTO #csv VALUES ('Jun'); 

 INSERT INTO #csv VALUES ('Jul'); 

 INSERT INTO #csv VALUES ('Aug'); 

 INSERT INTO #csv VALUES ('Sep'); 

 INSERT INTO #csv VALUES ('Oct'); 

 INSERT INTO #csv VALUES ('Nov'); 

 INSERT INTO #csv VALUES ('Dec'); 

 GO 

 -- Weg über eine Variable 

 DECLARE @csv varchar(200) = ''; 

 SELECT @csv = @csv + value + ';' 

 FROM #csv 

 -- Liste ausgeben 

 SELECT @csv AS csv; 

 -- Über XML; da muss zwischendurch konvertiert und Tags entfernt werden 

 DECLARE @sep char(1); 

 SET @sep = ';'; 

 SELECT REPLACE(REPLACE(StrTbl.StrValue, '<tag>', ''), '</tag>', '') AS csv 

 FROM  

  (SELECT StrValue = 

  CONVERT(varchar(400), 

  (SELECT tag = Value + @sep 

  FROM #csv 

  FOR XML PATH ('tag'), Type 

  ) 

  ) 

  ) AS StrTbl 

 GO -- Aufräumen 

 DROP TABLE #csv; 

Ergebnis:

 csv 

 ----------------------------------------------- 

 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 

 csv 

 ----------------------------------------------- 

 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 

So, und nun wieder zurück. Es gibt einen csv String und der soll nun wieder aufgesplittet. Auch nicht wirklich aufwendig, wenn man es über eine CTE (Common Table Expression) erledigt:

 DECLARE @csv varchar(200); 

 DECLARE @sep char(1); 

 -- Voraussetzung: Abschließender Separator 

 SET @csv = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec;'; 

 SET @sep = ';'; 

 WITH cte (Id, Value, Rest) 

 AS (SELECT 1 as Id 

  ,SUBSTRING(@csv, 1, CHARINDEX(@sep, @csv, 1) - 1) AS Value 

  ,SUBSTRING(@csv, CHARINDEX(@sep, @csv, 1) + 1, LEN(@csv)) AS Value 

  UNION ALL 

  SELECT Id + 1 AS Id 

  ,SUBSTRING(Rest, 1, CHARINDEX(@sep, Rest, 1) - 1) AS Value 

  ,SUBSTRING(Rest, CHARINDEX(@sep, Rest, 1) + 1, LEN(Rest)) AS Value 

  FROM cte 

  WHERE CHARINDEX(@sep, Rest, 1) <> 0) 

 SELECT Id, Value 

 FROM cte;  

Ergebnis:

 Id Value 

 ----------- ------ 

 1 Jan 

 2 Feb 

 3  Mar 

 4 Apr 

 5 May 

 6 Jun 

 7 Jul 

 8 Aug 

 9 Sep 

 10 Oct 

 11 Nov 

 12 Dec 

Zu den regelmäßigen und wichtigsten Wartungsarbeiten eines DBA (Database Administrator) gehört das Ausführen aller Datenbanken mittels DBCC CHECKDB(0);, um sie auf Konsistenz und Fehlerfreiheit zu überprüfen.

Das Problem ist nur, das je komplexer die Datenbank ist, so umfangreicher das Protokoll als Ergebnis ist. Diese Ergebnisliste durchzusehen ist aufwendig und es kommt leicht vor, dass man wichtige Meldungen übersieht. Da wäre es doch besser, wenn man das Protokoll auf bestimmte Ergebnisse hin selektieren kann.

Das kann man mit dem folgenden Script, es schreibt das ResultSet in eine temporäre Tabelle und selektiert daraus die wichtigsten Eckdaten und kumulierte Werte.

Das wäre in abgewandelter Form eine gute Basis für einen „benutzerdefinierten Report“ in SSMS; muss ich aber noch dran arbeiten.

Ebenso wäre interessant, eine History-Tabelle zu verwenden, um den Datenbank-Status zeitlich zu protokollieren; schaden kann es jedenfalls nicht.

 -- Für MS SQL Server 2005 

 -- CheckDB für die aktuelle Datenbank ausführen und 

 -- auswerten (Status-Übersicht und Details). 

 -- Die Mindest-Stati Werte können konfiguriert werden. 

 DECLARE @MinState int, @MinStatus int, @MinLevel int 

 SET @MinState = 1; 

 SET @MinStatus = 0 

 SET @MinLevel = 10; 

 -- Temporäre Tabelle für das CheckDB Ergebnis 

 CREATE TABLE #CheckDB 

  ([Error] int, [Level] int, [State] int,  

  [MessageText] varchar(7000), [RepairLevel] int, [Status] int, 

  [DbId] int, [ObjectID] int, [IndexId] int, [PartitionId] int, 

  [AllocUnitId] int, [File] int, [Page] int, [Slot] int,  

  [RefFile] int, [RefPage] int, [RefSlot] int, [Allocation] int); 

 -- CheckDB ausführen und Ergebnis in die TempTabelle 

 INSERT INTO #CheckDB 

  ([Error], [Level], [State], [MessageText], [RepairLevel],  

  [Status], [DbId], [ObjectID], [IndexId], [PartitionId], 

  [AllocUnitId], [File], [Page], [Slot], [RefFile],  

  [RefPage], [RefSlot], [Allocation]) 

 EXEC ('DBCC CHECKDB(0) WITH TABLERESULTS'); 

 -- Die schließende Meldung mit der Zusammenfassung 

 SELECT [MessageText] 

 FROM #CheckDB 

 WHERE [Error] = 8989; 

 -- Übersicht mit Anzahl je Stati 

 SELECT CDB.Error, CDB.Level, CDB.State, CDB.Status, 

  CDB.RepairLevel, COUNT(*) AS CountOccurence 

 FROM #CheckDB AS CDB 

 WHERE CDB.State >= @MinState 

  AND CDB.Status >= @MinStatus 

  AND CDB.Level >= @MinLevel 

 GROUP BY CDB.Error, CDB.Level, CDB.State, 

  CDB.Status, CDB.RepairLevel; 

 --Ergebnis-Details selektieren 

 SELECT OBJ.name AS ObjName, OBJ.type_desc AS ObjType, 

  IDX.Name AS IndexName, 

  ALU.type_desc AS AllocationType, 

  CDB.Error, CDB.Level, CDB.State, CDB.Status, 

  CDB.RepairLevel, MessageText 

 FROM #CheckDB AS CDB 

  LEFT JOIN sys.objects AS OBJ 

  ON CDB.ObjectId = OBJ.object_id 

  LEFT JOIN sys.indexes AS IDX 

  ON CDB.ObjectId = IDX.object_id 

  AND CDB.IndexID = IDX.index_id 

  LEFT JOIN sys.allocation_units AS ALU 

  ON CDB.AllocUnitId = ALU.allocation_unit_id 

 WHERE CDB.State >= @MinState 

  AND CDB.Status >= @MinStatus 

  AND CDB.Level >= @MinLevel 

 ORDER BY ObjType, ObjName, IndexName; 

 GO 

 -- Aufräumen 

 DROP TABLE #CheckDB; 

 GO 

Das bei DML Aktionen Sperren entstehen, weiß jeder. Wenn man sich in nicht das gerade einfache & trockene Thema einliest und seine Daten kennt, kann in etwa abschätzen, wann welche Sperren entstehen.

Im alten Aktivitätsmonitor oder mit der mittlerweile abgekündigten System Stored Procedure EXEC sp_lock kann man sich die Sperren anzeigen, allerdings ist die Auskunft nicht so informativ.

Über die DMV des Microsoft SQL Server 2005 kann man mehr Informationen ermitteln, wie z.B. auf welche Objekte Sperren liegen.

Interessant ist es dann auch mal mit den unterschiedlichen Isolation Levels oder Table-Hints zu experimentieren. Oder zu sehen, welche weitere Sperren durch ForeignKey Constraints oder History-Trigger entstehen. Zum Experimentieren startet man einfach eine explizite Transaktion, führt das DML Statement aus, wartet aber mit dem Commit. Dann kann man ich Ruhe die Sperren selektieren und committed erst dann. Das sollte aber tunlichst nur auf Test-Systemen machen, sonst gilt: Transaktionen so kurz wie nur irgend möglich.

Beispiel:

 USE [AdventureWorks2005] 

 GO 

 BEGIN TRANSACTION; 

 UPDATE Person.Person WITH (RowLock)  

 -- oder PagLock, TabLock, TabLockX 

 SET FirstName = RTRIM(FirstName) 

 WHERE BusinessEntityID = 1; 

 -- Mit dem Commit warten; 

 -- erst die Sperren selektieren 

 COMMIT TRANSACTION; 

Hier ein Beispiel für die Selektion der Sperren und deren Daten; wenn es interessiert, kann weitere Felder der Tabellen mit selektieren:

 -- Ermitteln aller Sperren einer Datenbank mit den 

 -- Daten der betroffenen Objekte für Sql Server 2005 

 SELECT DB.name AS DatabaseName 

  ,TL.request_owner_type AS RequestType 

  ,TL.request_status AS RequestStatus 

  ,TL.request_mode AS RequestMode  

  ,ES.session_id AS SPID 

  ,ES.login_name AS LoginName 

  ,ES.status as SessionStatus 

  ,TL.resource_type AS ResourceType 

  ,TL.resource_description AS ResourceDescription 

  ,COALESCE(SO.name, PO.name, DB.name) AS ObjectName 

  ,COALESCE(SO.type_desc, PO.type_desc, 'DATABASE') AS ObjectType 

  ,IX.name as IndexName 

  ,OWT.wait_type AS OsWaitType 

  ,OT.task_state AS OsTaskState 

  ,OT.pending_io_count AS OsTaskPendingIo 

 FROM sys.dm_tran_locks AS TL 

  INNER JOIN sys.databases AS DB 

  ON TL.resource_database_id = DB.database_id 

  INNER JOIN sys.dm_exec_sessions AS ES 

  ON TL.request_session_id = ES.session_id 

  LEFT JOIN sys.dm_os_waiting_tasks AS OWT 

   ON TL.lock_owner_address = OWT.resource_address 

  LEFT JOIN sys.dm_os_tasks AS OT 

  ON ES.session_id = OT.session_id 

  LEFT JOIN sys.objects AS SO 

  ON TL.resource_associated_entity_id = SO.object_id 

  AND TL.resource_type = 'OBJECT' 

  LEFT JOIN sys.partitions AS PT 

  ON TL.resource_associated_entity_id = PT.hobt_id 

  AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 

  LEFT JOIN sys.objects AS PO 

  ON PT.object_id = PO.object_id 

  LEFT JOIN sys.indexes AS IX 

  ON PT.index_id = IX.index_id 

  AND PT.object_id = IX.object_id 

 WHERE 1 = 1 

  -- Optimale Filter; einfach entkommentieren 

  --AND TL.resource_database_id = DB_ID() -- Nur aktuelle DB betrachten 

  --AND ES.status <> 'sleeping' -- Keine schlafende Prozesse 

  --AND SO.type_desc = 'USER_TABLE' -- Nur Sperren auf Tabelle 

  --AND SO.name = 'Persons' -- Nur zu einem Objekt 

  --AND TL.request_mode LIKE '%X'  -- Nur exklusive Locks 

 ORDER BY DB.name, ObjectName, ResourceType 

  ,IndexName, ES.session_id