VN:F [1.7.9_1023]
Rating: 5.0/5 (2 votes cast)

SQL Server od wersji 2005 to Wielki Kolekcjoner. Zbiera liczne dane, z których wprawiony DBA może odczytać naprawdę wiele. Także w zakresie trudnej sztuki optymalizacji SQL Server może być naszym przyjacielem (choć bywa też naszym zaciekłym przeciwnikiem…).

W ostatnim czasie miałem możliwość zastosowania wszechobecnych w SQL Server 2008 widoków i funkcji dynamicznych (DMV/DMF) w procesie optymalizacji bazy danych. Jednym z zadań było odnajdowanie “podejrzanych” operacji w obiektach sporej bazy danych. W tym celu napisałem kawałek kodu T-SQL, który wyglądał mniej więcej tak:

USE master;

GO

IF OBJECT_ID('dbo.sp_querystats', 'V') IS NOT NULL

  DROP VIEW dbo.sp_querystats;

GO

CREATE VIEW dbo.sp_querystats

AS

SELECT

  DB_NAME(st.dbid) AS [database_name],

  QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + '.' +

  QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) AS [object_name],

  SUBSTRING(

    st.text,

    (qs.statement_start_offset/2)+1,

    (CASE qs.statement_end_offset

      WHEN -1 THEN DATALENGTH(st.text)

      ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2 + 1

  ) AS statement_text,

  qs.execution_count,

  qs.creation_time,

  qs.last_execution_time,

  (qs.total_logical_reads + qs.total_physical_reads) / qs.execution_count AS avg_reads,

  qs.total_logical_reads + qs.total_physical_reads AS total_reads,

  qs.max_logical_reads + qs.max_physical_reads AS max_reads,

  qs.min_logical_reads + qs.min_physical_reads AS min_reads,

  qs.max_logical_reads,

  qs.total_logical_reads,

  qs.total_physical_reads,

  qs.max_physical_reads,

  qs.total_elapsed_time / qs.execution_count AS avg_duration,

  qs.total_elapsed_time  AS total_duration,

  qs.max_elapsed_time  AS max_duration,

  qs.min_elapsed_time  AS min_duration,

  qs.total_worker_time / qs.execution_count AS avg_cpu,

  qs.total_worker_time AS total_cpu,

  qs.max_worker_time AS max_cpu,

  qs.min_worker_time AS min_cpu,

  pl.query_plan

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS pl;

GO

Widok sp_querystats posłużył mi do odpytywania danych dotyczących wydajności zapytań kolekcjonowanych przez SQL Server 2008. Krótki komentarz do kodu widoku:

  • Dzięki użyciu widoku sys.dm_exec_query_stats (dane z tego widoku są wyrzucane przy każdym uruchomieniu usługi SQL Server) wydobywam informacje o czasie trwania, zużyciu procesora, operacjach I/O (logicznych i fizycznych) – to są najczęstsze kryteria wyszukiwania problemów optymalizacyjnych.
  • Funkcja sys.dm_exec_sql_text umożliwia wydobycie takich informacji, jak nazwa bazy danych obiektu, z którego pochodziła składnia wychwycona przez SQL Server, nazwa samego obiektu oraz kod T-SQL fragmentu obiektu
  • Funkcja sys.dm_exec_query_plan daje wgląd w graficzny plan wykonania całego obiektu (lub zapytania – jeżeli to zapytanie ad-hoc).
  • Dzięki odpowiedniemu zastosowaniu funkcji systemowych OBJECT_NAME i OBJECT_SCHEMA_NAME (warto zwrócić uwagę, że funkcje te mogą przyjmować dwa parametry – drugim może być identyfikator bazy danych), uzyskuję pełne nazwy obiektów z wszystkich baz danych.
  • Widok ma w nazwie prefiks sp_ i jest założony w bazie master, by można się do niego odwołać z dowolnej bazy danych na instancji SQL Server.

Co mogę osiągnąć dzięki takiemu widokowi?

  • Mogę znajdować zapytania, które miały najsłabsze czasy wykonania, najwyższe zużycie procesora czy dokonały największej ilości odczytów z bazy danych. Przykład (najdłuższy średni czas wykonania):
SELECT TOP 50 * FROM sp_querystats ORDER BY avg_duration DESC;

  • Mogę sprawdzić, które zapytania wykonują się z dużym rozrzutem wymienionych powyżej kryteriów, co może świadczyć na przykład o rozmnożonych planach wykonania procedury składowanej. Na przykład: gdzie minimalny czas wykonania znacznie różni się od czasu maksymalnego:
SELECT TOP 50 * FROM sp_querystats ORDER BY (max_duration - min_duration) DESC;

  • Jakie składnie z danego obiektu (widoku, procedury, funkcji) siały największe spustoszenie na serwerze. Stosuję taką technikę, gdy uda mi się zgrubnie Profilerem “złapać” wywołanie felernego obiektu (zazwyczaj procedury lub funkcji). Na przykład: która składnia w procedurze spowodowała największe obciążenie procesora:
SELECT * FROM sp_querystats

WHERE st.objectid = OBJECT_ID(N'dbo.MojaProcedura')

ORDER BY avg_cpu DESC

  • Mogę podejrzeć plan wykonania (dla obiektów proceduralnych wyświetlane są plany wykonania wszystkich składni zawartych w obiekcie). Wystarczy kliknąć na interesującym nas rekordzie w kolumnie query_plan (oczywiście jeżeli w Management Studio wynik wyświetlamy w siatce, a nie w postaci tekstowej).
  • Mogę przeszukać plany wykonania zapisane w formacie XML w kolumnie query_plan i znaleźć niepokojące operatory (skany, niesłuszne ideowo typy złączeń, wyszukiwania z predykatami, opóźnione filtrowania, spoole itd.) czy podpowiedzi SQL Servera dotyczące brakujących indeksów (o analizie planów wykonania w formacie XML napiszę innym razem, bo to temat na spory artykuł).
  • Mogę sprawdzić, ile razy każda składnia była wykorzystana (i na przykład zweryfikować, jaki przebieg w procedurach mają instrukcje sterujące IF lub ocenić, czy jakaś składnia T-SQL nie powoduje masowego wywołania funkcji skalarnej). Na wartości z kolumny execution_count warto zwracać uwagę także, gdy zabieramy się do optymalizacji – czasem nie warto brać w swoje ręce obiektów, które są używane raz na kilka miesięcy…

Widok sp_querystats daje mi pogląd na wiele spraw. Przechwytując w procesie optymalizacji informacje dostarczone przez ślady (SQL Trace i Profiler) mogę niemal “od ręki” odnaleźć składnie wykonywane w ramach podejrzanego obiektu i zobaczyć agregację wszystkich wykonań tych poleceń. Czasem jest to wystarczający bodziec do podjęcia działań mających na celu dokonanie optymalizacji konkretnego zapytania. Polecam używanie DMV jako uzupełnienia dla wspomnianego SQL Trace i ewentualnych innych narzędzi wykorzystywanych do optymalizacji. Używajmy T-SQL do monitorowania T-SQL :-)

[PL] SQL Server 2008 – DMV i sztuka optymalizacji5.052