Post reply

Procedure is taking 15 seconds after service restart but from second time procedure takes 1 sec

  • January 24, 2013 at 1:06 am

    #394011

    Hi,

    I have one store procedure which is taking 15 seconds after restarting the SQL Server Service. But when I try to execute the stored procedure from 2nd time, it takes only 0.8 to 1 second.

    I think the execution plan is taking much time, so can anyone tell me how to save the execution plan. So that the procedure will take 1 second even after restarting the SQL Server service. Or please share the solution if any.

    Thanks in Advance,

    Nishant Shende

  • January 24, 2013 at 1:27 am

    #1580404

    nishantshende-532951 (1/24/2013)

    I think the execution plan is taking much time, so can anyone tell me how to save the execution plan. So that the procedure will take 1 second even after restarting the SQL Server service. Or please share the solution if any.

    your sql restart flushed all the plan from buffer thats y when the SP got executed first time it toook longer time and next time since it found the exec plan available SP got executed faster.

    memory mangement manage the exec plan storage in buffer cache. thats sql server internal , you dont need to take care of this. but yes right amount of memory(RAM) should be there.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • January 24, 2013 at 4:49 am

    #1580477

    Very likely has nothing whatsoever to do with the execution plan. 15 seconds is far longer than SQL takes to compile anything other than a ridiculously complex query.

    Restarting cleared the data cache, the cache of data pages. After the restart queries have to read the data from disk (slow), not cache (fast). Hence the first run of any query will be slower than the second or third or subsequent runs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2013 at 3:16 am

    #1580907

    GilaMonster (1/24/2013)

    Very likely has nothing whatsoever to do with the execution plan. 15 seconds is far longer than SQL takes to compile anything other than a ridiculously complex query.

    Restarting cleared the data cache, the cache of data pages. After the restart queries have to read the data from disk (slow), not cache (fast). Hence the first run of any query will be slower than the second or third or subsequent runs

    +10

    1st Run = Disk --> RAM --> Client

    2nd Run = RAM --> Client

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • January 28, 2013 at 9:45 am

    #1581696

    Ah, I remember the days of DBCC PINTABLE. Those were the days.

    Kids, dont try that at home, it doesnt work anymore.

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

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