sp_who2 – tutte le sessioni sql
sp_who2 active — solo le sessioni attive
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id
JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE resource_database_id = db_id() AND Obj.Type = 'U'
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
kill SPID – per killare un processo (spid). SPID: numero sessione da dedurre con la sp_who2
select * from sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) stext
order by req.total_elapsed_time
http://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs
SELECT TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = –Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = –Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = –Converted from microseconds
qs.total_elapsed_time/1000000,
st.text,
qp.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC