June 6, 2008 at 4:22 am
Hi,
how can we see long running queries with t-sql .
Thaxx
Regards
Jagpal Singh
June 6, 2008 at 5:14 am
Off course the free MS "performance dashboard" is a good way of showing it.
and this may also be a good starting point.....
-- lists the top 50 statements by input/output usage. This script requires Microsoft SQL Server 2005.
-- http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg_IO]
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset
end - qs.statement_start_offset)/2) as query_text,
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,
qs.sql_handle,
qs.plan_handle,
qs.creation_time
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Avg_IO] DESC;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 6, 2008 at 5:22 am
Thaxx,
ALZDBA for this script but it doesnt work with sql 2000
Regards
Jagpal singh
June 6, 2008 at 5:32 am
Ah, but you posted in a SQL2005 forum ... hence my reply.
for sql2000 I use this:
- sp_who2
- and the consumers :
if (object_id('tempdb..#tmpVerbruik') is null)
begin
-- tmptabelleke aanmaken
select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'mailsweeper'
group by A.hostname, A.loginame, A.program_name
end
select A1.*
--, T.min_login_time
--, T.max_last_batch
--, T.sum_cpu
--, T.sum_physical_io
--, T.sum_memusage
, A1.sum_cpu - T.sum_cpu as Delta_cpu
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
from
(select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
-- into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'mailsweeper'
group by A.hostname, A.loginame, A.program_name
) A1
left join #tmpVerbruik T
on A1.hostname = T.hostname
and A1.loginame = T.loginame
and A1.program_name = T.program_name
where T.sum_cpu <> A1.sum_cpu
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
order by Delta_CPU desc, A1.hostname, A1.loginame, A1.program_name
or the modified sp_who2
declare @LoginName sysname
,@HostName sysname
,@DbName sysname --= NULL
,@ProgramName sysname --= NULL
select @LoginName=null, @HostName=null, @DbName=null , @ProgramName=null
-- jobi copy van SP_WHO2 met aanpassingen :
-- aktieve ORDER By-clause (geblokkeerde staan van boven)
-- meer info over blokkerende partij
-- versie 29/02/2002
set nocount on
Print '** hulpje kan zijn : sp_alz_lockinfo @option=2,@spid= '
print '** of DBCC OPENTRAN **'
print '** of DBCC INPUTBUFFER (spid) **'
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@Dbidlow int
,@Dbidhigh int
,@Dbid1 int
,@Dbidilow int
,@Dbidihigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
--------
select
@retcode = 0 -- 0=good ,1=bad.
--------defaults
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@LoginName IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17LoginNameEdited
--------
-- deels uitgeschakeld om toegang via nt-groepen aan te kunnen
--------
IF (patindex ('%[^0-9]%' , isnull(@LoginName,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @LoginName)
,@spidhigh = convert(int, @LoginName)
GOTO LABEL_17LoginNameEdited
end
LABEL_17LoginNameEdited:
-- jobi
--------------------------------------------------------------
IF (@HostName IS NULL) --Simple default to all HostNames.
GOTO LABEL_18HostNameEdited
-- geen verder check mogelijk
LABEL_18HostNameEdited:
--------------------------------------------------------------
IF (@DbName IS NULL) --Simple default to all DbNames.
GOTO LABEL_19DbNameEdited
--------
select @Dbid1 = null
-- if exists(select 1 from master.dbo.sysdatabases where name = @DbName)
select @Dbid1 = dbid from master.dbo.sysdatabases where name = @DbName
IF (@Dbid1 IS NOT NULL) --Parm is a recognized DbName.
begin
select @Dbidlow = @Dbid1
,@Dbidhigh = @Dbid1
GOTO LABEL_19DbNameEdited
end
--------
IF (patindex ('%[^0-9]%' , isnull(@DbName,'z')) = 0) --Is a number.
begin
select @Dbidlow = convert(int, @DbName)
,@Dbidhigh = convert(int, @DbName)
GOTO LABEL_19DbNameEdited
end
--------
RaisError(15010,-1,-1,@DbName)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_19DbNameEdited:
--------------------------------------------------------------
IF (@ProgramName IS NULL) --Simple default to all HostNames.
GOTO LABEL_20ProgramNameEdited
-- geen verder check mogelijk
LABEL_20ProgramNameEdited:
-- jobi
-------------------- Capture consistent sysprocesses. -------------------
SELECT
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
INTO #TbALZ_sysprocesses
from master.dbo.sysprocesses (nolock)
--------Screen out any rows?
IF (@LoginName IN ('active'))
DELETE #TbALZ_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
and blocked = 0
-- filter loginnames
IF (@LoginName is not null)
DELETE #TbALZ_sysprocesses
where spid = @spidlow
IF (@LoginName is not null)
DELETE #TbALZ_sysprocesses
where loginname <> @LoginName
-- filter HostName
IF (@HostName IS not NULL)
DELETE #TbALZ_sysprocesses
where hostname <> @HostName
-- filter DbNames
IF (@DbName is not null)
DELETE #TbALZ_sysprocesses
where dbid <> @Dbid1
-- @ProgramName
-- filter ProgramName
IF (@ProgramName is not null)
DELETE #TbALZ_sysprocesses
where program_name <> @ProgramName
--------Prepare to dynamically optimize column widths.
Select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
SELECT
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( convert(varchar,db_name(dbid)))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( convert(varchar,cpu))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( convert(varchar,physical_io))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( convert(varchar,cmd))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( convert(varchar,hostname))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( convert(varchar,program_name))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( convert(varchar,last_batch_char))) ,9)
)
from
#TbALZ_sysprocesses
-- where
-- sid >= @sidlow
-- and sid <= @sidhigh
-- and
-- spid >= @spidlow
-- and spid <= @spidhigh
--------Output the report.
EXECUTE(
'
SET nocount off
SELECT
SPID = a.spid
,Blocked =
CASE a.blocked
When 0 Then ''.''
Else ''X''
END
,LastBatch = substring(a.last_batch_char,1,' + @charMaxLenLastBatch + ')
,Login = substring(a.loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE a.hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(a.hostname,1,' + @charMaxLenHostName + ')
END
,DBName = substring(db_name(a.dbid),1,' + @charMaxLenDBName + ')
,Command = substring(a.cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,a.cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,a.physical_io),1,' + @charMaxLenDiskIO + ')
,ProgramName = substring(a.program_name,1,' + @charMaxLenProgramName + ')
,Status =
CASE lower(a.status)
When ''sleeping'' Then lower(a.status)
Else upper(a.status)
END
,SPID = a.spid --Handy extra for right-scrolling users.
,BlkBy_SPID = a.blocked
--CASE a.blocked
-- When 0 Then NULL
-- Else a.blocked
--END
,BlkBy_Login = substring(b.loginname,1,' + @charMaxLenLoginName + ')
,BlkBy_HostName =
CASE b.hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(b.hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy_ProgramName = substring(b.program_name,1,' + @charMaxLenProgramName + ')
from
#TbALZ_sysprocesses a --Usually DB qualification is needed in exec().
left join
#TbALZ_sysprocesses b
on a.blocked = b.spid
order by Blocked desc, a.last_batch_char desc, a.blocked, a.spid_sort
SET nocount on
'
)
LABEL_86RETURN:
if (object_id('tempdb..#TbALZ_sysprocesses') is not null)
drop table #TbALZ_sysprocesses
set nocount off
--return @retcode -- sp_ALZ_WhoBlocks
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 6, 2008 at 5:39 am
Hi,
Thaxx but this query not showing long running queries its just showing how much time it taken i need long ruuning queries.
Regards
Jagpal singh
June 6, 2008 at 6:18 am
Maybe you need to better define what you're looking for. ALZ's solution shows procedures that are running and how long they are taking. Determining whether or not a query is "long running" is subjective. A 30 second query can be quick on one system and completely killing performance on the next.
Personally, we capture performance metrics using Profiler and Operations Manager 2007.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply