SQL Server stale monitoruje się i gromadzi informacje przydatne do monitorowania stanu zdrowia instancji, stwierdza problemy, takie jak brakujące indeksy, i optymalizuje zapytania. DMO są wirtualnymi obiektami pozwalającymi na dostęp do danych kolekcjonowanych przez SQL Serwer w pamięci. Do tych obiektów należą widoki i funkcje dynamiczne. Wszystkie obiekty DMO należą do schematu system a ich nazwa rozpoczyna się od prefiksu dm_. Niektóre obiekty dynamiczne pokazują aktualny stan instancji serwera a inne gromadzą dane od startu instancji. Obiekty dynamiczne mogą być szczególnie przydatne przy monitorowaniu problematycznych zapytań, gdy trzeba czekać na kolejne ich uruchomienie lub ich wynik. DMO korzystają z już skumulowanych informacji. Wystarczy wykonać zapytanie T-SQL używające odpowiedniego obiektu DMO aby uzyskać użyteczne informacje. Niestety DMO posiadają również wady. Bardzo istotną cechą to rozpatrzenia jet to kiedy ostatnio instancja była uruchamiana ponownie. Kumulowane informacje nie są tak użyteczne jeżeli instancja jest często uruchamiana ponownie. SQL Serwer 2012 udostępnia ponad 130 obiektów dynamicznych. Lista wszystkich obiektów dostępna jest na stronie: http://msdn.microsoft.com/en-us/library/ms188754.aspx. W tej lekcji omówimy tylko najważniejsze dotyczące zapytań i indeksów.

Najważniejsze DMO dla optymalizacji zapytań

Obiekty DMO są pogrupowane na kategorie. Dla optymalizacji zapytań najbardziej użyteczne grupy to:

  • SQL Server Operating System (SQLOS)–related DMOs - zawiera obiekty pozwalające monitorować zasoby systemowe przypisane do SQL Serwera.
  • Execution-related DMOs - zawiera obiekty pozwalające monitorować wykonywane zapytania, ich tekst, plan zapytania, liczbę wykonań itd.
  • Index-related DMOs - zawiera obiekty pozwalające monitorować użycie indeksów oraz pokazujące brakujące indeksy.

Zaczynając analizę można podejrzeć stan systemu dzięki widokowi dynamicznemu sys.dm_os_sys_info:

SELECT cpu_count AS logical_cpu_count,
cpu_count / hyperthread_ratio AS physical_cpu_count,
CAST(physical_memory_kb / 1024. AS int) AS physical_memory__mb,
sqlserver_start_time
FROM sys.dm_os_sys_info;

Zapytanie zwraca informacje o liczbie logicznych i fizycznych CPU, fizycznej pamięci i czasie uruchomienia instancji SQL Serwera. Kolejnym ciekawym widokiem z kategorii SQLOS jest sys.dm_os_waitink_tasks który daje informacje na temat sesji które aktualnie na coś czekają. Przykładowo sesja może być blokowana przez inną sesję z powodu blokad. Łącząc ten widok z widokiem sys.dm_exec_sessions z kategorii execution-related DMOs otrzymujemy informacje o użytkowniku, hoście i aplikacji oczekującej. Użycie flagi is_user_process pozwala na odfiltrowanie sesji systemowych. Oto przykładowe zapytanie:

SELECT S.login_name, S.host_name, S.program_name,
WT.session_id, WT.wait_duration_ms, WT.wait_type,
WT.blocking_session_id, WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
INNER JOIN sys.dm_exec_sessions AS S
ON WT.session_id = S.session_id
WHERE s.is_user_process = 1;

Widok systemowy sys.dm_exec_requests z kategorii execution-relatedDMOs zwraca informacje o aktualnie wykonywanych żądaniach. Zawiera kolumnę sql_handle która jest mapą hash parii wykonywanego kodu T-SQL. Można użyć tej kolumny aby odczytać tekst zapytania z pomocą funkcji systemowej sys.dm_exec_sql_text z tej samej kategorii która przyjmuje wskaźnik sql_handle  jako parametr. Poniższe zapytanie łączy informacje o aktualnych żądaniach, ich oczekiwaniu, tekst partii kodu T-SQL oraz informacje o użytkowniku i aplikacji:

SELECT S.login_name, S.host_name, S.program_name,
R.command, T.text,
R.wait_type, R.wait_time, R.blocking_session_id
FROM sys.dm_exec_requests AS R
INNER JOIN sys.dm_exec_sessions AS S
ON R.session_id = S.session_id
OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS T
WHERE S.is_user_process = 1;

Za pomocą widoku sys.dm_exec_query_stats z kategorii execution-relatedDMOs można uzyskać wiele informacji o wykonywanych zapytaniach. Można odczytać informacje o IO dla zapytania, użycia CPU, czasie wykonania itp. Z pomocą widoku sys.dm_exec_sql_text można równiez odczytać tekst zapytania. Za pomocą widoku sys.dm_exec_query_stats i kolumn statement_start_offsetistatement_end_offset można pobrać tekst pojedynczych zapytań w partii kodu. Oto przykład zapytania:

SELECT TOP (5)
(total_logical_reads + total_logical_writes) AS total_logical_IO,
execution_count,
(total_logical_reads/execution_count) AS avg_logical_reads,
(total_logical_writes/execution_count) AS avg_logical_writes,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC;

Istnieje jeszcze wiele przydatnych DMO. Przykładowo można znależć brakujące indeksy za pomocą sys.dm_db_missing_index_details, sys.dm_db_missing_index_columns, sys.dm_db_missing_index_groups i sys.dm_db_missing_index_group_stats. Założenie za dużej ilości indeksów jest również niepożądane ponieważ pomimo tego, że zapytania ich nie używają to indeksy takie zajmują miejsce w pamięci i muszą być utrzymywane przez serwer. Za pomocą widoków sys.dm_db_index_usage_stats i sys.indexes możemy znaleźć nieużywane indeksy.

Ćwiczenia

I. Nieużywane indeksy

  1. Zresetuj instancję SQL Serwera poprzez kliknięcie prawym przyciskiem myszy na węźle głównym w Obiect Explorer w SSMS i wybranie opcji Restart.
  2. Połącz się ponownie do instancji serwera w SSMS i zmień kontekst na bazę danych TSQL2012.
  3. Znajź indesy nieklastrowe które nie były używane od ostatniego startu serwera za pomocą zapytania: 
    SELECT OBJECT_NAME(I.object_id) AS objectname,
I.name AS indexname,
I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
ON O.object_id = I.object_id
WHERE I.object_id > 100
AND I.type_desc = 'NONCLUSTERED'
AND I.index_id NOT IN
(SELECT S.index_id
FROM sys.dm_db_index_usage_stats AS S
WHERE S.object_id=I.object_id
AND I.index_id=S.index_id
AND database_id = DB_ID('TSQL2012'))
ORDER BY objectname, indexname;
    Zauważ, że wynikiem zapytania są wszystkie indeksy nieklastrowe. Otrzymaliśmy taki wynik ponieważ uruchomiliśmy ponownie instancję serwera i serwer nie zebrał jeszcze informacji na temat użycia indeksów. Pokazuje to jak ważny jest czas działania serwera gdy analizujemy skumulowane dane.

II. Brakujące indeksy

  1. Użyj poniższego zapytania tworzącego tabelę oraz indeks, zawierającą 10 razy mniej danych niż tabela Sales.Orders. Wykonaj również zapytanie wykorzystujące założony indeks:: 
    SELECT N1.n * 100000 + O.orderid AS norderid,
O.*
INTO dbo.NewOrders
FROM Sales.Orders AS O
CROSS JOIN (VALUES(1),(2),(3)) AS N1(n);
GO
CREATE NONCLUSTERED INDEX idx_nc_orderid
ON dbo.NewOrders(orderid);
GO
SELECT norderid
FROM dbo.NewOrders
WHERE norderid = 110248
ORDER BY norderid;
GO
  2. Znajdź brakujące indeksy za pomocą zapytania używającego DMO: 
    SELECT MID.statement AS [Database.Schema.Table],
MIC.column_id AS ColumnId,
MIC.column_name AS ColumnName,
MIC.column_usage AS ColumnUsage,
MIGS.user_seeks AS UserSeeks,
MIGS.user_scans AS UserScans,
MIGS.last_user_seek AS LastUserSeek,
MIGS.avg_total_user_cost AS AvgQueryCostReduction,
MIGS.avg_user_impact AS AvgPctBenefit
FROM sys.dm_db_missing_index_details AS MID
CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIG.index_handle = MID.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
ON MIG.index_group_handle=MIGS.group_handle
ORDER BY MIGS.avg_user_impact DESC;
  3. Usuń utworzoną tabelę za pomocą zapytania: 
    DROP TABLE dbo.NewOrders;

Podsumowanie

  1. Obiekty dynamiczne pozwalają na natychmiastowy dostęp do informacji kolekcjonowanych przez SQL Serwer.
  2. Do analizy zapytań używamy SQLOS i execution-related DMOs.
  3. Obiekty kategorii index-related DMOs nie tylko zwracają informacje na temat użycia indeksów lecz również dają informacje o brakujących indeksach.