DBAdmin/inbox/DataBase/SQLServer/performance.md

1. sys.dm_exec_requests i sys.dm_exec_sessions - pokazuje jeszcze idąde zapytania

cpu_time Czas procesora w milisekundach wykorzystany przez żądanie lub przez żądania w sesji. total_elapsed_time Całkowity czas w milisekundach od odebrania żądania lub od początku sesji. reads Liczba odczytów wykonanych przez żądanie lub żądania w sesji. writes Liczba zapisów wykonanych przez żądanie lub żądania w sesji. logical_reads Liczba odczytów logicznych wykonanych przez żądanie lub żądania w sesji. row_count Liczba wierszy zwróconych do klienta na podstawie tego żądania.

SELECT cpu_time, reads, total_elapsed_time, logical_reads, row_count FROM sys.dm_exec_requests WHERE session_id = 56 GO SELECT cpu_time, reads, total_elapsed_time, logical_reads, row_count FROM sys.dm_exec_sessions WHERE session_id = 56

2. sys.dm_exec_query_stats - zintegrowane informacje o wydajności

SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE objectid = OBJECT_ID('dbo.test')

SELECT SUBSTRING(text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END

• statement_start_offset)/2) + 1) AS statement_text, * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE objectid = OBJECT_ID('dbo.test')

pokazuje tylko zakończone zapytania Opis kolumn total_worker_time Całkowity czas procesora w mikrosekundach (z dokładnością do milisekund) dla wszystkich wykonań planu od jego kompilacji. last_worker_time Czas procesora w mikrosekundach (z dokładnością do milisekund) dla ostatniego wykonania planu. min_worker_time Najkrótszy czas procesora w mikrosekundach (z dokładnością do milisekund) spośród wszystkich wykonań planu. max_worker_time Maksymalny czas procesora w mikrosekundach (z dokładnością do milisekund) spośród wszystkich wykonań planu. total_physical_reads Sumaryczna liczba odczytów fizycznych dla planu od czasu jego skompilowania. last_physical_reads Liczba odczytów fizycznych podczas ostatniego wykonania planu. min_physical_reads Minimalna liczba odczytów fizycznych spośród wszystkich wykonań planu. max_physical_reads Maksymalna liczba odczytów fizycznych spośród wszystkich wykonań planu. total_logical_writes Sumaryczna liczba zapisów logicznych dla planu od czasu jego skompilowania. last_logical_writes Liczba zapisów logicznych podczas ostatniego wykonania planu. min_logical_writes Minimalna liczba zapisów logicznych spośród wszystkich wykonań planu. max_logical_writes Maksymalna liczba zapisów logicznych spośród wszystkich wykonań planu. total_logical_reads Sumaryczna liczba odczytów logicznych dla planu od czasu jego skompilowania. last_logical_reads Liczba odczytów logicznych podczas ostatniego wykonania planu. min_logical_reads Minimalna liczba odczytów logicznych spośród wszystkich wykonań planu. max_logical_reads Maksymalna liczba odczytów logicznych spośród wszystkich wykonań planu. total_clr_time Czas w mikrosekundach (z dokładnością do milisekund) spędzony wewnątrz obiektów wspólnego środowiska uruchomieniowego (CLR) .NET Framework podczas wszystkich uruchomień tego planu od czasu jego kompilacji. Obiekty CLR mogą być procedurami przechowywanymi, funkcjami, wyzwalaczami, typami i agregacjami. last_clr_time Czas w mikrosekundach (z dokładnością do milisekund) spędzony wewnątrz obiektów CLR .NET Framework podczas ostatniego uruchomienia planu. Obiekty CLR mogą być procedurami przechowywanymi, funkcjami, wyzwalaczami, typami i agregacjami. min_clr_time Minimalny czas w mikrosekundach (z dokładnością do milisekund) spędzony wewnątrz obiektów CLR .NET Framework spośród wszystkich uruchomień planu. Obiekty CLR mogą być procedurami przechowywanymi, funkcjami, wyzwalaczami, typami i agregacjami. max_clr_time Maksymalny czas w mikrosekundach (z dokładnością do milisekund) spędzony wewnątrz obiektów CLR .NET Framework spośród wszystkich uruchomień planu. Obiekty CLR mogą total_elapsed_time Sumaryczny czas w mikrosekundach (z dokładnością do milisekund) dla wszystkich zakończonych uruchomień planu. last_elapsed_time Czas w mikrosekundach (z dokładnością do milisekund) dla ostatniego uruchomienia planu. min_elapsed_time Najkrótszy czas w mikrosekundach (z dokładnością do milisekunda) spośród wszystkich ukończonych uruchomień planu. max_elapsed_time Najdłuższy czas w mikrosekundach (z dokładnością do milisekunda) spośród wszystkich ukończonych uruchomień planu.

3. Szukanie kosztownych zapytań

SELECT TOP 20 query_stats.query_hash, SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS avg_cpu_time, MIN(query_stats.statement_text) AS statement_text FROM (SELECT qs.*, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE qs.statement_end_offset END

• qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st) AS query_stats GROUP BY query_stats.query_hash ORDER BY avg_cpu_time DESC

SELECT TOP 20 query_plan_hash, SUM(total_worker_time) / SUM(execution_count) AS avg_cpu_time, MIN(plan_handle) AS plan_handle, MIN(text) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS st GROUP BY query_plan_hash ORDER BY avg_cpu_time DESC

Przykłady powyższe bazują na czasie procesora

Zaytanie zwraca najbardziej kosztowne zapytania SELECT TOP 20 SUBSTRING(st.text, (er.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END

• er.statement_start_offset)/2) + 1) AS statement_text , * FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st ORDER BY total_elapsed_time DESC

4. Zdarzenia rozszerzone 4.1 Lista zdarzeń jakie możemy monitorować SELECT name, description FROM sys.dm_xe_objects WHERE object_type = 'event' AND (capabilities & 1 = 0 OR capabilities IS NULL) ORDER BY name

--- tworzenie takiego trace

CREATE EVENT SESSION test ON SERVER ADD EVENT sqlserver.module_end( ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text)), ADD EVENT sqlserver.rpc_completed( ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text)), ADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text)), ADD EVENT sqlserver.sql_batch_completed( ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text)), ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash, sqlserver.sql_text)) ADD TARGET package0.ring_buffer WITH (STARTUP_STATE=OFF)

--- nie można zapomnieć o jej uruchomieniu

ALTER EVENT SESSION [test] ON SERVER STATE=START

--- odczytanie danych ze zdarzeń, dane są w formacie XML

SELECT name, target_name, execution_count, CAST(target_data AS xml) AS target_data FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE s.name = 'test'

--- odpytanie XML żeby nie trzeba było za bardzo czytać XML

SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name, event_data.value('(event/action[@name="query_hash"]/value)[1]','varchar(max)') AS query_hash, event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'int') AS cpu_time, event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS duration, event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'int') AS logical_reads, event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'int') AS physical_reads, event_data.value('(event/data[@name="writes"]/value)[1]', 'int') AS writes, event_data.value('(event/data[@name="statement"]/value)[1]', 'varchar(max)') AS statement FROM(SELECT evnt.query('.') AS event_data FROM (SELECT CAST(target_data AS xml) AS target_data FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE s.name = 'test' AND t.target_name = 'ring_buffer' ) AS data CROSS APPLY target_data.nodes('RingBufferTarget/event') AS xevent(evnt) ) AS xevent(event_data)

--- powyższe zapytanie będzie nie bardzo czytelne można więc przerzucić dane do tabeli np SELECT INTO i wykonać poniższe zapytanie SELECT query_hash, SUM(cpu_time) AS cpu_time, SUM(duration) AS duration, SUM(logical_reads) AS logical_reads, SUM(physical_reads) AS physical_reads, SUM(writes) AS writes, MAX(statement) AS statement FROM #eventdata GROUP BY query_hash

--- po zakończeni analizy należy usunąć ALTER EVENT SESSION [test] ON SERVER STATE=STOP GO DROP EVENT SESSION [test] ON SERVER

--- Uruchomienie dla jednej sesji CREATE EVENT SESSION [test] ON SERVER ADD EVENT sqlos.wait_info( WHERE ([sqlserver].[session_id]=(61))) ADD TARGET package0.ring_buffer WITH (STARTUP_STATE=OFF) GO --- Uruchomienie zdarzenia: ALTER EVENT SESSION [test] ON SERVER STATE=START --- odczytanie tych danych SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name, event_data.value('(event/data[@name="wait_type"]/text)[1]', 'varchar(40)') AS wait_type, event_data.value('(event/data[@name="duration"]/value)[1]', 'int') AS duration, event_data.value('(event/data[@name="opcode"]/text)[1]', 'varchar(40)') AS opcode, event_data.value('(event/data[@name="signal_duration"]/value)[1]', 'int') AS signal_duration FROM(SELECT evnt.query('.') AS event_data FROM (SELECT CAST(target_data AS xml) AS target_data FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address WHERE s.name = 'test' AND t.target_name = 'ring_buffer' ) AS data CROSS APPLY target_data.nodes('RingBufferTarget/event') AS xevent(evnt) ) AS xevent(event_data)