Minimalizowanie blokowania w systemie SQL Server

Autor: Cherié Warren

Opublikowano: 22 września 2008

Zawartość strony

	Blokowanie i eskalacja
	Unikanie niepotrzebnego blokowania
	Optymalizacja kwerend
	Dostosowanie konfiguracji
	Obserwowanie systemu
	Dodatkowe informacje

Blokowanie jest niezbędne do wspierania współbieżnych operacji odczytu i zapisu w bazie danych, jednak może negatywnie wpływać na wydajność systemu, niekiedy w subtelny sposób. Niniejszy artykuł prezentuje, jak możemy optymalizować bazy danych SQL Server 2005 lub SQL Server 2008 w celu minimalizacji blokowania, a także w jaki sposób możemy monitorować system tak, abyśmy mogli lepiej zrozumieć wpływ blokowania na wydajność.

Blokowanie i eskalacja

SQL Server® wybiera najodpowiedniejszy poziom blokady w oparciu o to, ile rekordów jest zaangażowanych oraz jakie współbieżne aktywności istnieją w systemie. Domyślnie SQL Server wybiera blokadę o najniższym poziomie, decydując się na rozleglejsze blokady jedynie wtedy, gdy umożliwia to bardziej efektywne wykorzystanie pamięci systemowej. SQL Server dokonuje eskalacji blokady, jeżeli poprawia to ogólną wydajność systemu. Jak widać na Rysunku 1, eskalacje pojawią się, gdy liczba blokad w danej operacji skanowania przekroczy 5000 lub gdy pamięć wykorzystywana przez system na blokady przekroczy dostępny rozmiar:

  •  24 procent pamięci (nie licząc pamięci AWE) wykorzystywanej przez aparat bazy danych, jeśli ustawienie blokad wynosi 0

  •  40 procent pamięci (bez AWE) wykorzystywanej przez aparat bazy danych, jeśli ustawienie blokad nie wynosi 0

Rysunek 1: Sytuacje powodujące eskalację blokady.

Jeśli pojawi się eskalacja, jej wynikiem zawsze będzie blokada na poziomie tabeli.

 Do początku strony

Unikanie niepotrzebnego blokowania

Blokowanie może wystąpić na dowolnym poziomie szczegółowości blokady, ale gdy pojawia się eskalacja, zasięg blokady zostaje zwiększony. Eskalacja blokady może stanowić sygnał, iż aplikacja została zaprojektowana, zaimplementowana lub skonfigurowana w sposób nieefektywny.

Ważnym czynnikiem, który pomaga w minimalizacji wzajemnego blokowania, jest przestrzeganie podstawowych zasad projektowania baz danych, takich jak zastosowanie znormalizowanego schematu z kluczami zawierającymi niewielką liczbę kolumn oraz unikanie masowych operacji przetwarzania danych w systemach transakcyjnych. Gdy zasady (takie jak np. oddzielenie systemu raportującego od systemu transakcyjnego lub przetwarzanie danych poza godzinami szczytu) nie są respektowane, zoptymalizowanie systemu nastręcza niemałych trudności.

Indeksowanie może stanowić kluczowy czynnik decydujący o tym, ile blokad jest koniecznych do uzyskania dostępu do danych. Indeks pozwala ograniczyć liczbę rekordów wykorzystywanych przez kwerendę, ponieważ redukuje liczbę wewnętrznych operacji wyszukiwania, które muszą zostać przeprowadzone przez aparat bazy danych. Na przykład, gdy wybieramy pojedynczy wiersz z tabeli z wykorzystaniem nieindeksowanej kolumny, konieczne jest tymczasowe zablokowanie każdego wiersza w tabeli, dopóki pożądany rekord nie zostanie zidentyfikowany. Natomiast gdyby kolumna była zaindeksowana, wystarczyłaby tylko jedna blokada.

SQL Server 2005 oraz SQL Server 2008 zawierają dynamiczne widoki zarządcze (sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details), które bazując na zebranych statystykach użycia, prezentują tabele oraz kolumny, których zaindeksowanie może przynieść korzyści.

Fragmentacja może również wpływać na wydajność, ponieważ aparat bazy danych może potrzebować dostępu do większej liczby stron niż to konieczne. Co więcej nieprawidłowe statystyki mogą prowadzić do tego, że optymalizator kwerend wybierze mniej efektywny plan.

Należy pamiętać, że choć indeksy przyspieszają dostęp do danych, mogą opóźniać operacje modyfikacji danych, ponieważ powodują, iż konieczna jest nie tylko zmiana samych danych, ale i aktualizacja indeksów. Dynamiczny widok zarządczy sys.dm_db_index_usage_stats ilustruje, jak często wykorzystywane są indeksy. Typowym przykładem nieefektywnego zastosowania indeksów złożonych jest sytuacja, gdy ta sama kolumna zostaje zaindeksowana osobno, jak i w połączeniu z innymi. Ponieważ SQL Server uzyskuje dostęp do indeksów od lewej do prawej, indeks będzie wykorzystywany, dopóki pomocne są kolumny położone najbardziej na lewo.

Partycjonowanie tabel pozwala zarówno zoptymalizować system (zmniejszając ryzyko wystąpienia blokad), jak i podzielić dane na osobne fizyczne obiekty tak, aby rywalizacja o każdy z nich przebiegała niezależnie. Choć partycjonowanie z podziałem na grupy wierszy stanowi bardziej oczywisty sposób rozdzielania danych, pionowe partycjonowanie danych stanowi inną opcję wartą rozważenia. Można celowo zdecydować się na rezygnację z normalizacji, dzieląc tabelę na osobne tabele zawierające tę samą liczbę wierszy i kluczy, ale inne kolumny – w celu obniżenia prawdopodobieństwa, że różne procesy będą potrzebowały wyłącznego dostępu do danych w tym samym czasie.

Atrakcyjność mechanizmu partycjonowania według kolumn jest tym większa, im bardziej zróżnicowane są sposoby uzyskiwania przez aplikację dostępu do określonego wiersza danych oraz im więcej kolumn może należeć do tego wiersza. Podejście to może czasem przynieść korzyści w zakresie kolejkowania aplikacji i tablic stanów. SQL Server 2008 dodaje możliwość wyłączenia eskalacji blokad dla poszczególnych partycji (lub poszczególnych tabel, jeśli nie posiadają one partycji).

 Do początku strony

Optymalizacja kwerend

Optymalizacja kwerend odgrywa ważną rolę w podnoszeniu wydajności. Oto trzy podejścia, które można zastosować:

Skrócenie transakcji Jedna z najważniejszych metod redukcji blokowania oraz podnoszenia ogólnej wydajności polega na utrzymywaniu możliwie jak najmniejszego rozmiaru transakcji. Wszelkie przetwarzanie, które nie jest kluczowe dla integralności transakcji (takie jak wyszukiwanie powiązanych danych, indeksowanie i porządkowanie danych) powinno być wyprowadzone na zewnątrz transakcji w celu zredukowania jej rozmiaru.

SQL traktuje każdą instrukcję jak niejawną transakcję. Jeśli pojedyncza instrukcja wpływa na wiele wierszy, nadal może stanowić dużą transakcję, w szczególności gdy obejmuje wiele kolumn lub gdy kolumny zawierają duży typ danych. Pojedyncza instrukcja może również powodować podziały stron, jeśli współczynnik wypełnienia jest wysoki lub gdy instrukcja UPDATE wypełnia kolumnę wartością szerszą niż alokowana. W takich sytuacjach pomocne może okazać się rozdzielenie transakcji na grupy wierszy i przetworzenie ich pojedynczo. Podział powinien być brany pod uwagę tylko wtedy, gdy poszczególne instrukcje lub grupy instrukcji mogą zostać rozdzielone na mniejsze serie, które stanowić będą kompletne jednostki pracy - niezależnie od tego, czy zakończą się sukcesem czy porażką.

Kolejność transakcji W ramach transakcji świadome określanie kolejności instrukcji może zmniejszać prawdopodobieństwo blokowania. Należy mieć na uwadze dwie podstawowe zasady. Po pierwsze, należy uzyskiwać dostęp do obiektów w tym samym porządku w całym kodzie SQL w systemie. Brak spójnego porządku może powodować powstawanie zakleszczeń, gdy dwa rywalizujące procesy próbują uzyskać dostęp do danych w różnej kolejności, powodując błąd systemowy jednego z tych procesów. Po drugie, obiekty o częstym lub kosztownym dostępie należy umieszczać na końcu transakcji. SQL czeka z zablokowaniem obiektów do momentu, aż będą one potrzebne w transakcji. Opóźnienie dostępu do "potencjalnych punktów zapalnych" powoduje, że obiekty te są blokowane przez krótszy okres czasu.

Użycie wskazówek blokowania Wskazówki blokowania mogą zostać stosowane na poziomie sesji lub na poziomie instrukcji dla określonej tabeli lub widoku. Typowy scenariusz wykorzystania wskazówki na poziomie sesji to np. przetwarzanie wsadowe w magazynie danych, gdy programista wie, że proces będzie jako jedyny działał w określonym czasie na danym zestawie danych. Gdy na początku procedury składowanej zastosowane zostanie polecenie takie jak SET ISOLATION LEVEL READ UNCOMMITTED, SQL Server nie stosuje żadnych blokad odczytu, redukując tym samym ogólne obciążenie procesami blokowania i zwiększając wydajność.

Typowy scenariusz zastosowania wskazówek na poziomie instrukcji stanowi sytuacja, gdy programista dopuszcza wystąpienie brudnego odczytu (na przykład w przypadku odczytu pojedynczego wiersza z tabeli, gdy inne równoległe procesy nie będą potrzebowały tego samego wiersza) lub gdy zawiodły wszystkie inne próby poprawienia wydajności (dostosowanie projektu schematu, projektu i konserwacji indeksów oraz kwerend), a programista chce zmusić kompilator do wykorzystania określonego typu wskazówki.

Zastosowanie wskazówek blokowania wierszy może mieć sens, gdy monitoring wykazuje pojawienie się blokad o szerszym zakresie, mimo iż kwerenda objęła niewielką liczbę rekordów. To podejście pozwala zredukować wzajemne blokowanie. Natomiast zastosowanie wskazówek blokowania tabel może być uzasadnione, gdy monitoring wykazuje utrzymywanie bardziej szczegółowych (nieeskalowanych) blokad, podczas gdy kwerenda objęła prawie wszystkie rekordy w tabeli. To podejście pozwala zredukować ilość zasobów systemowych potrzebnych do utrzymywania blokad. Warto mieć świadomość, że określenie wskazówki blokowania nie gwarantuje, iż blokada nie zostanie eskalowana, gdy liczba blokad osiągnie określony próg dla pamięci systemowej. Jednak ich zastosowanie pozwala zapobiec wszystkim innym eskalacjom.

 Do początku strony

Dostosowanie konfiguracji

Jak widać na Rysunku 2, konfigurując system SQL Server, należy wziąć pod uwagę wiele czynników.

Rysunek 2: W jaki sposób SQL Server określa ilość pamięci, która może być wykorzystywana do blokowania.

Pamięć Blokady są zawsze przechowywane poza pamięcią AWE, a zatem jedynie zwiększenie rozmiaru tej pamięci (z wyłączeniem pamięci AWE) spowoduje zwiększenie możliwości systemu w zakresie utrzymywania blokad.

Architektura 64-bitowa powinna stanowić nasz pierwszy wybór, gdy usiłujemy zwiększyć możliwość blokowania, ponieważ architektura 32-bitowa posiada ograniczenie 4GB pamięci (nie wliczając AWE), podczas gdy 64-bitowa nie posiada tego ograniczenia.

W systemach 32-bitowych możemy pozyskać dodatkowy gigabajt pamięci z systemu operacyjnego dla SQL Server, dodając przełącznik /3GB do pliku Boot.ini.

Ustawienia konfiguracyjne SQL Server Przy pomocy procedury sp_configure można dostosowywać różne ustawienia, które wpływają na blokowanie. Ustawienie blokowania konfiguruje, jak wiele blokad może być utrzymywanych przez system przed zgłoszeniem błędu. Domyślnie, ustawienie ma wartość 0, co oznacza, że serwer dynamicznie dostosuje utrzymywane blokady do innych procesów rywalizujących o pamięć. SQL wstępnie utrzymywać będzie 2500 blokad, przy czym każda blokada zużywa 96 bajtów pamięci. Pamięć stronicowana nie jest wykorzystywana.

Ustawienia min oraz max pamięci rezerwują ilość pamięci wykorzystywaną przez SQL Server, tym samym konfigurując serwer tak, aby statycznie utrzymywał on pamięć. Ponieważ eskalacja blokad jest związana z dostępną pamięcią, ochrona pewnej ilości pamięci przed równoległymi procesami może zadecydować o tym, czy eskalacja się pojawi czy też nie.

Ustawienia połączeń Domyślnie przetrzymujące zasoby blokady nie wygasają, ale można użyć ustawienia @@LOCK_TIMEOUT, które powoduje wystąpienie błędu, jeśli przekroczony zostanie określony czas oczekiwania na zwolnienie blokady.

Flagi śledzenia Dwie flagi śledzenia dotyczą w szczególności eskalacji blokad. Jedną z nich jest flaga śledzenia 1211, która wyłącza eskalację blokad. Jeśli liczba wykorzystywanych blokad przekroczy dostępną pamięć, zgłaszany jest błąd. Druga flaga 1224 wyłącza eskalację blokad dla poszczególnych instrukcji.

 Do początku strony

Obserwowanie systemu

