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.


konektAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.