SQL – Fragen und AntwortenCPU-Engpässe, Wiederherstellen und Verschieben von Datenbanken und mehr

Herausgegeben von Nancy Michell

F: Unter SQL Server™ ist die CPU-Auslastung plötzlich extrem angestiegen, obwohl ich nichts geändert habe. Es wurden keine neuen Benutzer hinzugefügt, es wurde keine Hardware entfernt, und es wurden keine neuen Tabellen erstellt. Wodurch wird dieses Problem verursacht?

Tipp: Zugriff während der Indexerstellung

Manchmal müssen für große Tabellen Indexe erstellt werden, was unter Umständen sehr lange dauern kann. Gleichzeitig sollen die Daten jedoch während dieses Vorgangs verfügbar sein. Wie lassen sich beide Ziele erreichen?

Beim Erstellen, Löschen oder Neuerstellen eines gruppierten Index versieht SQL Server die Tabelle mit einer Schemaänderungssperre (Schema modification, SCH-M). Diese verhindert für die Dauer des Vorgangs sämtliche Benutzerzugriffe auf die zugrunde liegenden Daten. Das ist auch der Fall, wenn Sie einen gruppierten Index für eine Tabelle erstellen. Wenn Sie hingegen einen nicht gruppierten Index für eine Spalte erstellen, versieht SQL Server die Tabelle mit einer gemeinsamen Sperre (Shared, S). Datenaktualisierungen der zugrunde liegenden Tabelle werden dadurch zwar ebenfalls verhindert, aber SELECT-Anweisungen zum Lesen der Daten können ausgeführt werden.

Wenn während der Erstellung eines gruppierten Index Lesezugriff auf die Tabelle benötigt wird, können Sie einen Index für eine Tabelle erstellen und dies als Onlinevorgang durchführen. Hier ist der Befehl dazu:

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

Auch beim Verwenden von Onlineindexvorgängen für Tabellen versieht SQL Server die zugrunde liegende Tabelle bei einem gruppierten Index mit einer SCH-M-Sperre und bei einem nicht gruppierten Index mit einer S-Sperre. Jedoch nur für sehr kurze Zeit: während der Start- und Endphase des Indexvorgangs. Dadurch ermöglicht diese Option besseren Zugriff zum Abfragen und Aktualisieren der zugrunde liegenden Tabelle während der Indexerstellung. Zu beachten ist lediglich, dass diese Vorgängen zur Onlineindexerstellung nur in SQL Server 2005 Enterprise Edition verfügbar sind.

A: Ein plötzlich und unerwartet auftretender CPU-Engpass, der nicht durch Änderungen oder zusätzliche Serverlast verursacht wird, kann mehrere Gründe haben. Zu den häufigen Ursachen zählen ein schlechter Abfrageplan, eine ungünstige SQL Server-Konfiguration, ein fehlerhaftes Anwendungs- oder Datenbankdesign oder unzureichende Hardwareressourcen.

In einem solchen Fall muss zuerst festgestellt werden, ob Ihr Server CPU-gebunden ist. Wenn das der Fall ist, ermitteln Sie die Anweisungen, die in Ihrem lokalen SQL Server-System die CPU am stärksten beanspruchen. Ob der Server CPU-gebunden ist, können Sie im Systemmonitor über den Zähler PROZESSOR: PROZESSORZEIT (%) bestimmen. Wenn der Wert für die verwendete Zeit pro CPU größer oder gleich 75 Prozent ist, liegt ein CPU-Engpass vor.

Überprüfen Sie auch die Planer von SQL Server, indem Sie die dynamische Verwaltungsansicht (Dynamic Management View, DMV) SYS.DM_OS_SCHEDULERS des Systems abfragen und den Wert der ausführbaren Aufgaben überprüfen. Ein Wert ungleich null zeigt an, dass Aufgaben zur Ausführung auf ihre Zeitscheibe warten müssen. Hohe Werte bei diesem Zähler deuten ebenfalls auf einen CPU-Engpass hin.

Mithilfe der folgenden Abfrage können Sie alle Planer auflisten und die Anzahl ausführbarer Aufgaben bestimmen:

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

Verwenden Sie zum Abfragen der 50 CPU-intensivsten SQL-Anweisungen die Abfrage in Abbildung 1.

Figure 1 Die 50 größten CPU-Verbraucher

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

