Stored procedure first run and second run

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Stored procedure first run and second run

Author

Topic

benildusmuerling
Yak Posting Veteran

81 Posts

Posted - 2012-07-04 : 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

52326 Posts

Posted - 2012-07-05 : 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

81 Posts

Posted - 2012-07-05 : 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

52326 Posts

Posted - 2012-07-05 : 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

81 Posts

Posted - 2012-07-05 : 02:59:10

understand, could the caching flushed, when a change in the parameter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources