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