SQL Server: Minimize Blocking in SQL Server

Artikel
11/18/2015

SQL Server

Minimieren der Blockierung in SQL Server

Cherié Warren

Kurz zusammengefasst:

Ursachen für Sperrenausweitung
Vermeiden unnötiger Blockierung
Optimieren Ihrer Abfragen
Überwachen der Auswirkungen des Sperrens auf die Leistung

Sperren ist notwendig, um gleichzeitiges Lesen und Schreiben in einer Datenbank zu unterstützen, aber das Blockieren kann die Systemleistung beeinträchtigen, und zwar auf eine Weise, die manchmal nur schwer erkennbar ist. In diesem Artikel werde ich mich damit befassen, wie Sie Ihre SQL Server 2005- oder SQL Server 2008-Datenbank optimieren können,

um das Blockieren zu minimieren, und wie Sie das System überwachen können, damit Sie besser verstehen, welche Auswirkungen das Sperren auf die Leistung hat.

Sperrung und Ausweitung

SQL Server® wählt den am besten geeigneten Sperrungsgrad, wobei es berücksichtigt, wie viele Datensätze betroffen sind und welche gleichzeitige Aktivität auf dem System stattfindet. Standardmäßig wählt SQL Server den kleinsten Sperrungsgrad aus. Höhere Sperrungsgrade werden nur dann gewählt, wenn dies eine effizientere Verwendung des Systemspeichers ermöglicht. SQL Server weitet eine Sperre aus, wenn die Gesamtleistung des Systems davon profitiert. Wie Abbildung 1 veranschaulicht, finden Ausweitungen statt, wenn die Anzahl der Sperren bei einem bestimmten Scan die Zahl 5.000 überschreitet oder wenn der vom System für Sperren verwendete Speicher die verfügbare Kapazität übersteigt:

Abbildung 1 Ursachen für Sperrenausweitung

Abbildung 1** Ursachen für Sperrenausweitung **(Klicken Sie zum Vergrößern auf das Bild)

Wenn die Sperreneinstellung auf 0 gesetzt ist, werden vom Datenbankmodul 24 Prozent des Nicht-AWE-Speichers (Address Windowing Extensions) verwendet.
Wenn die Sperreneinstellung nicht auf 0 gesetzt ist, werden vom Datenbankmodul 40 Prozent des Nicht-AWE-Speichers verwendet.

Wenn eine Ausweitung erfolgt, wird immer zu einer Tabellensperre ausgeweitet.

Vermeiden unnötiger Blockierungen

Eine Blockierung kann bei jedem Sperrungsgrad stattfinden, aber das Blockierungsrisiko steigt, wenn Ausweitungen erfolgen. Sperrenausweitung kann ein Zeichen dafür sein, dass Ihre Anwendung auf ineffiziente Weise entworfen, programmiert oder konfiguriert wurde.

Um Blockierung zu vermeiden, ist es wichtig, die Grundlagen des Datenbankentwurfs zu beherzigen und beispielsweise ein normalisiertes Schema mit eingeschränkten Schlüsseln zu verwenden und Vorgänge mit großen Datenmengen auf Transaktionssystemen zu vermeiden. Wenn diese Prinzipien (z. B. die Trennung des Berichterstattungssystems vom Transaktionssystem oder die Verarbeitung von Datenfeeds außerhalb der Geschäftszeiten) nicht befolgt werden, ist es schwierig, das System zu optimieren.

Die Indizierung kann ein wichtiger Faktor dafür sein, wie viele Sperren benötigt werden, um auf die Daten zugreifen zu können. Ein Index kann die Anzahl der Datensätze verringern, auf die eine Abfrage zugreift, indem er die Anzahl der internen Suchvorgänge reduziert, die das Datenbankmodul durchführen muss. Wenn Sie beispielsweise in einer nicht indizierten Spalte einer Tabelle eine einzige Zeile auswählen, muss jede Zeile der Tabelle vorübergehend gesperrt werden, bis der gewünschte Datensatz identifiziert wurde. Wenn diese Spalte dagegen indiziert ist, wird nur eine einzige Sperre benötigt.

Sowohl SQL Server 2005 als auch SQL Server 2008 enthalten dynamische Verwaltungsansichten (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details), von denen Tabellen und Spalten angezeigt werden, die basierend auf gesammelten Nutzungsstatistiken von Indizes profitieren würden.

Fragmentierung kann bei Leistungsproblemen ebenfalls eine Rolle spielen, denn sie zwingt das Datenbankmodul unter Umständen, auf mehr Seiten zuzugreifen, als ohne Fragmentierung notwendig wäre. Zusätzlich können falsche statistische Werte den Abfrageoptimierer dazu verleiten, einen weniger effizienten Plan zu wählen.

Bedenken Sie, dass Indizes zwar den Datenzugriff beschleunigen, aber andererseits die Datenänderung verlangsamen können, weil nicht nur zugrunde liegende Daten geändert, sondern auch die Indizes aktualisiert werden müssen. Die dynamische Verwaltungsansicht „sys.dm_db_index_usage_stats“ hebt hervor, wie oft Indizes verwendet werden. Ein häufiges Beispiel für eine ineffiziente Indizierung tritt im Fall zusammengesetzter Indizes auf, bei denen sowohl isoliert als auch kombiniert dieselben Spalten indiziert werden. Da SQL Server auf Indizes von links nach rechts zugreift, wird der Index verwendet, solange die Spalten links außen hilfreich sind.

Eine Partitionierung von Tabellen kann sowohl das System optimieren, um das Blockierungsrisiko zu senken, als auch die Daten in separate physische Objekte unterteilen, die separat angefordert werden können. Während die Einrichtung von Zeilenpartitionen das naheliegendere Verfahren zur Trennung der Daten darstellt, sollte als weitere Möglichkeit eine horizontale Partitionierung der Daten in Betracht gezogen werden. Sie können sich u. U. bewusst für eine Denormalisierung entscheiden, indem Sie eine Tabelle in separate Tabellen mit derselben Anzahl von Zeilen und Schlüsseln, aber unterschiedlichen Spalten aufteilen, um die Wahrscheinlichkeit zu senken, dass separate Prozesse gleichzeitig exklusiv auf die Daten zuzugreifen versuchen.

Je vielfältiger die Möglichkeiten sind, mit denen eine Anwendung auf eine bestimmte Zeile von Daten zugreifen kann, und je mehr Spalten in dieser Zeile enthalten sein können, desto vorteilhafter kann ein mit Spaltenpartitionierung arbeitendes Verfahren sein. Anwendungswarteschlangen und Statustabellen können manchmal von diesem Verfahren profitieren. SQL Server 2008 bietet die zusätzliche Möglichkeit, Sperrenausweitungen für einzelne Partitionen (oder einzelne Tabellen, falls für die Tabelle keine Partitionen eingerichtet sind) zu deaktivieren.

Abfrageoptimierung

Die Abfrageoptimierung spielt bei der Verbesserung der Leistung eine wichtige Rolle. Hierbei können Sie nach den drei folgenden Verfahren vorgehen:

Verkürzen der Transaktion Eines der wichtigsten Verfahren zum Verringern von Blockierungen sowie zum Verbessern der Gesamtleistung besteht darin sicherzustellen, dass die Transaktionen möglichst klein sind. Jede Verarbeitung, die für die Integrität der Transaktion nicht entscheidend ist (z. B. die Suche nach verwandten Daten sowie das Indizieren und Bereinigen von Daten) sollte aus der Transaktion ausgelagert werden, um ihre Größe zu verringern.

SQL behandelt jede Anweisung als implizite Transaktion. Wenn sich die Anweisung auf eine große Zahl von Zeilen auswirkt, kann eine einzige Anweisung dennoch eine große Transaktion darstellen, insbesondere dann, wenn viele Spalten beteiligt sind oder wenn die beteiligten Spalten einen großen Datentyp enthalten. Eine einzige Anweisung kann auch Seitenaufteilungen verursachen, falls der Füllfaktor hoch ist oder eine UPDATE-Anweisung eine Spalte mit einem längeren Wert füllt, als der Spalte zugeordnet war. In solchen Fällen kann es nützlich sein, die Transaktion in Zeilengruppen aufzuteilen und diese einzeln zu verarbeiten, bis sie vollständig sind. Batchverarbeitung sollte nur in Betracht gezogen werden, wenn die einzelne Anweisung oder die Gruppen von Anweisungen in kleinere Stapel aufgeteilt werden können, die dennoch als vollständige Verarbeitungseinheiten betrachtet werden können, wenn sie gelingen oder fehlschlagen.

Sequenzieren der Transaktion Innerhalb der Transaktion lässt sich durch Sequenzierung die Wahrscheinlichkeit des Blockierens verringern. Dabei sind zwei Prinzipien zu beherzigen. Erstes Prinzip: Greifen Sie in Ihrem System im gesamten SQL-Code stets in der gleichen Reihenfolge auf Objekte zu. Ohne eine konsistente Reihenfolge können Deadlocks auftreten, falls zwei konkurrierende Prozesse in unterschiedlicher Reihenfolge auf Daten zugreifen und für einen dieser Prozesse ein Systemfehler ausgelöst wird. Zweites Prinzip: Setzen Sie Objekte, auf die häufig zugegriffen wird oder bei denen der Zugriff sehr aufwändig ist, an das Ende der Transaktion. SQL wartet mit dem Sperren der Objekte, bis sie in der Transaktion benötigt werden. Das Hinauszögern des Zugriffs auf diese „problematischen Objekte“ ermöglicht es, die Dauer der Sperrung dieser Objekte zu verkürzen.

Verwenden von Sperrhinweisen Sperrhinweise können für eine bestimmte Tabelle oder Ansicht auf Sitzungs- oder Anweisungsebene verwendet werden. Ein typisches Szenario für die Verwendung eines Hinweises auf Sitzungsebene ist beispielsweise eine Batchverarbeitung in einem Data Warehouse, bei der der Entwickler weiß, dass diese Verarbeitung der einzige Prozess ist, der zu einem bestimmten Zeitpunkt an diesem Satz von Daten ausgeführt wird. Wenn am Beginn der gespeicherten Prozedur ein Befehl wie z. B. „SET ISOLATION LEVEL READ UNCOMMITTED“ verwendet wird, reserviert SQL Server keine Lesesperren, wodurch der durch Sperren entstehende zusätzliche Aufwand insgesamt verringert und die Leistung gesteigert wird.

Ein typisches Szenario für die Verwendung von Hinweisen auf Anweisungsebene ist beispielsweise eine Situation, in der der Entwickler weiß, dass ein unsauberes Lesen auf sichere Weise stattfinden kann (z. B. beim Lesen einer einzigen Zeile in einer Tabelle, wobei feststeht, dass keine anderen gleichzeitigen Prozesse diese spezielle Zeile benötigen), oder in der alle anderen Versuche, die Leistung zu optimieren (z. B. Schemaentwurf, Indexentwurf und -wartung sowie Abfrageoptimierung), gescheitert sind und der Entwickler den Compiler zwingen will, eine bestimmte Art von Hinweis zu verwenden.

Zeilensperrhinweise sind u. U. dann sinnvoll, wenn eine Überwachung zeigt, dass Sperrungen eines höheren Sperrungsgrads stattgefunden haben, bei denen sich die Abfrage auf sehr wenige Datensätze auswirkt, denn dadurch lassen sich Blockierungen möglicherweise verringern. Tabellensperrhinweise sind evtl. dann sinnvoll, wenn eine Überwachung zeigt, dass Sperrungen eines kleineren Sperrungsgrads aufrechterhalten (und nicht ausgeweitet) werden, bei denen sich die Abfrage auf fast alle Datensätze der Tabelle auswirkt, denn dadurch lassen sich die zum Aufrechterhalten der Sperren benötigten Systemressourcen möglicherweise verringern. Beachten Sie, dass das Festlegen eines Sperrhinweises keine Gewähr dafür bietet, dass die Sperre nicht ausgeweitet wird, wenn die Anzahl der Sperren den Schwellenwert für den Systemspeicher erreicht. Das Festlegen eines Sperrhinweises verhindert jedoch alle sonstigen Ausweitungen.

