SQL Server ‐ Performance Monitoring - shaysalomon12/Data-Engineer GitHub Wiki
sp_Whoisactive
exec DBA..sp_Whoisactive @get_additional_info = 1;
- http://whoisactive.com/downloads/
- This will list all active sessions with CPU, Reads, Writes, Blocking_session_id, query_plan and more. Need to download the latest version from the link above and run
Connected Sessions:
select
host_name,
db_name(database_id) as [database],
status,
count(*) as count
from sys.dm_exec_sessions
where host_name is not null
--and db_name(database_id) = 'Bagrut'
group by host_name, database_id, status
order by 2;
-- 3 Track Whats Running Now
SELECT
getdate() as MonitorTime,
req.start_time,
cast ( getdate()-req.start_time as time ) elapsed,
DB_NAME(req.database_id) db,
sess.host_name,
'(' + cast ( sess.host_process_id as varchar ) + ')' processID,
--client_interface_name ,
COALESCE(req.logical_reads, sess.logical_reads) AS logical_reads,
COALESCE(req.reads, sess.reads) AS physical_reads,
COALESCE(req.writes, sess.writes) AS writes,
COALESCE(req.CPU_time, sess.CPU_time) AS cpu_time,
req.total_elapsed_time duration_msecs,
stxt.text [sql],
SUBSTRING(stxt.text,
(req.statement_start_offset/2) + 1,
((CASE req.statement_end_offset
WHEN -1 THEN datalength(stxt.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2) + 1) as statement_text,
qpln.query_plan,
req.status,
req.wait_type,
req.session_id,
req.wait_resource,
req.last_wait_type ,
sess.login_name,
sess.login_time,
sess.program_name
FROM sys.dm_exec_requests req WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) stxt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qpln
FULL OUTER JOIN sys.dm_exec_sessions sess WITH (NOLOCK) ON req.session_id = sess.session_id
WHERE req.session_id > 50 -- Ignore system spids
AND req.session_id NOT IN (@@SPID) -- Ignore the current statement.
AND last_wait_type not in ('WAITFOR','BACKUPIO')
AND req.total_elapsed_time >= 5000
and DB_NAME(req.database_id) <> 'DBA'
and stxt.text not like 'Backup%'
;
Track Blocking Sessions
;with locks
as
(
select
t1.resource_type as [lock_type]
,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk_object]
,t1.request_mode as [lock_req] -- lock requested
--Waiter:
,t1.request_session_id as [waiter_sid] -- spid of waiter
,[waiter].[host_name] as waiter_hostname -- waiting spid hostname
,[waiter].[login_name] as waiter_loginame -- waiting spid login_name
,[waiter].[program_name] as waiter_program_name -- waiting spid program_name
,waiter.open_transaction_count as waiter_opentran
,isnull(rw.status,waiter.status) as waiter_status
,STw.transaction_id as waiter_transid
,t2.wait_duration_ms as [wait_time]
,rw.wait_type as waiter_wait_type
,waiter.last_request_start_time As waiter_starttime
,wtext.text as waiter_batch
,substring(wtext.text,rw.statement_start_offset/2,
(case when rw.statement_end_offset = -1
then len(convert(nvarchar(max), wtext.text)) * 2
else rw.statement_end_offset end - rw.statement_start_offset)/2) as waiter_stmt --- this is the statement executing right now
--Blocker:
,t2.blocking_session_id as [blocker_sid] -- spid of blocker
,blocker.[host_name] as blocker_hostname
,blocker.[login_name] as blocker_loginame
,blocker.[program_name] as blocker_program_name
,blocker.open_transaction_count as blocker_opentran
,isnull(rb.status,blocker.status) as blocker_status
,STb.transaction_id as blocker_transid
,rb.wait_type as blocker_wait_type
,blocker.last_request_start_time As blocker_starttime
,btext.text as blocker_batch
,substring(btext.text,rb.statement_start_offset/2,
(case when rb.statement_end_offset = -1
then len(convert(nvarchar(max), btext.text)) * 2
else rb.statement_end_offset end - rb.statement_start_offset)/2) as blocker_stmt
,GETDATE() record_time
from sys.dm_tran_locks as t1
join sys.dm_os_waiting_tasks as t2
on t1.lock_owner_address = t2.resource_address
--Blocker:
JOIN sys.dm_exec_sessions blocker
ON blocker.session_id=t2.blocking_session_id
left join SYS.DM_TRAN_SESSION_TRANSACTIONS STb
on blocker.session_id=stb.session_id
LEFT JOIN sys.dm_exec_requests rb
on blocker.session_id=rb.session_id
JOIN sys.dm_exec_connections cb
on blocker.session_id=cb.session_id
cross apply sys.dm_exec_sql_text(isnull(rb.sql_handle,cb.most_recent_sql_handle)) as btext
--Waiter:
JOIN sys.dm_exec_sessions waiter
ON waiter .session_id=t1.request_session_id
left join SYS.DM_TRAN_SESSION_TRANSACTIONS STw
on waiter.session_id=stw.session_id
LEFT JOIN sys.dm_exec_requests rw
on waiter.session_id=rw.session_id
JOIN sys.dm_exec_connections cw
on waiter.session_id=cw.session_id
cross apply sys.dm_exec_sql_text(isnull(rw.sql_handle,cw.most_recent_sql_handle)) as wtext
)
select * from locks
;
Dynatrace - Top database statements
To access the Top database statements page
- In the [Dynatrace menu], select Multidimensional analysis or Databases.
- Select Top database statements.
For example, to see top SQLs for database SQL Server "ItemStore_Ofakim":
Performance Dashboard Reports
- Connect to SQL Server Instance using SSMS.
- Right click the instance -> Reports -> Standard Reports -> Performance Dashboard
If you want to store historical data you need to install "SQL Server Management Data Warehouse":