Posts tagged procedury

Geneza

Niedawno na forum portalu WSS.pl użytkownik miscu zapytał, w jaki sposób znaleźć datę ostatniego wykonania procedury składowanej na SQL Server 2005. Odpowiedziałem wówczas na jego pytanie, ale potem sprawdziłem, czy w SQL Server 2008 nie dałoby się uzyskać tej informacji nieco łatwiej, bez “grzebania” w kilku widokach i wykonywania podejrzanych złączeń w nadziei, że otrzymany wynik będzie choć trochę przedstawiał prawdę prawdziwą. I owszem, można.

Statystyki procedur

W SQL Server 2008 (i oczywiście w R2 także) dostajemy ciekawe widoki DMV – sys.dm_exec_procedure_stats i sys.dm_exec_trigger_stats. Widoki te pokazują statystyki wykonania odpowiednio procedur składowanych i wyzwalaczy. Oba widoki są widokami globalnymi, tzn. przechowują statystyki dla wszystkich baz danych naraz (nawet dla bazy mssqlresource coś tam się znajdzie). Oba widoki bazują na danych zebranych od ostatniego uruchomienia instancji SQL Server i bazują na tym, co siedzi w cache’u planów wykonania. Napisałem kawałek kodu (pewnie nie ja pierwszy), który wybiera co ciekawsze dane dotyczące procedur składowanych:

SELECT
  DB_NAME(s.database_id) AS [database_name],
  QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id], s.database_id)) +
  '.' +
  QUOTENAME(OBJECT_NAME(s.[object_id], s.database_id)) AS [procedure_name],
  s.cached_time,
  s.last_execution_time,
  s.execution_count,
  CONVERT(
    decimal(38,2),
    (s.execution_count * 1.0 / t.uptime)
  ) AS executions_per_minute,
  CONVERT(
    decimal(38,2),
    (s.max_elapsed_time * 1.0 / 1000)
  ) AS max_execution_time_miliseconds,
  CONVERT(
    decimal(38,2),
    ((s.total_elapsed_time * 1.0 / 1000) / s.execution_count)
  ) AS avg_execution_time_miliseconds
FROM sys.dm_exec_procedure_stats AS s
CROSS JOIN (
  SELECT
    DATEDIFF(minute, sqlserver_start_time, GETDATE()) AS uptime
  FROM sys.dm_os_sys_info
) AS t
WHERE s.database_id <> 32767;

Krótki komentarz do kodu:

• wyświetlane dane zawierają:
  • database_name – nazwę bazy danych, w której znajduje się procedura,
  • procedure_name - dwuczłonową nazwę procedury,
  • cached_time – datę zapisania planu procedury w cache’u,
  • last_execution_time – datę ostatniego wykonania procedury,
  • execution_count – ilość wykonań procedury od ostatniego restartu usługi SQL Servera,
  • executions_count_per_minute – ilość wykonań procedury na minutę od ostatniego restartu usługi SQL Servera,
  • max_execution_time_miliseconds – maksymalny czas wykonania procedury wyrażony w milisekundach,
  • avg_execution_time_miliseconds – średni czas wykonania procedury wyrażony w milisekundach.
• wszystkie czasy w widoku sys.dm_exec_procedure_stats są wyrażone w mikrosekundach, stąd operacje dzielenia przez 1000,
• datę startu instancji wyciągam z widoku DMV sys.dm_os_sys_info (polecam do niego zajrzeć, bo w jednym wierszu zawiera sporo ciekawych informacji),
• w klauzuli WHERE odfiltrowuję obiekty z ukrytej bazy systemowej mssqlresource, bo i tak na nic nam informacje o procedurach z tej bazy :-)

Zamiast filtru eliminującego obiekty z mssqlresource, można pokusić się o filtrowanie względem bazy, w której chcemy optymalizować procedury:

… WHERE s.database_id = DB_ID('MojaBazaDanych');

Przykładowy fragment wyniku powyższego zapytania:

Znajdź nieużywane procedury

Widok sys.dm_exec_procedure_stats może się też przydać w scenariuszu, w którym w bazie danych zawierającą dużą ilość procedur chcemy odnaleźć te procedury, które nie są wykorzystywane (nie zostały użyte od ostatniego uruchomienia serwera). Do tego celu można użyć na przykład takiego zapytania (tu – szukam procedur nieużywanych w bazie AdventureWorks2008R2):

USE AdventureWorks2008R2; -- tu wstaw nazwę Twojej bazy danych
GO
SELECT
  QUOTENAME(SCHEMA_NAME(p.[schema_id])) +
  '.' +
  QUOTENAME(p.name) AS unused_procedure
FROM sys.procedures AS p
INNER JOIN sys.sql_modules AS m
ON p.[object_id] = m.[object_id]
LEFT JOIN sys.dm_exec_procedure_stats AS s
ON s.database_id = DB_ID() AND p.[object_id] = s.[object_id]
WHERE s.[object_id] IS NULL AND m.is_recompiled = 0;
GO

Jeżeli serwer nie był restartowany przez odpowiednio długi czas i wiesz, że zostały przeprowadzone przypadki biznesowe wykorzystujące procedury, możesz założyć, że procedury wylistowane przez powyższe zapytanie wymagają sprawdzenia, czy są do czegokolwiek potrzebne (od polityki firmy i solidności programistów / administratorów zależy, jak często zdarzy się, że ktoś założy na bazie produkcyjnej tymczasowe obiekty, a następnie zapomni po sobie posprzątać). Celowo w powyższym zapytaniu sięgam do widoku sys.sql_modules, gdyż jest tam informacja o tym, czy procedura została utworzona z opcją WITH RECOMPILE (kolumna is_recompiled). A jeśli tak jest, jej planu nie znajdziesz w cache’u i nie możesz się sugerować jej brakiem w widoku sys.dm_exec_procedure_stats.

Słowo o wyzwalaczach

Analogiczne informacje można uzyskać także dla wyzwalaczy. I to nie tylko dla wyzwalaczy DML, ale także DDL (w tym dla logon triggerów). Do wykorzystania oprócz DMV sys.dm_exec_trigger_stats są widoki:

• sys.triggers – zwraca metadane wyzwalaczy na poziomie bieżącej bazy danych (DML i DDL),
• sys.server_triggers – zwraca metadane wyzwalaczy DDL na poziomie serwera (w tym logon triggerów).