stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequent execution it give instantaneous results regardless of parameter values

  • I have stored procedure in SQL server 2005 that takes a lot of time on first execution but after subsequent execution it give instantaneous results regardless of parameter values. Please help me in this regard because I thought that it is not issue of index.

  • The first time, your SP needs to be compiled. This could explain the difference in 'apparent' execution time.

    Try

    sp_recompile 'name of your procedure'

    If you now execute your procedure again, you would notice the longer execution time.

    If that's the case, you should not worry about it. Be happy that it's executing fast 🙂

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • It could be compile time or it could be a question of data caching or it could be a combination of both.

    Usually compile times are pretty benign unless the query is incredibly complex.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant is right.

    If you want to test the impact of the caching, use

    dbcc dropcleanbuffers

    before running the SP (but after it has been compiled 🙂

    and then immediately run it a second time.

    NOTE: I strongly advise against doing this on a production server, unless you want to make some new "friends" 😀

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • Grant is correct, but the command you need to use is DBCC FREEPROCCACHE. The command mentioned by the previous poster will actually drop the cached data pages.

    Please see posting here on my blog The SQL Janitor.

    As an aside, I've periodically observed lengthy compile times with even moderately-complex SQL statements in SQL Server 2005. No simple explanation for it.

    I used to have a script that would give much more granular detail on compile and execute times. I'll post it if I can find it.

    Peter

    http://seattleworks.com including my blog The SQL Janitor

  • Found it. Use this script to get a better picture of the time to compile SQL statement(s), Function(s), or SPROC(s).

    use <mydatabase>

    go

    dbcc freeproccache

    go

    checkpoint

    go

    set statistics profile on

    go

    set statistics io on

    go

    set statistics time on

    go

    select 'starting time: '

    select getdate()

    go

    <insert your SQL here>

    go

    select 'ending time: '

    select getdate()

    go

    set statistics profile off

    go

    set statistics io off

    go

    set statistics time off

    go

    Peter

    http://seattleworks.com including my blog The SQL Janitor

  • Peter Samson (2/4/2010)


    Grant is correct, but the command you need to use is DBCC FREEPROCCACHE. The command mentioned by the previous poster will actually drop the cached data pages.

    Please see posting here on my blog The SQL Janitor.

    As an aside, I've periodically observed lengthy compile times with even moderately-complex SQL statements in SQL Server 2005. No simple explanation for it.

    I used to have a script that would give much more granular detail on compile and execute times. I'll post it if I can find it.

    Peter - it was exactly my intention to drop the cached data pages - the line before reads "If you want to test the impact of the caching" (the word data was implied here). In an earlier post I suggested to do sp_recompile to achieve a recompilation (similar to your DBCC FREEPROCCACHE). I seperated both so the impact of compilation and data caching could be assessed independently.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • OK, sorry I missed that in the comment!

    Peter

    http://seattleworks.com including my blog The SQL Janitor

Viewing 8 posts - 1 through 7 (of 7 total)

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