Wpływ blokowania na ogólną wydajność systemu można monitorować dla blokad i wzajemnego blokowania, pobierając dane stanu z określoną częstotliwością (na przykład co godzinę) oraz przechwytując uruchomione statystyki dla utrzymywanych blokad. Kluczowe informacje do przechwycenia to:

  •  Obiekt objęty blokadą, poziom i typ blokady

  •  Czas utrzymywania blokady i oczekiwania na zablokowane zasoby

  •  Wywołane polecenie SQL (nazwa procedury składowanej, wewnętrzna instrukcja SQL)

  •  Informacja na temat łańcucha oczekiwania na zablokowane zasoby, jeśli takowy wystąpił

  •  W jaki sposób system wykorzystuje dostępne zasoby blokowania

Można uruchomić skrypt, podobny do zaprezentowanego na Rysunku 3, aby przechwycić te informacje i zapisać je w tabeli z odpowiednią sygnaturą czasową. A w celu dodatkowego podziału ResourceId dla blokowanych danych można uruchomić skrypt, podobny do zaprezentowanego na Rysunku 4.

Rysunek 3: Przechwytywanie informacji o blokowaniu

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 (

-- Pobieranie informacji o blokadach dla procesu wywołującego, zwrócony zostanie tylko jeden rekord, 

-- który raportuje informacje na poziomie sesji

              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 

-- (tzn. schema, update, itd.)

              FROM    master.sys.dm_tran_locks cl WITH (nolock)

              WHERE   cl.request_status = 'WAIT' –- Stan żądania blokady = oczekiwanie

              GROUP BY cl.request_session_id

              ) AS clck

           ON er.session_id = clck.CallingSpid

         JOIN (

              -- Pobieranie informacji o blokadach dla procesu blokującego

              -- Zwrócony zostanie tylko jeden rekord (na przykład jedną z możliwości jest wystąpienie blokad wielu wierszy)

              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

Rysunek 4: Pozyskiwanie informacji o blokowanych danych

DECLARE @SQL                           nvarchar(max)

      , @CallingResourceType           varchar(30)

      , @Objectname                    sysname

      , @DBName                        sysname

      , @resource_associated_entity_id int



-- TODO: Ustawienie zmiennych dla obiektu, który chcemy poddać analizie



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

Można również monitorować system pod kątem eskalacji za pomocą narzędzia SQL Profiler (zdarzenie Lock:Escalation), dynamicznego widoku zarządczego dm_db_index_operational_stats (index_lock_promotion_count) lub regularnie pobierając informacje o blokowaniu w systemie. Monitorowanie eskalacji powinno dostarczyć cennej wiedzy o tym, czy eskalacja jest uzasadniona przez przetwarzanie. W przeciwnym wypadku odpowiednie procedury składowane mogą wskazać główną przyczynę problemów z wydajnością. Tabele z dużą ilością danych lub wysokim równoległym użyciem powinny stanowić centrum uwagi podczas ewaluacji.

Po zebraniu danych o blokowaniu, oczekiwaniu na zablokowane zasoby i eskalacjach można następnie przeanalizować te dane w celu określenia łącznego czasu utrzymywania blokad i oczekiwania na zasoby (liczba wystąpień pomnożona przez czas trwania) per obiekt. Zazwyczaj może to być wstępem do wykonania iteracyjnego cyklu dostosowań wydajności, w którym zmiany są wdrażane, monitorowane, analizowane i udoskonalane. Czasem wystarczy prosta modyfikacja, taka jak dodanie indeksu, aby znacznie poprawić wydajność i zmienić obszar systemu, który stanowił najbardziej odczuwalne wąskie gardło wydajności.

Więcej informacji na temat redukowania blokowania w SQL Server znaleźć można w sekcji zatytułowanej "Dodatkowe informacje". Przywiązywanie wagi do utrzymywania małego rozmiaru transakcji w fazie projektowania, kodowania oraz stabilizacji pozwala zminimalizować wiele problemów związanych z wzajemnym blokowaniem. Odpowiedni sprzęt pozwala znacznie zredukować prawdopodobieństwo niechcianych rozszerzeń. Niezależnie od tego, stała ewaluacja blokowania w systemie pozwala szybko zidentyfikować problemy z wydajnością u samego źródła.

 Do początku strony

Dodatkowe informacje

  •  Inside Microsoft SQL Server 2005: The Storage Engine autorstwa Kalen Delaney

  •  How to Monitor Blocking in SQL Server 2005 and in SQL Server 2000

  •  SQL Server 2005 Performance Statistics Script

  •  How to Resolve Blocking Problems that Are Caused by Lock Escalation in SQL Server

  •  Lock Escalation (Database Engine)

O autorze

Cherié Warren pełni funkcję Senior Development Lead w Microsoft IT. Obecnie jest odpowiedzialna za jedną z największych transakcyjnych baz danych w firmie Microsoft. Cherié często udziela konsultacji w kwestii głównych źródeł problemów wydajności związanych z blokowaniem. Od dziesięciu lat specjalizuje się w korporacyjnych bazach danych SQL Server.

Do początku strony