F: Ich musste eine SQL Server 2005-Datenbank von Band wiederherstellen. Nach der Wiederherstellung waren jedoch die meisten Zugriffsberechtigungen von Benutzern auf die Datenbank verloren. Können Sie mir erklären, was ich bei der Wiederherstellung falsch gemacht habe? Die Daten waren in Ordnung, aber die Berechtigungen waren ein einziges Durcheinander.

A: Höchstwahrscheinlich wurde die wiederhergestellte Masterdatenbank zu einem anderen Zeitpunkt gesichert, so dass die Anmeldedaten in der Benutzerdatenbank nicht mit der aktuellen Masterdatenbank übereinstimmen. Sie müssen daher eine Synchronisierung durchführen. In der Randleiste sind unter „Hilfe beim Verschieben einer Datenbank“ die hilfreichsten Ressourcen zum Lösen möglicher Probleme beim Wiederherstellen oder Verschieben einer Datenbank aufgeführt.

F: Ich habe eine Anwendung entwickelt, in der mithilfe von OpenXML von SQL Server 2005 SP1 XML-Daten durch eine gespeicherte Prozedur in relationale Tabellen verteilt werden. Solche XML-Dokumente sind höchstens 5 KB groß, durchschnittlich etwa 2,5 KB. Die gespeicherte Prozedur wird vielfach parallel aufgerufen (bis zu 50 Mal).

Nun treten ernsthafte Sperrkonflikte auf, und ich fürchte, sie könnten durch OpenXML verursacht werden. Was denken Sie?

A: Zwar lassen sich mit OpenXML Daten aus nur einem Thread schneller verteilen oder konvertieren als mit der nodes()-Methode, bei letzterer ist die Skalierung aber normalerweise besser, insbesondere bei Parallelausführung. Wenn Sie jedoch OpenXML verwenden, beachten Sie die folgenden Richtlinien, um die Gesamtleistung von OpenXML zu optimieren:

Statt OpenXML fünfmal für dasselbe Zeilenmuster aufzurufen (wie Sie es ja beschreiben), sollten Sie alle Daten mit demselben Zeilenmuster in eine temporäre Tabelle extrahieren. Diese temporäre Tabelle können Sie anschließend zum Auswählen verwenden. Versuchen Sie auch, den Speicher mit sp_xml_removedocument so früh wie möglich freizugeben. Außerdem sollten Sie die Verwendung von Platzhaltern wie z. B. * und //, soweit möglich, vermeiden. Eine explizite Pfadangabe verbessert die Abfrageleistung.

F: Die Ausführung von DBCC SHRINKFILE auf meinem Server ist wirklich langsam. Könnte ich die Leistung durch einen Multiprozessorcomputer steigern? Oder wie kann ich die Situation verbessern?

A: DBCC SHRINKFILE ist ein Singlethreadvorgang, d. h. mehrere CPUs bringen hier keinen Vorteil. Bei diesem Vorgang werden Seiten einzeln nacheinander vom Ende der Datei an den Dateianfang verschoben. Bei diesem Verkleinern, wie es auch oft genannt wird, wird keine Defragmentierung durchgeführt; in vielen Fällen wird hierdurch die logische Fragmentierung sogar verstärkt.

Eine Möglichkeit, die Leistung dieses Vorgangs zu verbessern, besteht im Verschieben von Seiten in gruppierten Indexen. Wenn Sie mit Heaps arbeiten und diese viele nicht gruppierte Indexe enthalten, ist die Geschwindigkeit im Vergleich zu gruppierten Indexen merklich langsamer.

Zu beachten ist auch, dass das Verschieben von Seiten für LOB-Daten (Large Object) langsam ist, weil Zeilendaten gelesen werden müssen, um den Stamm dieser Daten zu finden.

Wenn sich der Inhalt eines Index oder einer Tabelle größtenteils am Ende der Datei befindet, können Sie die Indexe neu erstellen, sodass sie an den Anfang der Datei verschoben werden. Für das Neuerstellen der Indexe können mehrere CPUs genutzt werden. Im bulk_logged-Modus kann zudem der Speicherplatzbedarf der Protokollierung verringert werden. Eine Verkleinerung läuft dann schneller ab.

Weitere grundlegende Informationen zu Verkleinerungsvorgängen finden Sie in den Einträgen unter blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx und blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx.