Anpassen Ihrer Konfiguration

Wie Abbildung 2 veranschaulicht, müssen Sie beim Konfigurieren Ihres SQL Server-Systems eine Reihe von Faktoren berücksichtigen.

Abbildung 2 Ermittlung des für Sperren verwendbaren Speichers durch SQL Server

Abbildung 2** Ermittlung des für Sperren verwendbaren Speichers durch SQL Server **(Klicken Sie zum Vergrößern auf das Bild)

Speicher Da Sperren immer im Nicht-AWE-Speicher aufbewahrt werden, wird durch eine Erhöhung der Größe des Nicht-AWE-Speichers die Kapazität des Systems für das Aufrechterhalten von Sperren erhöht.

Wenn Sie versuchen, die Sperrkapazität zu erhöhen, sollte eine 64-Bit-Architektur Ihre erste Wahl sein, denn eine 32-Bit-Architektur ist auf 4 GB des Nicht-AWE-Speichers begrenzt, während es in einer 64-Bit-Architektur in dieser Hinsicht keinerlei Einschränkungen gibt.

Auf 32-Bit-Systemen können Sie ein zusätzliches Gigabyte an Speicher, das vom Betriebssystem genutzt wird, für SQL Server reservieren, indem Sie in der Datei „Boot.ini“ den Schalter „/3GB“ hinzufügen.

SQL Server-Konfigurationseinstellungen Über sp_configure können verschiedene Einstellungen festgelegt werden, die sich auf das Sperren auswirken. Mit der Sperreneinstellung wird konfiguriert, wie viele Sperren vom System aktiviert werden können, bevor ein Fehler ausgelöst wird. Standardmäßig ist diese Einstellung auf 0 gesetzt, was bedeutet, dass der Server die Sperrenreservierungen dynamisch an andere Prozesse anpasst, die Speicher benötigen. SQL reserviert zu Beginn 2.500 Sperren, und jede Sperre nimmt 96 Byte im Speicher in Anspruch. Ausgelagerter Speicher wird nicht verwendet.

Die Einstellungen für minimalen und maximalen Speicher reservieren die von SQL Server verwendete Speichermenge und konfigurieren dadurch den Server dafür, den Speicher statisch bereitzuhalten. Da die Sperrenausweitung mit dem verfügbaren Speicher zusammenhängt, kann das Reservieren der Speichermenge aus konkurrierenden Prozessen einen Einfluss darauf haben, ob Ausweitungen stattfinden.

Verbindungseinstellungen Standardmäßig werden Sperren, die blockieren, nicht aufgrund einer Zeitüberschreitung deaktiviert, aber Sie können die Einstellung „@@LOCK_TIMEOUT“ verwenden, die einen Fehler auslöst, wenn die für die Freigabe einer Sperre festgelegte Wartezeit überschritten wird.

Ablaufverfolgungsflags Auf Sperrenausweitungen beziehen sich insbesondere zwei Ablaufverfolgungsflags. Eines dieser Flags ist das Ablaufverfolgungsflag 1211, das Sperrenausweitungen deaktiviert. Wenn die Anzahl der verwendeten Sperren den verfügbaren Speicher überschreitet, wird ein Fehler ausgelöst. Das andere dieser Flags ist das Ablaufverfolgungsflag 1224, das Sperrenausweitungen für einzelne Anweisungen deaktiviert.

Überwachen Ihres Systems

Weiterführende Literatur

Die Auswirkungen, die das Sperren auf die Gesamtsystemleistung hat, können für Blockierungen und Sperren überwacht werden, indem in bestimmten Intervallen (z. B. stündlich) Statusdaten abgerufen und fortlaufend Informationen zu den aktivierten Sperren erfasst werden. Es müssen folgende Hauptinformationen ermittelt werden:

