Post reply

On the first run sp takes allong time to execute

  • December 24, 2003 at 12:07 am

    #177384

    Hello all gurus of SQL

    I am running a sp on SQL2000 on Win2k machine with 128 MB ram.

    In the sp select /update/insert statements are used as they are the part of logic.This sp also fires another stored procedures.

    My problem is when this sp is executed for the first time ( after restarting the sql server) it takes around 45 -50 SECONDS.

    But when we execute it contonuously ( from query analyser )it takes only 150 -500 MILISECONDS.

    If we run it afetr sone time gap say 1 hour then this sp takes 7-8 SECONDS.

    Please help me out as I am totally confused on this behaviour of SQl server.

  • December 24, 2003 at 6:07 am

    #487729

    Taken from BOL as I could not put it in my own words 🙁

    This is just a basic explaination...

    When any SQL statement is executed in SQL Server 2000, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2000 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement.

    If no existing execution plan exists, SQL Server 2000 generates a new execution plan for the query. (this results in the time consumption when the procedure is run 1st time after SQL Server starts)

    After an execution plan is generated, it stays in the procedure cache.

    The age query plan is decremented, unless another user references the plan

    HTH

    Sachin

    🙂


    Regards,
    Sachin Dedhia

  • December 24, 2003 at 7:30 am

    #487739

    quote:

    I am running a sp on SQL2000 on Win2k machine with 128 MB ram.

    It could be shortage of memory resource. It runs longer first time could be caused involving I/O activities to load all necessary data it needs to run. After that, all data is in the cache and it runs faster next time.

    You should review the execution plan each time you feel the sp behaves stranger.

  • December 25, 2003 at 12:25 am

    #487799

    quote:

    It could be shortage of memory resource

    I have a sp that runs for approx 2 min on SQL2000 on Win2k machine with 128 MB RAM and approx 30 seconds with 256 MB RAM

    But the repeated execution of the same sp on

    SQL2000 on Win2k machine with 128 MB RAM does not makes comparable difference as compared to with 256 MB RAM.

    Sachin


    Regards,
    Sachin Dedhia

  • January 7, 2004 at 7:13 am

    #488950

     thanks sachindedhiya fo ryour suggestions.

Viewing 5 posts - 1 through 5 (of 5 total)

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