Hilfe beim Verschieben einer Datenbank

• Verschieben von Datenbanken zwischen Computern mit SQL Server
• SQL Server-Datenbanken mithilfe von Detach/Attach an einen neuen Speicherort verschieben
• INF: Verwenden der WITH MOVE-Option mit der RESTORE-Anweisung
• Lösen von Zugriffsproblemen, wenn eine Datenbank auf einen anderen SQL-Server verschoben wird
• Übertragen von Benutzernamen und Kennwörtern zwischen Instanzen von SQL-Server
• Beschreibung des Assistenten zum Kopieren von Datenbanken in SQL Server 2000 und Problembehandlung
• Benutzeranmeldungen und Berechtigungen für eine Datenbank können nach Wiederherstellung der Datenbank falsch sein
• Systemdatenbanksicherungen können nicht in einem anderen Build von SQL Server wiederhergestellt werden
• Abrufen einer bestimmten Tabelle oder von Zeilen aus Datenbanksicherungen oder Transaktionsprotokollsicherungen in SQL Server

Tipp: Ändern von Ports

Wie Sie wissen, hört SQL Server standardmäßig Port 1433 ab. Während benannte Instanzen von SQL Server so konfiguriert sind, dass sie dynamische Ports verwenden (d. h. beim Start einer SQL Server-Instanz kann jeder verfügbare Port ausgewählt werden), hört die SQL Server-Standardinstanz immer Port 1433 ab. Das bedeutet, wenn Ihr Server den Standardport verwendet und dieser nicht gut gesichert ist, besteht ein Sicherheitsrisiko. Um Angriffe zu erschweren, können Sie daher den Standardport ändern. So geht es.

Öffnen Sie SQL Server-Konfigurations-Manager, erweitern Sie „SQL Server 2005-Netzwerkkonfiguration“ und anschließend „Protokolle“. Doppelklicken Sie dann auf „TCP/IP“. Die Liste der TCP/IP-Eigenschaften und der zugehörigen Funktionen wird unten angezeigt. Nehmen Sie die entsprechenden Einstellungen vor.

Beachten Sie, dass das SQL Server-Datenbankmodul mehrere Ports an der gleichen IP-Adresse abhören kann. Geben Sie daher alle gewünschten Ports getrennt durch Kommata (z. B. 1433,1500,1501) an. Wenn Sie eine IP-Adresse so konfigurieren wollen, dass mehrere Ports abgehört werden, müssen Sie auf der Registerkarte „Protokolle“ auch den Parameter „Alle überwachen“ mit „Nein“ belegen.

Klicken Sie jetzt mit der rechten Maustaste auf die Adressen und dann auf „Eigenschaften“, um die zu konfigurierende IP-Adresse anzugeben. Wenn das Dialogfeld „Dynamische TCP-Ports“ die Ziffer „0“ enthält, das Datenbankmodul also dynamische Ports abhört, löschen Sie diesen Wert. Geben Sie im Bereich „IP-Eigenschaften“ im Feld „TCP-Port“ die Portnummer ein, welche diese IP-Adresse abhören soll, und klicken Sie dann auf „OK“. Klicken Sie im Konsolenfenster auf „SQL Server 2005-Dienste“, dann im Detailbereich mit der rechten Maustaste auf „SQL Server (<Instanzname>)“ und dann auf „Neu starten“, um SQL Server zu beenden und neu zu starten.

Nachdem Sie SQL Server so konfiguriert haben, dass nur bestimmte Ports abgehört werden, gibt es für Clients drei Möglichkeiten, eine Verbindung zum Port herzustellen. Erstens können Sie den SQL Server-Browserdienst auf dem Server ausführen, um eine Verbindung zur Datenbankmodulinstanz nach Namen herzustellen. Zweitens können Sie ein Alias auf dem Client erstellen und die Portnummer angeben, und drittens können Sie den Client so programmieren, dass zum Herstellen der Verbindung eine benutzerdefinierte Verbindungszeichenfolge verwendet wird.

Unser Dank für das Beantworten der Fragen in diesem Monat gilt folgenden Microsoft IT-Experten: Chad Boyd, Cindy Gross, John Hadden, Saleem Hakani, Stephen Jiang, Mahesh Nayak, Paul Randal und Wayne Yu.

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