SQL – Fragen und AntwortenErmitteln von Sperren, große Abfragen, E/A-Statistiken und mehr

Herausgegeben von Nancy Michell

Laden Sie den Code für diesen Artikel herunter: SQLQandA2007_08.exe (151KB)

F: Ich muss die Namen der Objekte in allen Datenbanken anzeigen, die mit Sperren versehen sind. Wie kann ich das erreichen?

A: In SQL Server™ 2000 können Sie die syslocks-Systemtabelle in Master abfragen oder sp_lock ausführen, um aktuelle Informationen zu Sperren abzurufen. Gehen Sie dabei folgendermaßen vor:

SELECT * FROM master..syslocks
EXEC sp_lock

Angenommen aber, dass Sie das eigentliche Objekt NAMES aus der ObjID aus dem OUTPUT des gespeicherten sp_lock-Verfahrens (oder aus der id-Spalte in master..syslocks) übersetzen wollen.

In SQL Server 2005 SP1 und früher können Sie mithilfe der OBJECT_NAME-Funktion nur einen Parameter übergeben: object_id. Um den Objektnamen richtig abzurufen, mussten Sie vor Ausführen von OBJECT_NAME die CURRENT-Datenbank bearbeiten. Dies erschwerte das Überwachen der aktuellen Sperren, da Sie benutzerdefinierten Code schreiben mussten, um zum Abrufen des richtigen OBJECT_NAME jede Datenbank zu durchlaufen.

USE DBNAME  
SELECT OBJECT_NAME(object_id)

SQL Server 2005 SP2 verbessert dies durch Hinzufügen eines zweiten Parameters: database_id. Dieser neue Parameter ermöglicht das Abfragen von Objektnamen unabhängig davon, mit welcher CURRENT-Datenbank Sie verbunden sind.

OBJECT_NAME ( object_id [, database_id ] )

Jetzt können Sie sys.dm_tran_locks abfragen und den Objektnamen für jede Datenbank folgendermaßen abrufen:

SELECT
DB_NAME(resource_database_id),
OBJECT_NAME(resource_associated_entity_id, resource_database_id)
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT'

Es ist jedoch zu beachten, dass dies nur in SQL Server 2005 SP2 oder höher funktioniert. Wenn Sie dies in einer früheren Version von SQL Server 2005 ausführen, wird folgender Fehler angezeigt:

Msg 174, Level 15, State 1, Line 1
The object_name function requires 1 argument(s).

In einem weiteren, in Abbildung 1 dargestellten Beispiel, werden sys.dm_exec_sessions verknüpft, um Informationen über die Dienstprozess-IDs (SPIDS) abzurufen, die an den Sperren beteiligt sind. Weitere Informationen finden Sie in der SQL Server-Onlinedokumentation unter technet.microsoft.com/library/ms130214(sql.90).aspx.

Figure 1 Ermitteln von Serverprozess-IDs

SELECT
DB_NAME(resource_database_id) as DBName,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) AS ObjectName,
request_mode,
request_type,
request_session_id,
es.host_name,
es.login_name,
es.login_time
FROM
sys.dm_tran_locks tl
INNER JOIN sys.dm_exec_sessions es
ON tl.request_session_id=es.session_id
WHERE resource_type='OBJECT'

F: Ich arbeite mit einer großen, dynamischen SQL-Abfrage, die bisweilen die Länge von NVARCHAR(max) zu überschreiten scheint. Gibt es eine einfache Lösung, die es mir ermöglicht, eine so große Zeichenfolge auszuführen?

A: Wenn die Länge von NVARCHAR(max) überschritten wird, liegt eine Abfrage von 2 GB Größe vor! Sie müssen wahrscheinlich alle Zeichenfolgen, die Sie verketten, auch in NVARCHAR(max) konvertieren. Eine einfachere Problemumgehung, die auch in Versionen von SQL Server vor SQL Server 2005 wirksam ist, besteht in der Verkettung einer Reihe von kleineren Zeichenfolgen. Hier ist ein Beispiel:

DECLARE @q1 NVARCHAR(4000), @q2 NVARCHAR(4000), @q3 NVARCHAR(4000)
SET @q1 = 'SELECT...'
SET @q2 = 'FROM...'
SET @q3 = 'WHERE...'
EXEC (@q1 + @q2 + @q3)

TIPP: Verwenden der OUTPUT-Klausel

Sie können jetzt von Ihnen durchgeführte Änderungen mithilfe von Datenbearbeitungssprachen-Anweisungen (Data Manipulation Language, DML) überprüfen, ohne Trigger zu verwenden. In SQL Server 2005 wurde eine OUTPUT-Klausel als Teil von DML-Anweisungen eingeführt, mit der während eines DML-Vorgangs durchgeführte Änderungen nachverfolgt werden können. Die OUTPUT-Klausel kann das Resultset in einer Tabelle oder Tabellenvariablen speichern.