Betroffenes Objekt, Sperrungsgrad und Sperrentyp
Sperr- und Blockierdauer
Erteilter SQL-Befehl (Name der gespeicherten Prozedur, darin enthaltene SQL-Anweisung)
Informationen zur Blockierungskette (sofern relevant)
Nutzung der verfügbaren Sperrkapazität durch das System

Sie können ein ähnliches Skript wie in Abbildung 3 ausführen, um diese Informationen zu ermitteln und sie mit dem relevanten Zeitstempel in eine Tabelle zu schreiben. Um die Ressourcen-ID der blockierten Daten noch weiter aufzuschlüsseln, können Sie ein ähnliches Skript wie in Abbildung 4 ausführen.

Figure 4 Weitere Informationen zu blockierten Daten

DECLARE @SQL                           nvarchar(max)
      , @CallingResourceType           varchar(30)
      , @Objectname                    sysname
      , @DBName                        sysname
      , @resource_associated_entity_id int

-- TODO: Set the variables for the object you wish to look up

SET @SQL = N'
USE     ' + @DbName + N'
DECLARE @ObjectId int

SELECT  @ObjectId = CASE
                    WHEN @CallingResourceType = ''OBJECT''
                    THEN @resource_associated_entity_id
                    WHEN @CallingResourceType IN (''HOBT'', ''RID'', ''KEY'',''PAGE'')
                    THEN (SELECT  object_id
                          FROM    sys.partitions 
                          WHERE   hobt_id = @resource_associated_entity_id)
                    WHEN @CallingResourceType = ''ALLOCATION_UNIT''
                    THEN (SELECT  CASE
                                     WHEN type IN (1, 3)
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   hobt_id = allocation_unit_id)
                                     WHEN type = 2
                                     THEN (SELECT  object_id
                                           FROM    sys.partitions 
                                           WHERE   partition_id = allocation_unit_id)
                                     ELSE NULL
                                     END
                          FROM    sys.allocation_units 
                          WHERE   allocation_unit_id = @resource_associated_entity_id)
                    ELSE NULL
                    END

SELECT  @ObjectName = OBJECT_NAME(@ObjectId)'

EXEC    dbo.sp_executeSQL
        @SQL
      , N'@CallingResourceType varchar(30)
      , @resource_associated_entity_id int
      , @ObjectName sysname OUTPUT'
      , @resource_associated_entity_id = @resource_associated_entity_id
      , @CallingResourceType = @CallingResourceType
      , @ObjectName = @ObjectName OUTPUT

Figure 3 Erfassen von Sperrstatistiken

SELECT  er.wait_time                      AS WaitMSQty
      , er.session_id                     AS CallingSpId
      , LEFT(nt_user_name, 30)            AS CallingUserName
      , LEFT(ces.program_name, 40)        AS CallingProgramName
      , er.blocking_session_id            AS BlockingSpId
      , DB_NAME(er.database_id)           AS DbName
      , CAST(csql.text AS varchar(255))   AS CallingSQL
      , clck.CallingResourceId
      , clck.CallingResourceType
      , clck.CallingRequestMode
      , CAST(bsql.text AS varchar(255))   AS BlockingSQL
      , blck.BlockingResourceType
      , blck.BlockingRequestMode
FROM    master.sys.dm_exec_requests er WITH (NOLOCK)
        JOIN master.sys.dm_exec_sessions ces WITH (NOLOCK)
          ON er.session_id = ces.session_id
        CROSS APPLY fn_get_sql (er.sql_handle) csql
        JOIN (
-- Retrieve lock information for calling process, return only one record to 
-- report information at the session level
              SELECT  cl.request_session_id                 AS CallingSpId
                    , MIN(cl.resource_associated_entity_id) AS CallingResourceId
                    , MIN(LEFT(cl.resource_type, 30))       AS CallingResourceType
                    , MIN(LEFT(cl.request_mode, 30))        AS CallingRequestMode 
-- (i.e. schema, update, etc.)
              FROM    master.sys.dm_tran_locks cl WITH (nolock)
              WHERE   cl.request_status = 'WAIT' -- Status of the lock request = waiting
              GROUP BY cl.request_session_id
              ) AS clck
           ON er.session_id = clck.CallingSpid
         JOIN (
              -- Retrieve lock information for blocking process
              -- Only one record will be returned (one possibility, for instance, 
              -- is for multiple row locks to occur)
              SELECT  bl.request_session_id            AS BlockingSpId
                    , bl.resource_associated_entity_id AS BlockingResourceId
                    , MIN(LEFT(bl.resource_type, 30))  AS BlockingResourceType
                    , MIN(LEFT(bl.request_mode, 30))   AS BlockingRequestMode
              FROM    master.sys.dm_tran_locks bl WITH (nolock)
              GROUP BY bl.request_session_id
                    , bl.resource_associated_entity_id 
              ) AS blck
           ON er.blocking_session_id = blck.BlockingSpId
          AND clck.CallingResourceId = blck.BlockingResourceId
        JOIN master.sys.dm_exec_connections ber WITH (NOLOCK)
          ON er.blocking_session_id = ber.session_id
        CROSS APPLY fn_get_sql (ber.most_recent_sql_handle) bsql
WHERE   ces.is_user_process = 1
        AND er.wait_time > 0

Sie können Ihr System auch in Bezug auf Ausweitungen überwachen, und zwar über SQL Profiler (Lock:Escalation-Ereignis), über die dynamische Verwaltungsansicht „dm_db_index_operational_stats“ (index_lock_promotion_count) oder durch regelmäßiges Abrufen von Systemsperrungsinformationen. Durch eine Ausweitungsüberwachung lassen sich Informationen darüber gewinnen, ob die Verarbeitung eine Ausweitung erfordert. Ist dies nicht der Fall, können die relevanten gespeicherten Prozeduren eine Ursache von Leistungsproblemen aufzeigen. Tabellen mit großen Datenmengen oder einer hohen gleichzeitigen Nutzung sollten im Mittelpunkt der Auswertung stehen.

Nach dem Sammeln von Daten zu Sperren, Blockierungen und Ausweitungen können die Daten anschließend analysiert werden, um die kumulative Blockier- und Sperrzeit (Anzahl der Fälle multipliziert mit der Dauer der Fälle) pro Objekt zu bestimmen. Dies kann in der Regel einen iterativen Zyklus der Leistungsoptimierung in Gang setzen, in dem Änderungen implementiert, überwacht, analysiert und korrigiert werden. Manchmal ist lediglich eine einfache Änderung wie z. B. das Hinzufügen eines Indexes notwendig, um eine beträchtliche Leistungssteigerung zu erzielen und etwas daran zu ändern, in welchem Bereich des Systems der gravierendste Leistungsengpass entsteht.

Weitere Informationen zum Verringern von Blockierungen in SQL Server finden Sie in der Randleiste „Weiterführende Literatur“. Wenn in den Phasen der Entwicklung, der Programmierung und der Stabilisierung sorgfältig darauf geachtet wird, die Transaktionen klein zu halten, lassen sich viele Blockierungsprobleme minimieren. Die Wahl einer geeigneten Hardware kann ebenfalls die Wahrscheinlichkeit unerwünschter Ausweitungen beträchtlich senken. Durch eine unabhängige, kontinuierliche Auswertung von Blockierungen im System lassen sich Leistungsprobleme schnell an der Quelle ihres Entstehens erkennen.

Cherié Warren arbeitet als leitende Entwicklerin für Microsoft IT. Sie ist derzeit für eine der größten Transaktionsdatenbanken bei Microsoft verantwortlich. Cherié Warren berät auch oft zu Fehlerursachen und Leistungsproblemen im Zusammenhang mit der Blockierung. Sie ist seit 10 Jahren auf SQL Server-Datenbanken auf Unternehmensebene spezialisiert.

Zusätzliche Ressourcen