MS SQL Server - Weird behaviour?

Author

Weird behaviour?

akashkurdekar@gmail.com

2006-11-29, 7:12 pm

Hi, I've got a seemingly unique situation. The query I have has a date
field (TrackTime). There are about 50k records, and the query execution
is very different for different time spans selected. Specifically, if I
search for records between 10-Nov-2006 00:00 and 28-Nov-2006 23:59, I
get 42k records and the query is executed in 20 seconds.
But, if I search for records between 28-Nov-2006 00:00 and 28-Nov-2006
23:59 (one day's worth), the query keeps running for more than 10
minutes, and if terminated, shows only 2 records.

Please can anyone tell me what's happening?

--------------------------------------------------------------------------------
Select t1.ActivityID,
t1.Body,
t1.tracktime,
t2.MsgFormat,
t2.MsgDirection,
t1.FlowID,
t2.SrcAppl,
t2.DestAppl,
t2.MsgType,
t2.UniqueID,
t2.MUR,
t2.Amount,
t2.Currency,
t2.Valuedate,
t2. TransactionReference
Number,
t2.SenderBIC,
t2.ReceiverBIC,
t2.MsgRcvdTime,
t2.MsgSentTime,
t2.RspRcvdTime,
t2.RspSentTime,
t2.IsPegaDiverted,
t2.PegaDivertedTime,
t2. PegaDivertedRspRcvdT
ime,
t2. SecondaryRspRcvdTime
,
t2.ACKStatus,
t2.Level1ServiceName,
t2.ACKTimestamp,
t2.ExceptionOrNAKCode,
t2.TRXMNumber,
t2.BusDealId,
t2.SourceType,
t2.ACKSourceId,
t2.ACK2Status
from dbo. bam_IntegratorBodyTr
acking_AllInstances t1 WITH (NOLOCK) ,
dbo. bam_IntegratorMessag
eFlow_AllInstances t2 WITH (NOLOCK)
where t2.SrcAppl NOT IN ('HOST MEMO','GMU IP','GMU OP')
and t2.DestAppl NOT IN ('DEAD LETTER','GMU TICS','GMU PAYMENT','GMU
MEMIR','GMU MEMBP') and
t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
<= convert(datetime,'28
nov 2006 23:59')
and t2.destappl is not null and t1.ActivityID = t2.ActivityID ORDER BY
trackTime
--------------------------------------------------------------------------------

Adi

2006-11-29, 7:12 pm

This does sound strange. Does it happen all the time or did you check
it once? If it happens all the time, then I would check the query plan
for both queries. There is a good chance that the query for the one
day is using an index and the query for the 18 days is doing a table
scan. In that case I would check the index with DBCC CHECKTABLE. I
admit that this is a shot in the dark, but this is the only thing that
I can think about.

Adi

akashkurdekar@gmail.com wrote:
> Hi, I've got a seemingly unique situation. The query I have has a date
> field (TrackTime). There are about 50k records, and the query execution
> is very different for different time spans selected. Specifically, if I
> search for records between 10-Nov-2006 00:00 and 28-Nov-2006 23:59, I
> get 42k records and the query is executed in 20 seconds.
> But, if I search for records between 28-Nov-2006 00:00 and 28-Nov-2006
> 23:59 (one day's worth), the query keeps running for more than 10
> minutes, and if terminated, shows only 2 records.
>
> Please can anyone tell me what's happening?
>
> --------------------------------------------------------------------------------
> Select t1.ActivityID,
> t1.Body,
> t1.tracktime,
> t2.MsgFormat,
> t2.MsgDirection,
> t1.FlowID,
> t2.SrcAppl,
> t2.DestAppl,
> t2.MsgType,
> t2.UniqueID,
> t2.MUR,
> t2.Amount,
> t2.Currency,
> t2.Valuedate,
> t2. TransactionReference
Number,
> t2.SenderBIC,
> t2.ReceiverBIC,
> t2.MsgRcvdTime,
> t2.MsgSentTime,
> t2.RspRcvdTime,
> t2.RspSentTime,
> t2.IsPegaDiverted,
> t2.PegaDivertedTime,
> t2. PegaDivertedRspRcvdT
ime,
> t2. SecondaryRspRcvdTime
,
> t2.ACKStatus,
> t2.Level1ServiceName,
> t2.ACKTimestamp,
> t2.ExceptionOrNAKCode,
> t2.TRXMNumber,
> t2.BusDealId,
> t2.SourceType,
> t2.ACKSourceId,
> t2.ACK2Status
> from dbo. bam_IntegratorBodyTr
acking_AllInstances t1 WITH (NOLOCK) ,
> dbo. bam_IntegratorMessag
eFlow_AllInstances t2 WITH (NOLOCK)
> where t2.SrcAppl NOT IN ('HOST MEMO','GMU IP','GMU OP')
> and t2.DestAppl NOT IN ('DEAD LETTER','GMU TICS','GMU PAYMENT','GMU
> MEMIR','GMU MEMBP') and
> t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
> <= convert(datetime,'28
nov 2006 23:59')
> and t2.destappl is not null and t1.ActivityID = t2.ActivityID ORDER BY
> trackTime
> --------------------------------------------------------------------------------

Tracy McKibben

2006-11-29, 7:13 pm

akashkurdekar@gmail.com wrote:
> Hi, I've got a seemingly unique situation. The query I have has a date
> field (TrackTime). There are about 50k records, and the query execution
> is very different for different time spans selected. Specifically, if I
> search for records between 10-Nov-2006 00:00 and 28-Nov-2006 23:59, I
> get 42k records and the query is executed in 20 seconds.
> But, if I search for records between 28-Nov-2006 00:00 and 28-Nov-2006
> 23:59 (one day's worth), the query keeps running for more than 10
> minutes, and if terminated, shows only 2 records.
>
> Please can anyone tell me what's happening?
>
> --------------------------------------------------------------------------------
> Select t1.ActivityID,
> t1.Body,
> t1.tracktime,
> t2.MsgFormat,
> t2.MsgDirection,
> t1.FlowID,
> t2.SrcAppl,
> t2.DestAppl,
> t2.MsgType,
> t2.UniqueID,
> t2.MUR,
> t2.Amount,
> t2.Currency,
> t2.Valuedate,
> t2. TransactionReference
Number,
> t2.SenderBIC,
> t2.ReceiverBIC,
> t2.MsgRcvdTime,
> t2.MsgSentTime,
> t2.RspRcvdTime,
> t2.RspSentTime,
> t2.IsPegaDiverted,
> t2.PegaDivertedTime,
> t2. PegaDivertedRspRcvdT
ime,
> t2. SecondaryRspRcvdTime
,
> t2.ACKStatus,
> t2.Level1ServiceName,
> t2.ACKTimestamp,
> t2.ExceptionOrNAKCode,
> t2.TRXMNumber,
> t2.BusDealId,
> t2.SourceType,
> t2.ACKSourceId,
> t2.ACK2Status
> from dbo. bam_IntegratorBodyTr
acking_AllInstances t1 WITH (NOLOCK) ,
> dbo. bam_IntegratorMessag
eFlow_AllInstances t2 WITH (NOLOCK)
> where t2.SrcAppl NOT IN ('HOST MEMO','GMU IP','GMU OP')
> and t2.DestAppl NOT IN ('DEAD LETTER','GMU TICS','GMU PAYMENT','GMU
> MEMIR','GMU MEMBP') and
> t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
> <= convert(datetime,'28
nov 2006 23:59')
> and t2.destappl is not null and t1.ActivityID = t2.ActivityID ORDER BY
> trackTime
> --------------------------------------------------------------------------------
>

Run your second query, wait for it to "hang", then check sysprocesses to
see if something is blocking it. Also compare the execution plans of
the two queries, looking for obvious differences.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Adi

2006-11-30, 12:12 am

I don't think that this is due to blocking because he is using the
nolock hint for all tables in the query.

Adi

Tracy McKibben wrote:
> akashkurdekar@gmail.com wrote:
>
> Run your second query, wait for it to "hang", then check sysprocesses to
> see if something is blocking it. Also compare the execution plans of
> the two queries, looking for obvious differences.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Uri Dimant

2006-11-30, 5:17 am

Hi
Have you look into an executiuon plan? BTW,what is the version are you
using? In SQL Server 2005 you can recompile on statement level.

What is happening if you change
t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
<= convert(datetime,dat
eadd(d,1,'28 nov 2006 00:00'))

<akashkurdekar@gmail.com> wrote in message
news:1164816180.774097.240110@80g2000cwy.googlegroups.com...
> Hi, I've got a seemingly unique situation. The query I have has a date
> field (TrackTime). There are about 50k records, and the query execution
> is very different for different time spans selected. Specifically, if I
> search for records between 10-Nov-2006 00:00 and 28-Nov-2006 23:59, I
> get 42k records and the query is executed in 20 seconds.
> But, if I search for records between 28-Nov-2006 00:00 and 28-Nov-2006
> 23:59 (one day's worth), the query keeps running for more than 10
> minutes, and if terminated, shows only 2 records.
>
> Please can anyone tell me what's happening?
>
> --------------------------------------------------------------------------------
> Select t1.ActivityID,
> t1.Body,
> t1.tracktime,
> t2.MsgFormat,
> t2.MsgDirection,
> t1.FlowID,
> t2.SrcAppl,
> t2.DestAppl,
> t2.MsgType,
> t2.UniqueID,
> t2.MUR,
> t2.Amount,
> t2.Currency,
> t2.Valuedate,
> t2. TransactionReference
Number,
> t2.SenderBIC,
> t2.ReceiverBIC,
> t2.MsgRcvdTime,
> t2.MsgSentTime,
> t2.RspRcvdTime,
> t2.RspSentTime,
> t2.IsPegaDiverted,
> t2.PegaDivertedTime,
> t2. PegaDivertedRspRcvdT
ime,
> t2. SecondaryRspRcvdTime
,
> t2.ACKStatus,
> t2.Level1ServiceName,
> t2.ACKTimestamp,
> t2.ExceptionOrNAKCode,
> t2.TRXMNumber,
> t2.BusDealId,
> t2.SourceType,
> t2.ACKSourceId,
> t2.ACK2Status
> from dbo. bam_IntegratorBodyTr
acking_AllInstances t1 WITH (NOLOCK) ,
> dbo. bam_IntegratorMessag
eFlow_AllInstances t2 WITH (NOLOCK)
> where t2.SrcAppl NOT IN ('HOST MEMO','GMU IP','GMU OP')
> and t2.DestAppl NOT IN ('DEAD LETTER','GMU TICS','GMU PAYMENT','GMU
> MEMIR','GMU MEMBP') and
> t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
> <= convert(datetime,'28
nov 2006 23:59')
> and t2.destappl is not null and t1.ActivityID = t2.ActivityID ORDER BY
> trackTime
> --------------------------------------------------------------------------------
>

F_clef

2006-11-30, 7:14 pm

Are there any indexes on these tables? If so, how often are they rebuilt?
Also, have you checked the execution plan to ensure they are being used?

"akashkurdekar@gmail.com" wrote:

> Hi, I've got a seemingly unique situation. The query I have has a date
> field (TrackTime). There are about 50k records, and the query execution
> is very different for different time spans selected. Specifically, if I
> search for records between 10-Nov-2006 00:00 and 28-Nov-2006 23:59, I
> get 42k records and the query is executed in 20 seconds.
> But, if I search for records between 28-Nov-2006 00:00 and 28-Nov-2006
> 23:59 (one day's worth), the query keeps running for more than 10
> minutes, and if terminated, shows only 2 records.
>
> Please can anyone tell me what's happening?
>
> --------------------------------------------------------------------------------
> Select t1.ActivityID,
> t1.Body,
> t1.tracktime,
> t2.MsgFormat,
> t2.MsgDirection,
> t1.FlowID,
> t2.SrcAppl,
> t2.DestAppl,
> t2.MsgType,
> t2.UniqueID,
> t2.MUR,
> t2.Amount,
> t2.Currency,
> t2.Valuedate,
> t2. TransactionReference
Number,
> t2.SenderBIC,
> t2.ReceiverBIC,
> t2.MsgRcvdTime,
> t2.MsgSentTime,
> t2.RspRcvdTime,
> t2.RspSentTime,
> t2.IsPegaDiverted,
> t2.PegaDivertedTime,
> t2. PegaDivertedRspRcvdT
ime,
> t2. SecondaryRspRcvdTime
,
> t2.ACKStatus,
> t2.Level1ServiceName,
> t2.ACKTimestamp,
> t2.ExceptionOrNAKCode,
> t2.TRXMNumber,
> t2.BusDealId,
> t2.SourceType,
> t2.ACKSourceId,
> t2.ACK2Status
> from dbo. bam_IntegratorBodyTr
acking_AllInstances t1 WITH (NOLOCK) ,
> dbo. bam_IntegratorMessag
eFlow_AllInstances t2 WITH (NOLOCK)
> where t2.SrcAppl NOT IN ('HOST MEMO','GMU IP','GMU OP')
> and t2.DestAppl NOT IN ('DEAD LETTER','GMU TICS','GMU PAYMENT','GMU
> MEMIR','GMU MEMBP') and
> t1.tracktime >= convert(datetime,'28
nov 2006 00:00') and t1.tracktime
> <= convert(datetime,'28
nov 2006 23:59')
> and t2.destappl is not null and t1.ActivityID = t2.ActivityID ORDER BY
> trackTime
> --------------------------------------------------------------------------------
>
>

Sponsored Links