Efektywność procedur

Efektywność procedur to wskaźnik oceniający wydajność wykonania procedur składowanych w bazie danych.

Co oznacza efektywność procedur składowanych

Efektywność procedur składowanych opisuje, jak sprawnie baza danych wykonuje zapisany w niej zestaw operacji. Pod uwagę bierze się przede wszystkim czas realizacji, liczbę odczytanych i zmodyfikowanych rekordów, wykorzystanie procesora, pamięci operacyjnej oraz operacji wejścia i wyjścia. Sama poprawność wyniku nie świadczy jeszcze o dobrej wydajności. Procedura może zwracać prawidłowe dane, ale jednocześnie wykonywać pełne skanowanie dużej tabeli, wielokrotnie uruchamiać ten sam podział logiczny albo długo czekać na blokady.

Ocena efektywności wymaga odniesienia wyniku do konkretnego obciążenia. Inaczej ocenia się procedurę wywoływaną sporadycznie przez jednego użytkownika, a inaczej procedurę uruchamianą równocześnie przez setki połączeń. Istotne są także rozmiar tabel, liczba indeksów, selektywność warunków wyszukiwania, częstotliwość zmian danych i sposób przekazywania parametrów. Pomiar należy wykonywać dla reprezentatywnych danych, ponieważ procedura szybka na małej tabeli może zachowywać się zupełnie inaczej po jej rozrośnięciu.

Parametry mierzone podczas analizy

Podstawowym wskaźnikiem jest czas wykonania procedury, najlepiej rozdzielony na czas procesora i czas oczekiwania. Krótki czas procesora przy długim czasie całkowitym może oznaczać oczekiwanie na odczyt z dysku, blokadę utrzymywaną przez inną transakcję albo przeciążenie systemu. Warto rejestrować także wartości minimalne, medianę, percentyle oraz maksimum. Średnia może ukrywać pojedyncze, bardzo wolne wywołania, które w praktyce powodują opóźnienia odczuwalne dla użytkowników.

Drugą grupę stanowią dane o pracy planu wykonania. Analizuje się między innymi liczbę odczytanych stron danych, liczbę wierszy przekazanych między operatorami, kolejność operacji oraz użycie indeksów. Duża różnica między szacowaną a rzeczywistą liczbą rekordów wskazuje na niedokładne statystyki, nietypowe rozkłady danych albo nieoptymalne warunki zapytania. Należy również sprawdzić, czy procedura nie wykonuje niepotrzebnych sortowań, powtarzalnych podzapytań, konwersji typów lub operacji na całych tabelach, gdy potrzebny jest tylko niewielki zakres danych.

Źródła spadku wydajności

Jedną z częstych przyczyn spowolnienia jest niezgodność warunku wyszukiwania z dostępnym indeksem. Jeżeli filtr obejmuje kolumnę bez odpowiedniego indeksu, silnik może odczytać znaczną część tabeli i dopiero później odrzucić niepasujące wiersze. Problemem bywają również funkcje stosowane bezpośrednio na kolumnach, porównywanie wartości o różnych typach oraz niejawne konwersje. Takie operacje mogą uniemożliwić wykorzystanie struktury indeksu w sposób efektywny.

Wydajność pogarszają także zbyt długie transakcje. Procedura, która modyfikuje dane i przez długi czas utrzymuje blokady, może powodować kolejki innych operacji. Znaczenie ma również kolejność dostępu do tabel, obsługa błędów i sposób wycofywania zmian. Przy dużych zbiorach danych nieuzasadnione przetwarzanie wiersz po wierszu zwykle generuje większy narzut niż operacja zbiorowa. Nie każdą procedurę należy jednak skracać za cenę zwiększenia zużycia pamięci lub pogorszenia spójności; zmiana musi uwzględniać pełny przebieg transakcji.

Diagnostyka i poprawa procedur

Rzetelna diagnostyka zaczyna się od odtworzenia problematycznego wywołania z tymi samymi lub podobnymi parametrami, które występują w pracy produkcyjnej. Następnie porównuje się czas wykonania, obciążenie zasobów i plan przed oraz po zmianie. Przydatne są dzienniki wywołań, narzędzia monitorujące zapytania, statystyki tabel i analiza oczekiwań związanych z blokadami. Każdy pomiar powinien uwzględniać możliwość wpływu pamięci podręcznej, równoległych operacji oraz chwilowego obciążenia systemu.

Poprawa może polegać na zmianie zapytania, dodaniu lub przebudowie indeksu, aktualizacji statystyk, ograniczeniu zwracanych kolumn, skróceniu zakresu transakcji albo usunięciu zbędnych operacji pośrednich. Zmianę trzeba zweryfikować zarówno dla małej, jak i dużej liczby danych oraz dla różnych wartości parametrów. Procedura powinna zachować oczekiwane wyniki, poprawną obsługę wartości pustych, właściwe zachowanie w przypadku błędu i bezpieczne wycofanie częściowo wykonanej transakcji. W środowisku użytkowanym na co dzień analiza wydajności jest elementem diagnostyki aplikacji, gdy opóźnienia wynikają z pracy bazy danych, zapytań lub współdzielenia zasobów przez wiele procesów.