Solved

Slow reaction time of tempdb in MSSQL2000 - URGENT!

Posted on 2004-09-30
18
497 Views
Last Modified: 2011-10-03
Hello all,

We have an MSSQL2K database which uses the tempdb for temporary query result-tables (which are created by a stored procedure, filled in with data, queried and then dumped).

We noticed that as the size of data increases (and we are not talking about hugh quantities, about a few thousands of records), the reaction time of the tempdb got slower and slower.

When testing the stored procedure, and breaking it into stages, we saw it took 8 seconds to do the CREATE of the temporary table, another 8 seconds to the the INSERT (of about 1000 records) and then another 8 seconds to do a select (which is a very simple select that returns the top 100 records of that table).

Upon further examination, we found out that the tempdb had grown to 32GB (!), out of which only 1.5MB were being used.
We shrunk the tempdb using the dbcc shrinkdatabase so it is now down to 8MB, we also restarted the DB itself, hoping that would resolve the problem.

Unfortunatley, even though the tempdb is now in normal size, the SP still takes 24 seconds to perform.

We tested doing exactly the same procedure but on ready-made empty tables on the regular DB, and then the insert took about 1 second and the select less than that, so obviously there is something very wrong with the tempdb.

We tested it on other MSSQL2K database machiens we have with more or less the same result, so this is not a one-time occurence but rather a behaviour of the tempdb.

Can anyone tell me how I can resolve this? What kind of settings do I need to do to make it respond in a reasonible amount of time? What could have caused a tempdb of a 100MB live database grow to 32GB? and how can I prevent it from happening again?

The top priority is to speed up the creation, inserting and selection from the tempdb. The SP itself is optimized and the problem is defintley the response time of the tempdb itself.

This is quite urgent as this is a query which works at the heart of our business' web system, which is now getting time-outs due to the long response time.

Any help would be appreciated, thank you in advance.
0
Comment
Question by:liranlotker
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 12197683
I'm not positive it's tempdb--post the code that you notice the problem with.

If you are having problems with tempdb, it would most likely be because of IO.  Make sure you have your tempdb datafile on different drives/raid groups than your tempdb logfile.  Also, hopefully it's not on RAID5 group.

Post your proc....
0
 
LVL 11

Expert Comment

by:ram2098
ID: 12197703
Did you test executing the stored procedure directly using query analyzer and check whether the problem is with TEMPDB? First, make sure there is no problem the way you are calling the procedure from your front-end code.

If you think there is a problem with tempdb, why don't you go for other options....

1. Create a Table variable rather than a temp tabe and store and retrieve the data from table variables.
2. If you want to access the data across lot of procedure, create a permanant table itself and store the data into it (since you are saying this is critical to the business, I guess it should fine adding another table to your database).

Thanks - Ram

0
 
LVL 34

Expert Comment

by:arbert
ID: 12197747
Definately post the proc.....We've done some large "funky" things with tempdb in the past without problems--show us what you're doing....
0
 

Author Comment

by:liranlotker
ID: 12197779
Thank you for the quick comments.
I tried to run it directly from the Query Analyzer with the same result, I noticed that it is always the interaction points with the tempdB that create the problem.

We are not using RAID5 on the drive that holds the tempDb. The logfile of the tempdb is in the same directory as the tempdb itself but is quite small (about 1.2MB)  and also, as I said we tried this on 2 other MSSQL2K DBs which sit on other physical machines and we got pretty much the same result, so it really feels like there are some configuration issues with the tempdb which screw up the connection to it.

Again, I can tell you for certain that the only problems with this SP is when it interacts with the tempDb (when creating the table, when inserting values into the table and when selecting values from the table). All other actions take milliseconds (we timed each action in the DB with a time-stamp to see exactly how much time each stage takes).

One more thing I noticed just now, as I said, we decreased the size of it to 8MB yesterday, but now I see that just a short while ago it all of the sudden jumped to 28MB in size. This, when the whole live DB of the system is about 200MB..

Here is the SP itself (warning, it is quite big) :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

ALTER       procedure Monitoring_searchStatistic
      @MonitoringNo            varchar(25)      
      ,@CustomerNo            varchar(20)      
      ,@CustomerRefA      varchar(20)      
      ,@CustomerRefB      varchar(20)      
      ,@SendingStationId      dt_id       = null      
      ,@ArrivalStationId      dt_id      = null
      ,@RailcarNo            varchar(20)       
      ,@RailcarRef            varchar(20)       
      ,@UserId            dt_UserId
      ,@DateFrom            dt_Date            = null            
      ,@DateTo            dt_Date            = null                  
      ,@Completed            dt_boolean
      ,@Status            char(1)
      ,@SpecialAttention      varchar (50)
      ,@DelayedOnly            dt_boolean  
as

        declare      @err                  int,
            @rowc                  int,
            @Counter            int,
            @SpControl             int

      declare @PrimCustID int

SET NOCOUNT ON

If       Len(@CustomerNo) > 1
     Begin
      select      @PrimCustID = CustomerID
            from      Customer
            where CustomerNo like @CustomerNo
    End


if @DateFrom is null
      Select @DateFrom = convert(datetime,'20000101',112)
if @DateFrom < convert(datetime,'20000101',112)
      Select @DateFrom = convert(datetime,'20000101',112)
if @DateTo is null
      Select @DateTo = convert(datetime,'22000101',112)
if @DateTo < convert(datetime,'20000101',112)
      Select @DateTo = convert(datetime,'22000101',112)

      if @SpecialAttention = 'Special Attention'
            Select  @SpControl = 0

      if @SpecialAttention != 'Special Attention'
            Select  @SpControl = -1

CREATE TABLE #tempsearch (Monitoringid  INT)
CREATE TABLE #tempsearch1 (Monitoringid  INT)


Select @Counter = 0


      if @Completed = 0
              goto ACTIVE
      else
      if @Completed = 1
              goto COMPLETED

ACTIVE:

IF Len(@CustomerNo) > 1
Begin
 if len(@MonitoringNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  

      where a.monitoringno like @MonitoringNo
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (a.CustomerID = @PrimCustID or a.SendingCustomerID = @PrimCustID or a.ArrivalCustomerID = @PrimCustID or a.ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl


      Select @Counter = @Counter + 1
    End

 if len(@CustomerRefA) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
 
      where CustomerRefA like @CustomerRefA
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl


      Select @Counter = @Counter + 1
    End


 if len(@CustomerRefB) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefB like @CustomerRefB
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where RailcarNo like @RailcarNo
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarRef) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where b.RailcarReference like @RailcarRef
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End
END
Else
Begin
 if len(@MonitoringNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where monitoringno like @MonitoringNo
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End

 if len(@CustomerRefA) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefA like @CustomerRefA
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@CustomerRefB) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefB like @CustomerRefB
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where RailcarNo like @RailcarNo
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarRef) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring_act a  Join Monitoringrow_act b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where b.RailcarReference like @RailcarRef
             and a.Completed = 0
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End
END


insert  into  #tempsearch1 (monitoringid)
select monitoringid from #tempsearch
      group by monitoringid
      having count(*) = @Counter


      Select      Distinct Top 101
            MON.MonitoringID,
            MON.MonitoringNo,
            MON.CustomerRefA,
            MON.CustomerRefB,
            SENDSTN.Name as SendingStationName,
            SENDSTN.StationNo as SendingStationNo,
            SENDSPUR.Name as SendingSpurName,
            MON.SendingDateTime,
            ARRSTN.StationNo as ArrivalStationNo,
            ARRSTN.Name as ArrivalStationName,
            ARRSPUR.Name as ArrivalSpurName,
            MON.ArrivalDateTime,
            MON.ETA,
            MON.ShuntingDays,
            MON.NoOfRailcars,
            MON.LastKnownDate,
            MON.Completed,
            SpecialAttention =
            case

            when convert(int,MON.SpecialAttention) + convert(int, MON.SpecialAttentionRailcar)>0
            then
                  1
            else
                  0
            end,
            MON.MaxETA,
            MON.MaxOrigETA,
            ArrivedDateTime = (Select
                              MAX(MORR.ArrivedDateTime)
                        from
                              MonitoringRow_act MORR
                        where  MON.MonitoringId = MORR.MonitoringId),
            MON.Delayed
      from
            Monitoring_act MON,
            Customer CUS,
            Station SENDSTN,
            Spur SENDSPUR,
            Station ARRSTN,
            Spur ARRSPUR,
            #tempsearch1 TEMPS
            where
            MON.MonitoringID =  TEMPS.MonitoringID
            AND MON.CustomerId = CUS.CustomerId
            and MON.SendingStationId = SENDSTN.StationId
            and MON.SendingSpurId *= SENDSPUR.SpurId
            and MON.ArrivalStationId = ARRSTN.StationId
            and MON.ArrivalSpurId *= ARRSPUR.SpurId
      

          
      select @err    = @@error    
                    if @err != 0      
                       goto ERROR
              

      
      goto OK    
COMPLETED:

IF Len(@CustomerNo) > 1
Begin
 if len(@MonitoringNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where monitoringno like @MonitoringNo
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl
      
      Select @Counter = @Counter + 1
    End

 if len(@CustomerRefA) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefA like @CustomerRefA
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@CustomerRefB) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefB like @CustomerRefB
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarNo) > 1
    begin      

      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where RailcarNo like @RailcarNo
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End



 if len(@RailcarRef) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )

      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where b.RailcarReference like @RailcarRef
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and (CustomerID = @PrimCustID or SendingCustomerID = @PrimCustID or ArrivalCustomerID = @PrimCustID or ClientCustomerID = @PrimCustID
      or exists (select 1
                  from CustomerACL
                  where CustomerID = @PrimCustID
                  and      (AccessToCustomerID = a.CustomerId or
                         AccessToCustomerID = a.SendingCustomerID or
                         AccessToCustomerID = a.ArrivalCustomerID or
                         AccessToCustomerID = a.ClientCustomerID)))
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End
END
Else
Begin
 if len(@MonitoringNo) > 1
    begin      
      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where monitoringno like @MonitoringNo
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID

      Select @Counter = @Counter + 1
    End

 if len(@CustomerRefA) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefA like @CustomerRefA
             and a.Completed =1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@CustomerRefB) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where CustomerRefB like @CustomerRefB
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarNo) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where RailcarNo like @RailcarNo
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End


 if len(@RailcarRef) > 1
    begin      
      INSERT INTO #tempsearch  (Monitoringid )
      Select distinct a.monitoringid from Monitoring a  Join Monitoringrow b on a.monitoringid = b.monitoringid
                                          join Railcar c on b.railcarid = c.railcarid    
                                          join ESYUserRailcarACL d on  c.OwnerID = d.RailcarOwnerID  
      where b.RailcarReference like @RailcarRef
             and a.Completed = 1
      and a.Deleted = 0
      and a.Status = @Status
      and a.SendingDateTime between @DateFrom and @DateTo
      and      d.ESYUserID       = @UserID
      and convert(int,a.SpecialAttention) + convert(int,a.SpecialAttentionRailcar) > @SpControl

      Select @Counter = @Counter + 1
    End
END

insert  into  #tempsearch1 (monitoringid)
select monitoringid from #tempsearch
      group by monitoringid
      having count(*) = @Counter



      Select      Distinct Top 101
            MON.MonitoringID,
            MON.MonitoringNo,
            MON.CustomerRefA,
            MON.CustomerRefB,
            SENDSTN.Name as SendingStationName,
            SENDSTN.StationNo as SendingStationNo,
            SENDSPUR.Name as SendingSpurName,
            MON.SendingDateTime,
            ARRSTN.StationNo as ArrivalStationNo,
            ARRSTN.Name as ArrivalStationName,
            ARRSPUR.Name as ArrivalSpurName,
            MON.ArrivalDateTime,
            MON.ETA,
            MON.ShuntingDays,
            MON.NoOfRailcars,
            MON.LastKnownDate,
            MON.Completed,
            SpecialAttention =
            case
            when convert(int,MON.SpecialAttention) + convert(int, MON.SpecialAttentionRailcar)>0
            then
                  1
            else
                  0
            end,
            MON.MaxETA,
            MON.MaxOrigETA,
            ArrivedDateTime = (Select
                              MAX(MORR.ArrivedDateTime)
                        from
                              MonitoringRow MORR
                        where  MON.MonitoringId = MORR.MonitoringId),
            MON.Delayed
      from
            Monitoring MON,
            Customer CUS,
            Station SENDSTN,
            Spur SENDSPUR,
            Station ARRSTN,
            Spur ARRSPUR,
            #tempsearch1 TEMPS
            where
            MON.MonitoringID =  TEMPS.MonitoringID
            AND MON.CustomerId = CUS.CustomerId
            and MON.SendingStationId = SENDSTN.StationId
            and MON.SendingSpurId *= SENDSPUR.SpurId
            and MON.ArrivalStationId = ARRSTN.StationId
            and MON.ArrivalSpurId *= ARRSPUR.SpurId

                
              select @err    = @@error    
                    if @err != 0      
                       goto ERROR
              

      goto OK    


OK:        
SET NOCOUNT OFF
        return(0)
ERROR:
SET NOCOUNT OFF
        return(1)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

0
 
LVL 12

Expert Comment

by:ill
ID: 12197788
What recovery model is set for tempDB? Simple wouldn't make so much disk operation.
0
 

Author Comment

by:liranlotker
ID: 12197814
Recovery mode is simple already..
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12197840
Looking at the code...

Here some questions that you should ALL fully answer, these may point out some configuration problems:
How are the different databases (master, tempdb, YOURDB) distributed on the disk system?
what disk system do you have (how many drives, RAID, SAN, NAS ??? etc)
How much RAM have you in your Windows Server Machine?
How much RAM have you allocated to the SQL Server processes?
How is the pagefile configured (which disk, which size etc)?
How is the tempdb database configured:
* datafiles : autogrow? max size? increment size?
* transaction: autogrow? max size? increment size?

CHeers
0
 
LVL 12

Accepted Solution

by:
ill earned 75 total points
ID: 12197852
--if you only have problems with tembDB, why not avoid it with table variable?
declare @tempsearch TABLE (Monitoringid  INT primary key)  
-- and replace
#tempsearch -> @tempsearch
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12197965
I created the tables/column, without data the proc completes for me in 0 (<1) seconds...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:liranlotker
ID: 12197976
Hello,

The master, tempdb and my db all sit on the same disk. the indexes sit on another disk.
We have 5 70GB 15000RPM hard drives with RAID (not sure which, the one that duplicates the hard drive).
We have 4GB on the Windows 2003 machine which is the DB.
The MSSQL can take up to 100% of the RAM if required.
Pagefile is defined on drive C (which holds only the Windows) and is between 2GB to 4GB.
The tempdb has autogrow with no max size and 10% increment.
The transaction is the same.

Thanks,
 Liran
0
 

Author Comment

by:liranlotker
ID: 12198000
To angelIII - So for you the tempDB interaction takes less than 0 seconds ? then does this mean we defintley have a problem with the tempdb configuration?

Adnd to ill - So you suggest using a temporary table variable which will sit in the RAM? could that not be a problem if we are dealing with multiple users using the same procedure at the same time?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 12198011
>>We have 5 70GB 15000RPM hard drives with RAID (not sure which, the one that duplicates the hard drive).
RAID 1 (mirroring)
>>The MSSQL can take up to 100% of the RAM if required.
If the server has no other applications running (for example IIS & Co), reduce to max 80% of RAM, say ~3 GB.
If you have other server applications, estimate/configure the amount of RAM they use and set appropriately, if you have a IIS for example set RAM for SQL Server to 2GB.

>>The tempdb has autogrow with no max size and 10% increment.
>>The transaction is the same.
You might change the MAX Size to a specific value to avoid it fills the disk.
Things you should set are:
* Initial size of tempdb to say 50 MB, with increment of 25 MB (instead of 10%)
* similar for the transaction log
This will prevent slow response times on the tempdb because of file grows.
Don't use shrink db on tempdb too often, once per week is ok.

ill: >>What recovery model is set for tempDB? Simple wouldn't make so much disk operation.
I think tempdb model cannot be changed anyhow


As posted above, i quickly implemented some empty tables to run your proc on my SLOW workstation, and exection time was below second. You might send me a zipped backup of your database (remove any crutial data before) so I could test with your db...

Cheers
0
 
LVL 12

Expert Comment

by:ill
ID: 12198029
it won't. table variables have same scope as normal variables. it's size ( few thousands* (4+PK)B) means nearly nothing to server.
rem: i think fastest way to solve this problem is to avoid it.
0
 

Author Comment

by:liranlotker
ID: 12198455
Thank you both, I made the changes to the tempdb as was suggested and I also tried using variables instead of the tempdb directly, the results are now returned in less than 2 seconds (instead of 24 before).

Only question I have is - This system could potentially have hundreds of almost simultanious requests for results from this SP. Would using variables affect that in any way? Also, abou tthe size of the tempdb, would that require me to make it bigger (I set maximum size for 512MB for the transaction log and the tempdb itself).?

Thank you again.
 Liran
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12198497
>Would using variables affect that in any way?
no. variables are local to the procedure, i/e local to the session running the proc.

>Also, abou tthe size of the tempdb, would that require me to make it bigger (I set maximum size for 512MB for the transaction log and the tempdb itself).?
The tempdb is used
* for temporary tables
* for intermediate results for SQL Server during sort/group operations

This means if you use often/many temporary tables with large data amounts, or many queries where SQL Server needs intermediate results for sorting/grouping you will need a tempdb sized appropriately.

CHeers
0
 
LVL 12

Expert Comment

by:ill
ID: 12198514
re: Would using variables affect that in any way.
there will be no conflicts with @tempsearch ( look at "table data type" in BOL for details).
it'll just coast some memory, mostly the same ammount as if you use #tempsearch.
0
 

Author Comment

by:liranlotker
ID: 12198846
Thank both of you (ill and angelIII) for your help, seems like things are working! The only problem is, I want to give the points here to both of you but dont know how to do that.. :)

Liran
0
 
LVL 12

Expert Comment

by:ill
ID: 12198894
you can use link " split points" above comment area
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now