• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Intermittent Long Stored Procedure run time + tempdb size explosion

I'm experiencing an issue with a intermittently long running stored procedure.  I've performed analysis on our production environment to isolate the stored procedure in question and noticed that when I run it, it sometimes results in the size of the TempDB to explode to 5-6 gig.  This stored proedure creates 5 temp tables (2 tables containing 9 columns, and 3 with 3 columns each) of which each are populated with approximately 30 rows of data each.  These temp tables are joined with each other, which is the only detail I can think of which may be resulting in tempdb size explosion

I have copied the sql code from the the stored proc to Query analyzer and run it, and surprisingly it always runs fast, completing in approximately 6 seconds. But when run as a stored procedure containing exactly the same code, it either takes 6 seconds or 5 minutes.  When it takes 6 seconds, I notice only a small amount of growth in tempdb (maybe 2 or 3 meg at the max).  When it takes 5 minutes, the tempdb grows to 5 or 6 gig, and processor utilization hits 100% for the full 5 mins.  

What baffles me is the intermittent nature of the long run times.  The only consistent observation is that when the stored proc takes 5 mins to run, it always results in the tempdb expanding to 5 gig & cpu hits 100% for the whole time.  When the stored proc only takes 6 seconds, cpu utilisation is 50% and tempdb grows by only a few meg.

I have experienced long run times executing the stored proc during both busy production times, as well as 2am in the morning when database is idling.  There appears to be no real correlation between the amount of available database resrouces and the execution time of the SP.  I have reindexed the DB using profiler/ index tuning wizard, as well as defragment / reconstruction of indexes using DBCC and a database maintenance plan.  This makes no difference to the reliability of the run times.  I have also recreated/renamed the stored proc numerous times, but the same eratic execution times continue to occur.  as a side note, the TempDB is sitting on a Raid 0+1 striped volume on a HP dl380 server.

Anyone who can provide some more ideas would be much appreciated.


0
konekt
Asked:
konekt
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
try to replace the temp tables with table variables

also send us the Query
0
 
imran_fastCommented:
if this the case try to use either Permanent table in side the stored procedure and issue turncate table statement for these tables in the begining of the procedure or use Table Vaiables (as  aneeshattingal  suggested).
Please Post The Procedure if possible.
Imran
0
 
LowfatspreadCommented:
does the data that you execute the stored procedure with vary markedly such that different access paths are likely to be
optimal for different inputs?

I suspect that that will be the case...

in that scenario then either create the stored procedure with the WITH RECOMPILE phrase
or execute it with the RECOMPILE Phrase...

hth

ands please post the procedures...

 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
konektAuthor Commented:
Thanks for the comments so far.  I have tried the WITH RECOMPILE option on both the stoerd proc definition and call, & also tried with both temp tables and table variables but still no difference.  Please see below for the nasty sproc code:


CREATE PROCEDURE dbo.spREPORTING_GetAgedWIPTableByWorkgroup 7000

(
      @WorkgroupID int
)

AS


DECLARE
      @CurrentDate DateTime

SET @CurrentDate = DateAdd(ss, -1, (DateAdd(dd, 1, GetDate())))
--SET @WorkgroupID = 7000


Declare @tmpWIPDistinctItems TABLE
(
      NoteID int,
      LogID int,
      ConsultantID int,
      ConsultantName nvarchar(100),
      RecordModifiedDate datetime,
      FEEAmtIncGST float,
      GSTAmt float,
      FEEAmtExGST float
)

INSERT INTO @tmpWIPDistinctItems
SELECT DISTINCT
      dbo.tblLog_TAC.NoteID ,
      dbo.tblLog_TAC.LogID,
      dbo.tblPerson.PersonID as ConsultantID,
      dbo.tblPerson.GivenName + ' ' + UPPER(dbo.tblPerson.LastName) as ConsultantName,
      dbo.tblActivityNote.RecordCreatedDate,
      dbo.tblLog_TAC.DiffFee + dbo.tblLog_TAC.DiffGST as FeeAmtIncGST,
      dbo.tblLog_TAC.DiffGST as GSTAmt,
      dbo.tblLog_TAC.DiffFee as FEE
FROM
      dbo.tblActivityNote  WITH (NOLOCK)
      INNER JOIN dbo.tblLog_TAC WITH (NOLOCK) ON dbo.tblActivityNote.NoteID = dbo.tblLog_TAC.NoteID
      INNER JOIN dbo.tblPerson WITH (NOLOCK) ON dbo.tblActivityNote.RecordCreatorID = dbo.tblPerson.PersonID
      INNER JOIN dbo.tblCaseReferralWorkgroup WITH (NOLOCK) ON dbo.tblActivityNote.ReferralID = tblCaseReferralWorkgroup.ReferralIDFK
WHERE
      dbo.tblLog_TAC.RecordModifiedDate <= @CurrentDate
      AND dbo.tblCaseReferralWorkgroup.WorkgroupIDFK = @WorkgroupID
      AND (
            ((dbo.tblLog_TAC.RecordModifiedDate >= dbo.tblCaseReferralWorkgroup.DateValidFrom) AND ((dbo.tblLog_TAC.RecordModifiedDate <= dbo.tblCaseReferralWorkgroup.DateValidTo)))
            OR ((dbo.tblLog_TAC.RecordModifiedDate >= dbo.tblCaseReferralWorkgroup.DateValidFrom) AND ((dbo.tblCaseReferralWorkgroup.DateValidTo IS NULL)))
      )


DECLARE @tmpWIPTable TABLE(
      ConsultantID int,
      ConsultantName nvarchar(200),
      LessThan30DayPercentage float,
      LessThan30DayAmount float,
      LessThan60DayPercentage float,
      LessThan60DayAmount float,      
      GreaterThan60DayPercentage float,
      GreaterThan60DayAmount float,
      TotalAmount float
)
      


INSERT INTO @tmpWIPTable
SELECT DISTINCT
      tmpWIPDistinctItemsParent.ConsultantID,
      tmpWIPDistinctItemsParent.ConsultantName,
      ROUND((ISNULL((SELECT SUM(tmpWIPDistinctItems1.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems1 WHERE tmpWIPDistinctItems1.RecordModifiedDate > DateAdd(day, -30, @CurrentDate) AND tmpWIPDistinctItems1.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems1.FEEAmtIncGST)  <> 0), 0)
            / (ISNULL((SELECT SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItemsTotal WHERE tmpWIPDistinctItemsTotal.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST)  <> 0), 0) + 0.001) * 100), 2) as LessThan30DayPercentage,
      ROUND(ISNULL((SELECT SUM(tmpWIPDistinctItems1.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems1 WHERE tmpWIPDistinctItems1.RecordModifiedDate > DateAdd(day, -30, @CurrentDate) AND tmpWIPDistinctItems1.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems1.FEEAmtIncGST)  <> 0), 0), 2) as LessThan30DayAmount,
      ROUND((ISNULL((SELECT SUM(tmpWIPDistinctItems2.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems2 WHERE  tmpWIPDistinctItems2.RecordModifiedDate <= DateAdd(day, -30, @CurrentDate) AND tmpWIPDistinctItems2.RecordModifiedDate > DateAdd(day, -60, @CurrentDate) AND tmpWIPDistinctItems2.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems2.FEEAmtIncGST)  <> 0), 0)
            / (ISNULL((SELECT SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItemsTotal WHERE tmpWIPDistinctItemsTotal.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST)  <> 0), 0) + 0.001) * 100), 2) as LessThan60DayPercentage,
      ROUND(ISNULL((SELECT SUM(tmpWIPDistinctItems2.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems2 WHERE tmpWIPDistinctItems2.RecordModifiedDate <= DateAdd(day, -30, @CurrentDate) AND tmpWIPDistinctItems2.RecordModifiedDate > DateAdd(day, -60, @CurrentDate) AND tmpWIPDistinctItems2.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems2.FEEAmtIncGST)  <> 0), 0), 2) as LessThan60DayAmount,
      ROUND((ISNULL((SELECT SUM(tmpWIPDistinctItems3.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems3 WHERE tmpWIPDistinctItems3.RecordModifiedDate <= DateAdd(day, -60, @CurrentDate) AND tmpWIPDistinctItems3.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems3.FEEAmtIncGST)  <> 0), 0)
            / (ISNULL((SELECT SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItemsTotal WHERE tmpWIPDistinctItemsTotal.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST)  <> 0), 0) + 0.001) * 100), 2) as GreaterThan60DayPercentage,
      ROUND(ISNULL((SELECT SUM(tmpWIPDistinctItems3.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItems3 WHERE tmpWIPDistinctItems3.RecordModifiedDate <= DateAdd(day, -60, @CurrentDate) AND tmpWIPDistinctItems3.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItems3.FEEAmtIncGST)  <> 0), 0), 2) as GreaterThan60DayAmount,
      ROUND((ISNULL((SELECT SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST) FROM @tmpWIPDistinctItems tmpWIPDistinctItemsTotal WHERE tmpWIPDistinctItemsTotal.ConsultantID = tmpWIPDistinctItemsParent.ConsultantID HAVING SUM(tmpWIPDistinctItemsTotal.FEEAmtIncGST)  <> 0), 0)), 2) as TotalAmount
FROM @tmpWIPDistinctItems  tmpWIPDistinctItemsParent
GROUP BY tmpWIPDistinctItemsParent.ConsultantID, tmpWIPDistinctItemsParent.ConsultantName
ORDER BY tmpWIPDistinctItemsParent.ConsultantName ASC

SELECT * FROM @tmpWIPTable WHERE TotalAmount <> 0 order by ConsultantName ASC
GO
0
 
LowfatspreadCommented:
it does look nasty

try it like this...

CREATE PROCEDURE dbo.spREPORTING_GetAgedWIPTableByWorkgroup 7000

(
     @WorkgroupID int
)

AS

SET NOCOUNT ON

DECLARE @CurrentDate DateTime

SET @CurrentDate = DateAdd(ss, -1, (DateAdd(dd, 1, GetDate())))
--SET @WorkgroupID = 7000


Declare @tmpWIPDistinctItems TABLE
(
     NoteID int,
     LogID int,
     ConsultantID int,
     ConsultantName nvarchar(100),
     RecordModifiedDate datetime,
     FEEAmtIncGST float
--    ,
--     GSTAmt float,
--     FEEAmtExGST float
)

INSERT INTO @tmpWIPDistinctItems
SELECT DISTINCT
       dbo.tblLog_TAC.NoteID ,
       dbo.tblLog_TAC.LogID,
       dbo.tblPerson.PersonID as ConsultantID,
       dbo.tblPerson.GivenName + ' ' + UPPER(dbo.tblPerson.LastName) as ConsultantName,
       dbo.tblActivityNote.RecordCreatedDate,
       dbo.tblLog_TAC.DiffFee + dbo.tblLog_TAC.DiffGST as FeeAmtIncGST
 --,
 --      dbo.tblLog_TAC.DiffGST as GSTAmt,
 --      dbo.tblLog_TAC.DiffFee as FEE
  FROM dbo.tblActivityNote  WITH (NOLOCK)
 INNER JOIN dbo.tblLog_TAC WITH (NOLOCK)
    ON dbo.tblActivityNote.NoteID = dbo.tblLog_TAC.NoteID
 INNER JOIN dbo.tblPerson WITH (NOLOCK)
    ON dbo.tblActivityNote.RecordCreatorID = dbo.tblPerson.PersonID
 INNER JOIN dbo.tblCaseReferralWorkgroup WITH (NOLOCK)
    ON dbo.tblActivityNote.ReferralID = tblCaseReferralWorkgroup.ReferralIDFK
WHERE dbo.tblLog_TAC.RecordModifiedDate <= @CurrentDate
  AND dbo.tblCaseReferralWorkgroup.WorkgroupIDFK = @WorkgroupID
  AND dbo.tblLog_TAC.RecordModifiedDate Between dbo.tblCaseReferralWorkgroup.DateValidFrom
                                            AND Coalesce(dbo.tblCaseReferralWorkgroup.DateValidTo,'99991231 23:59:59.997')

SELECT
     ConsultantID,
     ConsultantName,

     ROUND(LessThan30DayAmount
          / (TotalAmount + 0.001) * 100, 2) as LessThan30DayPercentage,

     ROUND(LessThan30DayAmount, 2) as LessThan30DayAmount,

     ROUND(LessThan60DayAmount
          / (TotalAmount + 0.001) * 100, 2) as LessThan60DayPercentage,

     ROUND(LessThan60DayAmount, 2) as LessThan60DayAmount,

     ROUND(GreaterThan60DayAmount
          / (TotalAmount + 0.001) * 100, 2) as GreaterThan60DayPercentage,

     ROUND(GreaterThan60DayAmount, 2) as GreaterThan60DayAmount,

     ROUND(TotalAmount, 2) as TotalAmount

FROM
(Select consultantid
      ,ConsultantName
      ,Isnull(Sum(FeeAmtIncGST),0) as TotalAmount
      ,Isnull(sum(case when RecordModifiedDate > DateAdd(day, -30, @CurrentDate)
                then FeeAmtIncGST else Null end),0) as LessThan30DayAmount
      ,Isnull(sum(case when RecordModifiedDate <= DateAdd(day, -30, @CurrentDate)
                     and RecordModifiedDate > DateAdd(day, -60, @CurrentDate)
                then FeeAmtIncGST else Null end),0) as LessThan60DayAmount
      ,Isnull(sum(case when RecordModifiedDate <= DateAdd(day, -60, @CurrentDate)
                then FeeAmtIncGST else Null end),0) as GreaterThan60DayAmount
  From @tmpWIPDistinctItems
 group by consultantID,ConsultantName
 Having Sum(FeeAntIncGST) <> 0
 ) as B

ORDER BY ConsultantName ASC


RETURN
GO


although i'm unclear as to why you
need to select distinct in the creation of the temp table...

and you could probably dispence with the temp table altogether and just do a single select
against the base tables...

hth
0
 
konektAuthor Commented:
Thanks hth

Your suggested sql changes made a huge difference to the execution time when I ran the query in query analyser!  Much more elegant solution so thanks for taking the time to rewrite my cumbersome code.  

Frustratingly though, when I copied the SQL from QA into a NEW stored procedure and ran it with exactly the same arguements, it sent the sql server processor utilisation to 100% until I canceled the query execution 4 minutes later.  I'm starting to think there is an issue / difference with the database server's config regarding the way it runs stored procs as opposed to how it runs SQL statements from query analyser.  

Any ideas anybody?  - This is turning into a nightmare!



0
 
konektAuthor Commented:
Well it appears that nobody else has a solution for me, so thanks to hth for the re-coded solution.  It has enabled me to streamline many queries, and overall speed has been increased.  Thanks again to all.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now