27 września 2011

Blokowanie w SQL Server dla początkujących

Jak ujarzmić MS SQL Server?

Silnik baz danych MS SQL Server to takie niewdzięczne stworzenie w świecie .NET Framework. Relacyjne bazy danych są często w pogardzie u części programistów,  postrzegane jako zło konieczne. Niektórzy tworzą NoSQL movement,  inni proponują, żeby przenieść bazę danych do pamięci operacyjnej lub chociaż do chumry, usunąć relacje i wcisnąć ten cały data management w obiekty. Oczywiście, na dzisiaj (rok 2011, miesiąc Wrzesień) wielu nieszczęśliwych z tego powodu developerów musi zmagać się z SQL Server'em. Właśnie o pewnym aspekcie tych wysiłków chciałem pokrótce napisać.
Wyróżniłbym kilka klas problemów z którymi zmagają się na codzień programiści baz danych

  • Indeksowanie tabel i widoków
  • Blokowanie
  • Analiza dużej ilości zapytań (tzw. trace)
  • Porównywanie danych
  • Analiza wydajności zapytań
  • Śledzenie i rejestrowanie zapytań generowanych przez aplikację

Zapewne można wyróżnić jeszcze kilka kategorii, aczkolwiek proszę o wyrozumiałość, gdyż nic więcej nie przyszło mi do głowy.  W tym poście skupię się na zagadnieniach związanych z blokowaniem zapytań i narzędziach które mogą pomóc w rozwiązowaniu problemów spowodowanych przez blokady.



Blokowanie na ekranie


Blokowanie zapytań jest zjawiskiem częstym na platformie SQL Server, wynika ona z architektury tego silnika bazodanowego i domyślnego ustawienia poziomu izolacji tranzakcji na READ COMMITED. Praktycznie każde zapytanie generuje blokady (różnego rodzaju), zatem rozumienie mechanizmu blokad w SQL Serverze jest dosyć istotne. Istnieje kilka prostych procedur składowanych, które mogą nam dostarczyć potrzebnych informacji na temat blokowanych procesów, zapytań.


LockSampleDb


Zacznę od utworzenia bazy danych LockSampleDb, która posłuży nam za poligon eksperymentalny do monitorowania blokad.

CREATE DATABASE LockSampleDb
GO
Use LockSampleDb
GO
CREATE TABLE dbo.LockSample
(
 Id Int,
 Name Varchar(20)
)
GO
  --Works only in SQL Server 2008 and higher
 DECLARE @i Int = 1
 WHILE @i < 1000
 BEGIN
 INSERT INTO LockSample(Id,Name)
    VALUES (@i, 'Row '+CAST(@i AS VARCHAR)) 
    SET @i += 1
 END
GO 

Otwórz SQL Server Management Studio i uruchom poniższe zapytanie w jednym oknie.

--Query Window 1
Use LockSampleDb
GO
BEGIN TRAN
 
 UPDATE LockSample
 SET Name = 'Row Updated'
 WHERE Id = 40
 --ROLLBACK TRAN




A w drugi uruchom poniższe zapytanie.


Use LockSampleDb
GO
--Query Window 2 
SELECT * FROM  LockSample WHERE Id = 40




SELECT powinien ładnie zawisnąć, ponieważ próbuje odczytać wiersz, który jest już aktualizowany przez pierwsze zapytanie (tranzakcja jest otwarta, ponieważ nie została uruchomiona instrukcja COMMIT TRAN lub ROLLBACK TRAN).W tym momencie możemy zadziałać na kilka sposobów (SPID  procesu zablokowanego to 82, blokującego SPIP to 75, SPID - Server Process Identifier).

Activity monitor dla leniwych


Najprostszym narzędziem do analizy zablokowanych zapytań jest   SQL Server Management Studio Activity Monitor (aka Activity Monitor).  Wyświetli on wszystkie procesy SQL Server'a (nie mylić z sqlservr.exe) i w kolumnie Blocked By znajdziemy winowajcę (w SQL Server 2008 klikamy prawym na instancję SQL Servera i wybieramy z menu Activity Monitor).



Kolumna 'Blocked By' wyświetliła SPID  procesu blokującego ( w naszym przypadku 75).  Wyświetlając Details dla procesu SPID=75 możemy zobaczyć ostatnie zapytanie wykonane na tym procesie, które może powodować blokadę. Podkreślam, może powodować, jest to po prostu ostatnie zapytanie wykonane dla tego procesu.




sp_who


sp_who jest procedurą systemową, jest ona częścią silnika bazodanowego. Po wywołaniu prezentuje ona listę "procesów" SQL Servera z informacjami o użytkowniku w kontekście którego uruchamiane są polecenia. Dla nas istotną informacją jest kolumn SPID (identyfikator procesu)oraz blk (numer SPID procesu który blokuje dany proces). Na poniższym obrazku widać, że proces SPID=82 jest blokowany przez proces SPID=75. Czasami może być tak, że wiele procesów będzie blokowanych, a my szukamy tylko jednego. Z poziomu aplikacji trudno nam będzie ustalić jaki jest SPID danego połączenia, ale z pomocą może przyjść nieudokumentowana procedura  sp_who2 która wyświetla dodatkowe informacje o procesie (np. ProgramName).


sp_blocker_pss08 czyli kombajn


sp_blocker_pss08 to procedura dostarczona przez Microsoft do rozwiązaywania problemów z blokowanie. Rezultatem uruchomienia tej procedury są informacje o blokadach, rodzajach blokad, procesach na poziomie instancji SQL Server'a. 

sp_blocker_pss08 ma trzy rodzaje skryptów tworzących procedurę, w zależności od wersji SQL Servera, więc nie bądź zaskoczony błędami przy uruchomieniu Microsoft'owych skryptów.

Oczywiście, zawsze można skorzystać po prostu z starej, dobrej sp_lock, niestety jest ona oznaczona jako deprecated w BOL (Books Online).

sp_lock (deprecated)


W dalszym poszukiwaniu winowajcy, możemy skorzystać z  procedury systemowej sp_lock. Umożliwia ona wylistowanie blokad dla danego procesu lub dla wszystkich procesów SQL Server na instancji. Ponieważ wiemy, że proces SPID=75 blokuje zapytanie SELECT. Możemy pokusić się o wywołanie sp_lock w poniższy sposób.

EXEC sp_lock 75


Widać, że na proces 75 założył Exclusive Lock na wierszu. Rozszyfrowując ObjId używając funkcji Object_Name docieramy do tabelki  LockSample.

SELECT OBJECT_NAME(2105058535)


Można pójśc o krok dalej i znaleźć wiersz, na którym "zawisł" SELECT. Wystarczy użyć nieudokumentowanego polecenia DBCC PAGE. Odczytujemy file_id=1, page_id=78 i row_id=39 z columny Resource.

DBCC TRACEON(3604) 
DBCC PAGE (LockSampleDB, 1, 78,3)

I otrzymamy dump całej strony bazy danych na której  znajduje się nasz problematyczny wiersz i lokalizujemy dane w tym wierszu.

Pomocne mogą okazać się Dynamic Management Views (DMV's, np. sys.dm_tran_locks.

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = 75

Zaprezentowałem kilka sposobów lokalizowania problemów z blokowaniem zapytań. Zachęcam do eksperymentów z blokowaniem, polecam laboratoria PSS Service Center Labs - 2005 dostępne na stronie Microsoft.

Hope this helps.

1 komentarz:

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.