Today I am going to discuss about how to find opentransactions , blockings , headblocker , runtime of a query, Long Running queries , cpu usage of a particular database or for all databases..etc., in a current situation
In a simple way we can call it as Activity Monitor Script which can show us current situation of SQL Instance :
I am sharing this as it helps me a lot in so many situations and Hope it will help you also .
First we need to create views in master :
USE [master]
GO
/****** Object: View [dbo].[all_task_usage] Script Date: 12/21/2016 6:14:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--display sessions with unfreed objects/pages
CREATE VIEW [dbo].[all_task_usage]
AS
SELECT session_id,
SUM(user_objects_alloc_page_count) AS task_user_objects_alloc_page_count,
SUM(user_objects_dealloc_page_count) AS task_user_objects_dealloc_page_count,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;
GO
USE [master]
GO
/****** Object: View [dbo].[all_session_usage] Script Date: 12/21/2016 6:15:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_session_usage]
AS
SELECT R1.session_id,
R1.internal_objects_alloc_page_count
+ R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count
+ R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
,R1.user_objects_alloc_page_count
+ R2.task_user_objects_alloc_page_count AS session_user_objects_alloc_page_count,
R1.user_objects_dealloc_page_count
+ R2.task_user_objects_dealloc_page_count AS session_user_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO
USE [master]
GO
/****** Object: View [dbo].[all_request_usage] Script Date: 12/21/2016 6:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_request_usage]
AS
SELECT session_id, request_id,
SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id;
GO
USE [master]
GO
/****** Object: View [dbo].[all_query_usage] Script Date: 12/21/2016 6:16:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[all_query_usage]
AS
SELECT R1.session_id, R1.request_id,
R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
FROM all_request_usage R1
INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO
After creating above views we need to create a Stored Procedure called getactivity :
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_getactivity] Script Date: 11/06/2014 00:31:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_getactivity](
@login varchar(130)='%',
@dbname varchar(130)='%',
@tsql varchar(130)='%',
@spidgtr int=0,
@showalltext int=-1,
@groupbysession bit=1,
@lightactive bit=0,
@default bit=1
)
as
set nocount on
set @dbname=lower(@dbname)
set @login=lower(@login)
print '--show default activity monitor and text for what is running:'
print 'exec master..sp_getactivity'
print '@dbname=''%'''
print ',@login=''%'''
print ',@default=1'
print ''
print '--show active tasks in detail:'
print 'exec master..sp_getactivity'
print ',@default=0'
print ''
print '--show default activity monitor and text for what is running in master db:'
print 'exec master..sp_getactivity'
print '@dbname=''master'''
print ',@login=''%'''
print ',@default=1'
print ''
print '--show default activity monitor and text for what is running under sa login:'
print 'exec master..sp_getactivity'
print '@dbname=''%'''
print ',@login=''sa'''
print ',@default=1'
print ''
if @default=1
begin
----------------------------------------------------------------------
--original act mon:
------------------------------------------------------------------
if object_id('tempdb.dbo.#defaultactmonitor') is not null
drop table #defaultactmonitor
SELECT
[killsid] = 'kill '+convert(varchar,s.session_id),
[User Process] = CONVERT(CHAR(1), s.is_user_process),
[login] = s.login_name,
[database] = ISNULL(db_name(p.dbid), N''),
[Task State] = ISNULL(t.task_state, N''),
[Command] = ISNULL(r.command, N''),
[Application] = ISNULL(s.program_name, N''),
[tsql]=convert(varchar(max),ltrim(replace(replace(replace(replace(REPLACE(REPLACE(replace(replace(replace(isnull((select text from sys.dm_exec_sql_text(p.sql_handle)),''),CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '),'*',''),'----','-'),'==',''))),
[Wait Time (ms)] = ISNULL(w.wait_duration_ms, 0),
[Wait Type] = ISNULL(w.wait_type, N''),
[Wait Resource] = ISNULL(w.resource_description, N''),
[Blocked By] = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
[Head Blocker] =
CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
[Total CPU (ms)] = s.cpu_time,
[Total Physical I/O (MB)] = (s.reads + s.writes) * 8 / 1024,
[Memory Use (KB)] = s.memory_usage * 8192 / 1024,
[Open Transactions] = ISNULL(r.open_transaction_count,0),
[Login Time] = s.login_time,
[Last Request Start Time] = s.last_request_start_time,
[Host Name] = ISNULL(s.host_name, N''),
[Net Address] = ISNULL(c.client_net_address, N''),
[Execution Context ID] = ISNULL(t.exec_context_id, 0),
[Request ID] = ISNULL(r.request_id, 0),
[Workload Group] = N'',
[sid] = convert(varchar,s.session_id)
into #defaultactmonitor
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
-- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
-- waiting for several different threads. This will cause that thread to show up in multiple rows
-- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,
-- and use it as representative of the other wait relationships this thread is involved in.
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
where s.session_id>@spidgtr
and lower(convert(varchar(130),s.login_name)) like @login
and lower(convert(varchar(130),ISNULL(db_name(p.dbid), N''))) like @dbname
ORDER BY s.session_id desc
select * from #defaultactmonitor
where [sid]>@spidgtr
and lower(convert(varchar(130),[login])) like @login
and lower(convert(varchar(130),[database])) like @dbname
and lower(convert(varchar(130),[tsql])) like @tsql
ORDER BY [sid] desc
--lightweight:
select 'lightweight',db_name(database_id) 'dbname',session_id,command,status,wait_type,cpu_time,reads,writes,logical_reads,
sqltext.text 'batchtext'
from sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
where req.session_id>@spidgtr and (req.session_id<>@@spid)
order by req.session_id desc
--lightweight.
end
else
--------------if @ligthactive=0:
begin
print 'Info: @login and @dbname ignored when @default=0'
--end deletesqlobjects.sql
--declare @showalltext int,@groupbysession bit
--select @retval = instid from instance where guid = @guid
--set statistics profile off
--set @groupbysession=0 -- 0=show all tasks per spid
--set @groupbysession=1 -- 1=show 1 line per spid
--set @showalltext=-1 -- -1=current txt
--set @showalltext=0 -- 0=curr,input_buffer
--set @showalltext=1 -- 1=curr,input_buffer,offset,next
--set @showalltext=2 -- 2=curr,offset,next,prev ,input_buffer
--set @showalltext=3 -- 3=curr,offset,next,prev,fullbatch,input_buffer
--set @showalltext=4 -- 4=everything
--SELECT * FROM sys.dm_os_schedulers
--select * from sysprocesses where spid=135
--select * from sys.dm_exec_requests req where session_id>50
--if @showalltext is null set @showalltext=0
--if @groupbysession is null set @groupbysession=1
--note:the max degree of parallelism is per STEP in the query, NOT per query.
--So a given query could have many more threads than the MAXDOP setting.
--MAXDOP setting is used to limit the number of threads per operation in the execution plan (e.g, scan,seek) and
--does not limit the number of threads used to execute the query.
--So it is normal to see threads per SPID in sysprocesses greater than MAXDOP setting
if object_id('tempdb.dbo.#tempactivitymonitor') is not null
drop table #tempactivitymonitor
if object_id('tempdb.dbo.#tmpDBCCinputbuffer') is not null
drop table #tmpDBCCinputbuffer
SELECT
top 100
IDENTITY(int,1,1) 'id',
convert(varchar(max),'') inbuff,
sqltext.text 'batchtext',
qpx.query_plan 'queryplan',
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, 0,(case
when req.statement_end_offset=0
then charindex(left(req.command,charindex(' ',req.command+' ')),sqltext.text)
else (req.statement_start_offset/2) + 1 end)))
else 'encrypted' end as [prev],
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, (case
when req.statement_end_offset=0
then charindex(left(req.command,charindex(' ',req.command+' ')),sqltext.text)
else (req.statement_start_offset/2) + 1 end),
((CASE --req.statement_end_offset
WHEN req.statement_end_offset < 1 THEN DATALENGTH(sqltext.text)
ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1))
else 'encrypted' end as [current],
case when sqltext.encrypted=0 then
ltrim(SUBSTRING(sqltext.text, (CASE
WHEN req.statement_end_offset < 1 THEN DATALENGTH(sqltext.text)
ELSE req.statement_end_offset END/2+1)
,DATALENGTH(sqltext.text)/2+1))
else 'encrypted' end as [next],
convert(varchar(50),'DBCC OUTPUTBUFFER('+convert(varchar,req.session_id)+')') outbuffcmd,
sqltext.encrypted 'encrypted',
case when sqltext.encrypted=0 then
convert(varchar,req.statement_start_offset/2)+'-'+
convert(varchar,(case when req.statement_end_offset<1
then DATALENGTH(sqltext.text)/2 else req.statement_end_offset/2 end))+
case when req.statement_end_offset>0 --if > 0 then there is more in batch
then ' '+convert(varchar,DATALENGTH(sqltext.text)/2) else '' end else 'encrypted' end 'offset',
'kill '+convert(varchar,req.session_id) 'sessid',
hdblkr=CASE
-- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
WHEN r2.session_id IS NOT NULL AND (req.blocking_session_id = 0 OR req.session_id IS NULL) THEN '1'
-- session is either not blocking someone, or is blocking someone but is blocked by another party
ELSE ''
END,
convert(varchar,req.session_id) 'spid',
req.request_id 'reqid',
req.status 'status', --s.status,
req.command 'command',
--req.cpu_time 'cpu_ms', --commented this because next one totals all from multiple processes and same session:
--(select sum(convert(bigint,p.cpu_time))/1000 from sys.dm_exec_sessions p where (req.session_id = p.session_id)) 'cpu_ss',
(select sum(convert(bigint,p.cpu))/1000 from sys.sysprocesses p where (req.session_id = p.spid)) 'cpu_ss',
task_internal_objects_alloc_page_count-task_internal_objects_dealloc_page_count 'tskipgs',
session_internal_objects_alloc_page_count-session_internal_objects_dealloc_page_count 'sesipgs',
task_user_objects_alloc_page_count-task_user_objects_dealloc_page_count 'tskupgs',
session_user_objects_alloc_page_count-session_user_objects_dealloc_page_count 'sesupgs',
(task_internal_objects_alloc_page_count+task_user_objects_alloc_page_count)-(task_internal_objects_dealloc_page_count+task_user_objects_dealloc_page_count) 'tskpgs',
(session_internal_objects_alloc_page_count+session_user_objects_alloc_page_count)-(session_internal_objects_dealloc_page_count+session_user_objects_dealloc_page_count) 'sespgs',
ru.request_internal_objects_alloc_page_count-ru.request_internal_objects_dealloc_page_count 'reqpgs',
qu.request_internal_objects_alloc_page_count-qu.request_internal_objects_dealloc_page_count 'qpgs',
req.granted_query_memory 'mpgs',
req.row_count 'rows',
(select count(*) from sys.sysprocesses p where (req.session_id = p.spid)) 'thds', --threads
CAST(((DATEDIFF(s,req.start_time,GetDate()))/3600) as varchar) + 'h'
+ CAST((DATEDIFF(s,req.start_time,GetDate())%3600)/60 as varchar) + 'm'
+ CAST((DATEDIFF(s,req.start_time,GetDate())%60) as varchar) + 's' as run_time,
--case
--when req.total_elapsed_time<(1000*60*2) then convert(varchar,req.total_elapsed_time/1000)+'s'
--else convert(varchar,convert(decimal(10,1),req.total_elapsed_time/1000.0/60.0))+'m'
--end [elaps],
--req.total_elapsed_time/1000 'elaps_ss',-- commented this for elaps_mi cuz it's smaller value:
--convert(decimal(10,2),req.total_elapsed_time/1000.0/60.0) 'elaps_mi', Sie_018810
--next is showing more physical io count that the Wr Re below..so added:
(select sum(convert(bigint,p.physical_io)) from sys.sysprocesses p where (req.session_id = p.spid)) 'physio',
db_name(req.database_id) 'db',
case when len(s.login_name)>0 then s.login_name else s.original_login_name end as 'login',
s.host_name 'host_name',
s.writes 'Wr',
s.reads 'Re',
s.logical_reads 'logical Re',
req.prev_error 'error',
(req.wait_time/1000) 'wait_ss',
(wait_duration_ms/1000) 'wait_dur_task_ss',
wt.wait_type 'wait_type',
exec_context_id 'exec_context_id',
blocking_exec_context_id 'blocking_exec_context_id',
case
when wt.wait_type like 'PAGE%LATCH[_]%' and (wt.resource_description like '2:%:1' or wt.resource_description like '2:%:2' or wt.resource_description like '2:%:3') then 'tempdb:fileid:page contention in the allocation structures that track allocation information: 1=PFS, 2=GAM, 3=SGAM'
when wt.wait_type like 'PAGE%LATCH[_]%' and (wt.resource_description like '2:%:%') then 'tempdb:fileid:page'
when wt.wait_type like 'PAGE%' then 'exec ['+db_name(req.database_id)+']..[sp_pagetoobject] '''+case when len(resource_description)>0 then resource_description else req.wait_resource end+''''
when wt.wait_type='ASYNC_NETWORK_IO' then 'client disconnect or huge select resultset?'
when wt.wait_type='CXPACKET' then 'MAXDOP too high?'
when wt.wait_type='SOS_SCHEDULER_YIELD' then 'voluntarily yielded'
when wt.wait_type='OLEDB' or req.wait_resource like '%(SPID=%)' then 'using linked server?'
when req.wait_resource like '%:%:%' then 'exec ['+db_name(req.database_id)+']..[sp_pagetoobject] '''+replace(replace(req.wait_resource,' ',''),'PAGE:','')+''''
else ''
end [misc notes],
req.wait_resource 'wait_resource',
wt.resource_description 'resource_description',
req.last_wait_type 'last_wait_type',
case when req.blocking_session_id<>0 then 'kill '+convert(varchar,req.blocking_session_id)
else convert(varchar,req.blocking_session_id) end 'blocker',
req.executing_managed_code 'exe',
req.open_resultset_count 'opresultset',
req.open_transaction_count 'optrn',
req.percent_complete 'percComp',
CAST(((DATEDIFF(s,req.start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,req.start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,req.start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((req.estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((req.estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((req.estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
convert(smalldatetime,dateadd(second,req.estimated_completion_time/1000, getdate())) as est_completion_time
,s.client_interface_name 'cli'
into #tempactivitymonitor
FROM sys.dm_exec_requests req
left join sys.dm_exec_sessions s ON (s.session_id = req.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
left join all_task_usage tu ON (tu.session_id = req.session_id)
left join all_session_usage su ON (su.session_id = req.session_id)
left join all_request_usage ru ON (ru.session_id = req.session_id and req.request_id=ru.request_id)
left join all_query_usage qu ON (qu.session_id = req.session_id and req.request_id=qu.request_id)
left join sys.dm_os_waiting_tasks wt on (req.session_id=wt.session_id)
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) as qpx
where (req.session_id<>@@spid and req.session_id>@spidgtr)
order by db_name(req.database_id), req.session_id
-----------------------
if @groupbysession=1
delete from #tempactivitymonitor WHERE isnull(exec_context_id,0)<>0
----------
create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(max))
declare @session_id int,@reqid int
declare c cursor for select distinct spid,reqid from #tempactivitymonitor;
open c
fetch next from c into @session_id,@reqid
while @@FETCH_STATUS = 0
begin
begin try
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER('+@session_id+','+@reqid+') WITH NO_INFOMSGS')
update #tempactivitymonitor
set inbuff=(select ltrim([Event Info]) from #tmpDBCCinputbuffer)
where spid=@session_id and reqid=@reqid
end try
begin catch
--
exec usp_geterrorinfo
end catch
truncate table #tmpDBCCinputbuffer
fetch next from c into @session_id,@reqid
end
close c
deallocate c
--remove more than one space, and CRLF and tab to space:
update #tempactivitymonitor
set batchtext=ltrim(replace(replace(replace(replace(replace(replace(batchtext,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set inbuff=ltrim(replace(replace(replace(replace(replace(replace(inbuff,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set prev=ltrim(replace(replace(replace(replace(replace(replace(prev,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set [current]=ltrim(replace(replace(replace(replace(replace(replace([current],CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
update #tempactivitymonitor
set next=ltrim(replace(replace(replace(replace(replace(replace(next,CHAR(10),' '),CHAR(13),' '),char(9),' '),' ','<>'),'><',''),'<>',' '))
if @showalltext=-1
select
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
sessid,status,blocker,hdblkr,command,cpu_ss,sespgs,--tskipgs,sesipgs,tskupgs,sesupgs,sespgs,tskpgs,reqpgs,
qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,wait_ss,wait_type,resource_description,[misc notes],wait_resource,last_wait_type,exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,
cli,spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
------------------------------------
if @showalltext=0
select
case
when len(rtrim(prev))<1
then inbuff
else prev end 'prev/inbuff',
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
sessid,status,blocker,hdblkr,command,cpu_ss,sespgs,--tskipgs,sesipgs,tskupgs,sesupgs,sespgs,tskpgs,
--reqpgs,
qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,
cli,spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
----
----------------------
if @showalltext=1
select
case
when len(rtrim(prev))<1
then inbuff
else prev end 'prev/inbuff',
case
when encrypted=1
then inbuff
when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss,--tskipgs,sesipgs,tskupgs,sesupgs
sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,
Wr,Re,[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,
exe,opresultset,optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
-----
if @showalltext=2
select
inbuff,
[prev],
case when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss--tskipgs,sesipgs,tskupgs,sesupgs
,sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,[logical Re],error,
wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,optrn,percComp,
running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
---
if @showalltext=3
select inbuff,batchtext,
[prev],
case when len(rtrim([current]))<1
then prev
else [current] end 'current',
[next],offset,sessid,status,blocker,hdblkr,command,cpu_ss--tskipgs,sesipgs,tskupgs,sesupgs
,sespgs,tskpgs,reqpgs,qpgs,mpgs,rows,thds,run_time,physio,db,login,host_name,Wr,Re,
[logical Re],error,wait_ss,wait_type,[misc notes],wait_resource,resource_description,last_wait_type,exe,opresultset,
optrn,percComp,running_time,est_time_to_go,est_completion_time,queryplan,cli,
spid,reqid,exec_context_id from #tempactivitymonitor
order by db,spid
---
if @showalltext=4
select * from #tempactivitymonitor
order by db,spid
Declare @sqlversion char(1),@cmd varchar(2000)
Select @sqlversion=convert(char(1),convert(varchar(10),@@microsoftVersion/16))
if @sqlversion<>8 and (isnull((select count(*) from #tempactivitymonitor where blocker like 'kill%'),0)>0)
begin
set @cmd='declare @date varchar(50),@i int;set @i=0
while (@i<1)
begin
set @date=convert(varchar(50),convert(varchar,cast(getdate() as smalldatetime),102)+'' ''+convert(varchar,cast(getdate() as datetime),108))
SELECT convert(varchar(100),db_name(sp.dbid))
,convert(varchar(10),sp.spid) blockerSPID
,convert(varchar(50),sp.status) blockerStatus
,convert(varchar(50),sp.cmd) blockerCmd
,convert(varchar(100),sp.[program_name]) blockerPgmnm
,convert(varchar(50),sp.loginame) blockerLogin
,convert(varchar(50),sp.hostname) blockerHost
,convert(varchar(50),sp.physical_io) physio
,convert(varchar(50),convert(varchar,cast(sp.login_time as smalldatetime),102)+'' ''+convert(varchar,cast(sp.login_time as datetime),108)) logintime
,convert(varchar(50),@date) reportdate
,convert(varchar(50),sp.lastwaittype) waittyp
,convert(varchar(50),sp.waitresource) waitres
,convert(varchar(50),convert(varchar,cast(sp.last_batch as smalldatetime),102)+'' ''+convert(varchar,cast(sp.last_batch as datetime),108)) ''lastbatch''
,convert(varchar(50),sp.memusage) ''memusage''
,convert(varchar(10),sp.open_tran) ''opentran''
,convert(varchar(1000),ltrim(replace(replace(replace(replace(REPLACE(REPLACE(replace(replace(replace(isnull((select text from sys.dm_exec_sql_text(sp.sql_handle)),''''),CHAR(10),'' ''),CHAR(13),'' ''),char(9),'' ''),'' '',''<>''),''><'',''''),''<>'','' ''),''*'',''''),''----'',''-''),''=='',''''))) text
,convert(varchar(50),serverproperty(''SERVERNAME'')) servername
FROM master..sysprocesses sp
LEFT OUTER JOIN sys.dm_exec_requests r ON (sp.spid = r.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (sp.spid = r2.blocking_session_id)
where sp.spid > 0
and r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)
--cross apply sys.dm_exec_sql_text(sp.sql_handle) sqltext
set @i=@i+1
--waitfor delay ''00:00:01''
end'
end
exec(@cmd)
end --@lightactive=0
After creating above views and stored procedure execute below script as per your requirement :
use master --dbname and login : case insensitive:
exec master..sp_getactivity
@dbname='%%' -- Provide DB name to see what is going on for particular DB.If you leave it empty it will show for all DB's.
,@login='%%' -- provide the loginame to see what is going on with particular login.If you leave it empty it will show for all Logins.
,@default=0
,@spidgtr=50
,@showalltext=-1
,@groupbysession=1
go
Note : This script will be used like Activity Monitor and it will show only current situation of sql instance or database.