Die Funktion ähnelt denen von Triggern bei INSERTED- und DELETED-Tabellen, die auf die während des DML-Vorgangs geänderten Zeilen zugriffen. Um zu sehen, wie dies funktioniert, wird die Adresse aus der Adresstabelle in die Umkehrung des ursprünglichen Werts geändert, wie es die nachstehende Abbildung zeigt. Sie können die in diesem Code dargestellte Logik auch verwenden, um an den Daten vorgenommene Änderungen nachzuverfolgen und in einer Tabelle zu speichern.

Die Ausgabe der Abfrage sieht folgendermaßen aus:

Original Value:'1234 One Microsoft Way, Redmond, Wa.' has been changed to: '.aW,dnomdeR 
,yaW tfosorciM enO 4321'

Ändern einer Adresse

--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))

--Insert data
Insert into Address Values (234,567,'1234 One Microsoft Way, Redmond, Wa.')


--Declare a table variable
Declare @Recordchanges table (change Varchar(255))

--Update the address
Update Supplier.Address
Set Address=reverse(address)

--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' 
into @RecordChanges

--Query the changes from table variable
Select * from @RecordChanges

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

F: Ich arbeite mit einer unternehmenswichtigen SQL Server 2005-Clusterinstallation auf Windows Server® 2003. Der Microsoft® Distributed Transaction Coordinator (MS DTC) wurde auf derselben Gruppe mit dem Quorum „geclustert“, verwendet aber eine dedizierte Datenträgerressource (MS DTC verwendet denselben Netzwerknamen und dieselbe IP-Adresse wie die Quorumclustergruppe). Ich möchte diese Konfiguration entsprechend der Best Practices von Microsoft ausrichten. Daher brauche ich Hilfestellung beim Verschieben von MS DTC in eine dedizierte Clustergruppe. Reicht es, die MS DTC-Dienste mithilfe des Clusterverwaltungsprogramms zu entfernen und sie in einer dedizierten Gruppe neu zu erstellen?

A: Da Sie bereits über eine dedizierte Datenträgerressource für MS DTC verfügen, müssten Sie in der Lage sein, die Ressource einfach zu entfernen und sie in einer neuen Gruppe neu zu erstellen. Sie müssen in der neuen Gruppe auch einen neuen Netzwerknamen und eine virtuelle IP-Adresse erstellen.

Alternativ könnten Sie den neuen Netzwerknamen und die IP-Adresse in der Clustergruppe erstellen und die Abhängigkeiten zu den neuen Ressourcen ändern. Sie können MS DTC dann in eine neue Gruppe ziehen – der dedizierte Datenträger und die neuen Ressourcen werden dabei ebenfalls verschoben.

F: Ich muss E/A-Statistiken der physischen Datenbankdateien in einer Datenbank anzeigen. Was sollte ich dazu verwenden?

A: Die Systemfunktion „fn_virtualfilestats“, die in SQL Server 2000 und SQL Server 2005 zur Verfügung steht, und die Systemfunktion „sys.dm_io_virtual_file_stats“ (nur in SQL Server 2005) erfüllen genau diese Aufgabe. Die Funktion gibt statistische Informationen zurück, die seit dem letzten Start der Instanz von SQL Server gesammelt wurden. Beispielergebnisse sind in Abbildung 2 dargestellt.

Figure 2 Anzeigen von E/A-Statistiken in einer Datenbank

Wenn Sie die E/A-Auswirkung auf die zugrunde liegenden Datendateien verstehen, können Sie das physische Verteilen von Dateien und Dateigruppen auf Datenvolumes, das Erkennen möglicher E/A-Engpässe, die Durchführung der Datenbankwartung auf Dateiebene und ähnliche Aufgaben besser planen. Diese Funktion ist besonders nützlich für das Untersuchen der E/A-Auswirkung für große Datenbanken, in denen möglicherweise mehrere Dateien und Dateigruppen vorhanden sind.

Die Abfrage zum Anzeigen der Datei-E/A-Informationen für SQL Server 2000 sieht folgendermaßen aus:

SELECT *
FROM ::fn_virtualfilestats(default,default)
GO

Zum Anzeigen einer bestimmten databaseID übergeben Sie die ID für die Datenbank wie folgt:

SELECT *
FROM ::fn_virtualfilestats(7,default)
GO

Dies ist der SQL Server 2005-Code, der Dateistatistiken für alle Datenbanken auf dem Server anzeigt:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
GO

Die folgende Abfrage gibt Dateistatistiken nur für die aktuelle Datenbank zurück:

SELECT *
FROM ::fn_virtualfilestats(NULL,NULL)
WHERE DBID=db_id()
GO

Für SQL Server 2005 gibt es auch eine neue Systemfunktion mit der Bezeichnung „sys.dm_io_virtual_file_stats“, die letztendlich die Legacyfunktion „fn_virtualfilestats“ ersetzen soll:

sys.dm_io_virtual_file_stats( 
{ database_id | NULL },
{ file_id | NULL }
)

So wird sie verwendet:

SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL)

Wenn Sie einen lesbareren Bericht generieren wollen, der die eigentlichen Datenbanknamen und Dateinamen aus der Ausgabe zeigt, können Sie den Code im begleitenden Download für SQL Server 2000 oder SQL Server 2005 verwenden. Der Code ist auf der TechNet Magazin-Website verfügbar.

F: Ich muss auf einfache Art und Weise feststellen, ob eine Löschtransaktion einen Trigger ausgelöst hat. Wie kann ich diese Informationen abrufen?

A: Beim Umgang mit Triggern, die Aktionen wie Löschen, Einfügen und Aktualisieren handhaben, sind eine Reihe verschiedener Verfahren gebräuchlich, um zu bestimmen, ob durch eine Löschtransaktion ein Trigger ausgelöst wurde. Eine populäre Methode besteht im Vergleich der Anzahl der eingefügten und gelöschten virtuellen Tabellen, um festzustellen, ob sie einander entsprechen. Es gibt jedoch eine einfachere Methode: Sie können stattdessen die Columns_Updated-Funktion verwenden.

Wenn ein Trigger nämlich durch eine Löschtransaktion ausgelöst wird, gibt Columns_Updated immer den varbinary-Wert 0x zurück. Durch die folgende Überprüfung wird nachgewiesen, ob der Trigger durch eine Löschtransaktion ausgelöst wurde:

IF Columns_Updated() = 0x

TIPP: Initialisieren von Daten- und Protokolldateien

Wussten Sie, dass Daten- und Protokolldateien initialisiert werden, um auf dem Datenträger vorhandene Daten zu überschreiben, die aus zuvor gelöschten Dateien stammen? Daten- und Protokolldateien werden zunächst initialisiert, indem die Dateien mit Nullen gefüllt werden, wenn eine Datenbank erstellt wird, Dateien, Protokolle oder Daten einer vorhandenen Datenbank hinzugefügt werden, eine vorhandene Datei (auch mithilfe automatischer Vergrößerungsvorgänge) vergrößert oder eine Datenbank oder Dateigruppe wiederhergestellt wird. Aufgrund der Dateiinitialisierung nehmen diese Vorgänge mehr Zeit in Anspruch. Wenn jedoch zum ersten Mal Daten in die Dateien geschrieben werden, muss das Betriebssystem die Dateien nicht mit Nullen füllen.

In SQL Server 2005 können Datendateien unverzüglich initialisiert werden. Diese Funktion ermöglicht die schnelle Ausführung der zuvor erwähnten Dateivorgänge. Durch die sofortige Dateiinitialisierung wird belegter Speicherplatz zurückgewonnen, ohne ihn mit Nullen zu füllen. Stattdessen wird Datenträgerinhalt überschrieben, wenn neue Daten in die Dateien geschrieben werden. Protokolldateien können nicht unmittelbar initialisiert werden. Die sofortige Dateiinitialisierung ist nur in Windows XP Professional und Windows Server 2003 oder späteren Versionen verfügbar.

Da der gelöschte Datenträgerinhalt nur überschrieben wird, wenn neue Daten in die Dateien geschrieben werden, könnte ein nicht autorisierter Prinzipal auf den gelöschten Inhalt zugreifen. Obwohl die Datenbankdatei an die Instanz von SQL Server angehängt ist, wird die Bedrohung der Offenlegung von Informationen durch die freigegebene Zugriffssteuerungsliste (Discretionary Access Control List, DACL) auf der Datei verringert. Diese DACL erlaubt den Dateizugriff nur durch das SQL Server-Dienstkonto und ermöglicht dies lediglich dem lokalen Administrator. Doch wenn die Datei getrennt ist, kann auf sie von einem Benutzer oder Dienst zugegriffen werden, der nicht über SE_MANAGE_VOLUME_NAME verfügt. Eine ähnliche Bedrohung ist bei der Sicherung der Datenbank vorhanden. Der gelöschte Inhalt kann für einen nicht autorisierten Benutzer oder Dienst zugänglich werden, wenn die Sicherungsdatei nicht mit einer entsprechenden DACL geschützt ist.

Wenn Sie Bedenken haben, dass gelöschte Inhalte potenziell offengelegt werden könnten, sollten Sie sicherstellen, dass alle getrennten Datendateien und Sicherungsdateien über einschränkende DACLs verfügen. Zudem sollten Sie die sofortige Dateiinitialisierung für die Instanz von SQL Server durch Widerrufen von SE_MANAGE_VOLUME_NAME im SQL Server-Dienstkonto deaktivieren.

Thanks to the following Microsoft IT pros for their technical expertise: Christian Bolton, Dan Carollo, Robert Davis, Jorge Guzman, Saleem Hakani, Ward Pond, Kalyan Yella, and Paolo Zavatarelli.

© 2008 Microsoft Corporation und CMP Media, LLC. Alle Rechte vorbehalten. Die nicht genehmigte teilweise oder vollständige Vervielfältigung ist nicht zulässig.