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)