| Author |
 Topic  |
|
|
benildusmuerling
Yak Posting Veteran
Australia
81 Posts |
 Posted - 07/04/2012 : 21:44:26
|
Hi All,
Thanks for reading my post, why does a stored procedure's first run takes longer than a second run.
IS IT BECAUSE OF CACHING.
Thanks,
AB |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/05/2012 : 00:01:28
|
yep...first time it runs it creates plan and caches it. Any further run will try to use the plan which is there in cache until it gets flushed out and hence will be faster.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
benildusmuerling
Yak Posting Veteran
Australia
81 Posts |
Posted - 07/05/2012 : 01:11:39
|
Oh thanks mate, when does flushing happens
one would be when the parameter changes is it, and server restart if I am wrong correct me please.
Thanks,
AB |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/05/2012 : 01:45:06
|
quote:
Originally posted by benildusmuerling
Oh thanks mate, when does flushing happens
one would be when the parameter changes is it, and server restart if I am wrong correct me please.
Thanks,
AB
there are lot of scenarios where flushing happens like:-
when database is restored
The memory is required for other objects and all available memory is currently in use.
Stored procedure is not called at all by any connection
You can also flush it manually using DBCC FREEPROCCACHE statement
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
benildusmuerling
Yak Posting Veteran
Australia
81 Posts |
Posted - 07/05/2012 : 02:59:10
|
| understand, could the caching flushed, when a change in the parameter. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/05/2012 : 09:56:57
|
change in parameter means it has to recompile the procedure and create a new plan
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Stored procedure first run and second run
