Post reply

Performance Degradation

  • May 14, 2006 at 5:31 pm

    #152971

    Hi,

            I am having a issue where we run a Stored Proc which Truncate Six table and reinsert records. One table has around 3 millions records before and after truncation, 1 other has 750K records and others have around 100K records in them. Most of the time after running this Proc we do not see any Issues but periodically (once in 2 weeks) we see huge Performance Issues. CPU usage hits 100% and RAM usage is also very high and strange thing is this problem lasts for 35-40 minutes and after that SQL Server starts behaving correctly. This issue also goes away if we restart the Server.

    Some points --

    • Table with 3 million records is being in a Query when we see this problem.
    •  None of the above mentioned table has Clustered Index defined on them (Inherited this DB).
    • No re-indexing is being done in the Proc mentioned above.
    • No Updating of Statistic is being done in the Proc mentioned above.

    Can some one help me to solve this Issue? I will really appreciate any help.

     

    Thanks,

    Jay Moorthy

     

  • May 14, 2006 at 5:57 pm

    #637759

    Instead of restarting the server, try updating the statistics on the table in question.  Does that solve your issue?

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • May 14, 2006 at 6:03 pm

    #637760

    Thanks for your comment.

    Running "Update Statistics" after truncate/inserts or when we start to have problem. I have tried it during Performance problem but got nothing out of it.

    JM

  • May 14, 2006 at 6:14 pm

    #637762

    Try updating the statistics, then forcing a recompile of the procedure you are having trouble with after the truncate/inserts.  You might want to do this periodically several times after the process.  That way, the execution plan will have more up-to-date statistics available.  We had this issue with a large lead import process we built at work.  It was a pain in the arse.  We updated statistics and recompiled a procedure every 15-30 minutes, which fixed the issue. 

    Should you ever need to do this?  No.    It did fix our particular issue though.

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply