Showing posts from December, 2015

SQL - Determine Locking

Gets the list of sessions which are blocking others in SQL WITH [Blocking] AS (SELECT w.[session_id] ,s.[original_login_name] ,s.[login_name] ,w.[wait_duration_ms] ,w.[wait_type] ,r.[status] ,r.[wait_resource] ,w.[resource_description] ,s.[program_name] ,w.[blocking_session_id] ,s.[host_name] ,r.[command] ,r.[percent_complete] ,r.[cpu_time] ,r.[total_elapsed_time] ,r.[reads] ,r.[writes] ,r.[logical_reads] ,r.[row_count] ,q.[text] ,q.[dbid] ,p.[query_plan] ,r.[plan_handle] FROM [sys].[dm_os_waiting_tasks] w INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id] INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id] CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p WHERE w.[session_id] > 50 AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT' ,'ASYNC_NETWORK_IO')) SELECT b.[session_id] AS [Waiti…

SQL Currently Running Sessions

Get all current sessions running on sql instance. SELECT r.session_id, r.status, r.start_time, r.command, s.text, r.wait_time, r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level ,r.* FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

SQL - Get Connected Hosts

Gets a list of all the connections currently open on SQL SELECT HOST_NAME, PROGRAM_NAME, wait_resource, wait_time, wait_type, net_transport, DMER.session_id, start_time, DMES.status, plan_handle, auth_scheme, local_tcp_port, local_net_address FROM sys.dm_exec_requests DMER INNER JOIN sys.dm_exec_sessions DMES ON DMES.session_id=DMER.session_id INNER JOIN sys.dm_exec_connections DMEC ON DMEC.session_id=DMES.session_id