Description:
Example to show the active users in a SQL Server Database,
Check the example below:
------------------------------------------------------------------------------------------------------
select ('kill '+ cast(spid as varchar(10))) as [kill proces],
sql_handle as [sql handle],
convert(varchar(8), dateadd(second, datediff(ss, a.last_batch, getdate()), 0), 108) as [duration last_batsch],
last_batch as [start_time last_batch],
left(cmd, 12) as [command],
left(loginame,20) as [login name],
cpu as cpu,
physical_io as [physical_io],
memusage as [memory usage],
blocked as [blocked process],
b.wait_type as [wait type],
b.resource_description as [resource description],
spid as spid,
kpid as kpid,
login_time as [login time],
getdate() as [current time]
from master..sysprocesses a inner join sys.dm_os_waiting_tasks b on a.spid = b.session_id
where loginame not in ('sa') and cmd <> 'awaiting command'
order by last_batch desc
/*
Active Users in Database (Waiting on ....)
*/
select ('kill '+ cast(spid as varchar(10))) as [kill proces],
sql_handle as [sql handle],
convert(varchar(8), dateadd(second, datediff(ss, a.last_batch, getdate()), 0), 108) as [duration last_batsch],
last_batch as [start_time last_batch],
left(cmd, 12) as [command],
left(loginame,20) as [login name],
cpu as cpu,
physical_io as [physical_io],
memusage as [memory usage],
blocked as [blocked process],
b.wait_type as [wait type],
b.resource_description as [resource description],
spid as spid,
kpid as kpid,
login_time as [login time],
getdate() as [current time]
from master..sysprocesses a inner join sys.dm_os_waiting_tasks b on a.spid = b.session_id
where loginame not in ('sa') and wait_type = 'pageiolatch_sh' -- waiting for some page to be brought into memory (from database files on disk).
and spid <> @@spid
order by [duration last_batsch] asc
/*
Active Sessions in Database (Currently execute on ....)
*/
select d.name as [dbname],
'kill ' + convert (varchar(10), a.session_id) as [kill proces],
a.sql_handle as [sql handle],
convert(varchar(8), dateadd(second, datediff(ss, a.start_time, getdate()), 0), 108) as [duration starttime],
a.start_time as [start_time last_batch],
--datediff(ss, a.start_time, getdate()) as [seconds],
--a.total_elapsed_time / 1000.0 as [elapsedtime],
a.command as [command],
a.blocking_session_id as [blocked session id],
m.dop as [degree of parallelism],
b.text as [sql text],
substring(b.text, a.statement_start_offset / 2, case when (a.statement_end_offset - a.statement_start_offset) / 2 > 0 then (a.statement_end_offset - a.statement_start_offset) / 2 else 1 end ) as [stmt],
a.cpu_time as [cpu],
a.reads as [reads],
a.writes as [writes],
a.logical_reads as [logical reads],
a.wait_type as [waittype],
a.wait_time as [waittime],
a.last_wait_type as [last waittype],
a.wait_resource as [waitresource],
m.requested_memory_kb as [requested memory],
a.granted_query_memory as [granted query memory],
m.max_used_memory_kb as [max used memory]
from sys.dm_exec_requests a with (nolock) outer apply sys.dm_exec_sql_text(a.sql_handle) b left join sys.dm_exec_query_memory_grants m (nolock) on m.session_id = a.session_id
and m.request_id = a.request_id join sys.databases d on d.database_id = a.database_id
where a.session_id > 50
and a.session_id <> @@spid
order by [duration starttime] asc
/*
Dubbele users ..
*/
declare @db varchar(25)
set @db = '___'
select getdate()
, loginame
, spid
, login_time
, last_batch
, cmd
, cpu
, physical_io
from master..sysprocesses
where loginame in (select loginame
from master..sysprocesses
where dbid = (select dbid
from master..sysdatabases
where name = @db)
and loginame like '%.%'
group by loginame having count(*) > 1
)
--AND cmd <> 'AWAITING COMMAND'
--ORDER BY loginame, last_batch
/*
SQL_HANDLE
*/
declare @spid varchar(25)
set @spid = '___'
select [text] from ::fn_get_sql((select sql_handle
from master..sysprocesses
where spid = @spid
));
GO
/*
Show dubbele queries
*/
select sql_handle
, count(*) as count
from master..sysprocesses inner join sys.dm_os_waiting_tasks on master..sysprocesses.spid = sys.dm_os_waiting_tasks.session_id
where loginame not in ('sa') and wait_type = 'pageiolatch_sh' --and cmd <> 'awaiting command'
group by sql_handle
order by count desc
/*
PLAN_HANDLE (show sqlplan for a session_id)
*/
declare @id varchar(25)
set @id = '___'
select query_plan, *
from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_query_plan(cp.plan_handle)
where plan_handle = (select plan_handle
from sys.dm_exec_requests
where session_id = @id
);