SYS.DM_EXEC_PROCEDURE_STATS

sys.dm_exec_procedure_stats prezentuje statystyki wykonania procedur składowanych.

Licznik wykonań przypięty do planu procedury

Ten dynamiczny widok zarządzania zwraca po jednym wierszu na każdą procedurę składowaną, której skompilowany plan siedzi obecnie w pamięci podręcznej. Kluczowe kolumny to database_id i object_id, execution_count, cached_time oraz last_execution_time, a dalej pary sumaryczne i skrajne: total_worker_time i max_worker_time dla czasu procesora, total_elapsed_time dla czasu zegarowego, total_logical_reads i total_physical_reads dla odczytów. Nazwę procedury odczytuje się funkcją OBJECT_NAME z dwoma argumentami, bo bez podania identyfikatora bazy zwróci ona nazwę z bieżącego kontekstu.

Dlaczego liczby potrafią zniknąć z dnia na dzień

Widok nie jest dziennikiem, tylko odbiciem zawartości cache planów. Restart instancji, wyczyszczenie cache poleceniem DBCC FREEPROCCACHE, przełączenie bazy w tryb offline, zmiana definicji procedury poprzez ALTER albo zwykłe wywłaszczenie planu pod presją pamięci kasują dotychczasowe statystyki. Kolumna cached_time mówi, od kiedy liczone są bieżące wartości, i bez niej porównywanie execution_count między dniami nie ma sensu. Z tego powodu przy stałym monitoringu zrzuca się zawartość widoku do tabeli historycznej o ustalonych porach i liczy różnice. Warto też pamiętać, że czasy podawane są w mikrosekundach, więc średni czas wykonania w milisekundach to total_elapsed_time podzielone przez execution_count i przez tysiąc.

Pułapki interpretacyjne przy szukaniu winnej procedury

Sortowanie po total_worker_time wskazuje procedury zjadające najwięcej procesora łącznie, ale na czele stanie krótka procedura wołana sto tysięcy razy, a nie ta, która raz dziennie miele godzinę. Odwrotnie, sortowanie po max_elapsed_time wyciąga pojedyncze wykonania spowolnione przez blokadę, a nie realny problem wydajnościowy. Statystyki obejmują wyłącznie procedury - zapytania ad hoc wysyłane przez aplikację trzeba szukać w sys.dm_exec_query_stats. Wewnętrzne wywołania zagnieżdżone liczą się osobno, więc czas procedury nadrzędnej zawiera czas wywołanych przez nią podprocedur i proste sumowanie kolumn daje wynik zawyżony. Dostęp wymaga uprawnienia VIEW SERVER STATE, którego zwykłe konto aplikacji handlowej nie ma.

Strojenie baz systemów sprzedażowych

Przy zgłoszeniach o mulącym Subiekcie, WAPRO czy Comarchu ten widok jest zwykle pierwszym miejscem, do którego zaglądamy - pokazuje w kilka sekund, czy wąskim gardłem jest raport magazynowy, czy operacja księgowania. Zestawiamy go z planem wykonania i statystykami indeksów, dokładamy brakujący indeks albo poprawiamy sposób wywołania procedury z poziomu aplikacji. Robimy to zdalnie i na miejscu, o dowolnej porze, bo w firmach handlowych okno serwisowe wypada zwykle po zamknięciu sklepu.