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