Melihat seluruh koneksi user ke SQL Server
Untuk mengetahui komputer mana saja yang sedang mengakses database.
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
c.client_net_address,
DB_NAME(s.database_id) AS database_name,
s.status,
s.login_time,
s.last_request_start_time,
s.last_request_end_time
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
WHERE s.is_user_process = 1
ORDER BY s.last_request_start_time DESC;
Melihat query paling berat (CPU dan I/O)
Untuk mengetahui query yang paling banyak menggunakan resource SQL Server.
SELECT TOP 20
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_elapsed_time / 1000 AS total_duration_ms,
qs.total_logical_reads,
qs.total_logical_writes,
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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;



