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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply