February 4, 2010 at 12:22 am
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.
February 4, 2010 at 12:35 am
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 🙂
February 4, 2010 at 6:56 am
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
February 4, 2010 at 7:24 am
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" 😀
February 4, 2010 at 6:18 pm
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
February 4, 2010 at 6:29 pm
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
February 5, 2010 at 12:05 am
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.
February 5, 2010 at 4:38 am
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