SQL Server -SP execution path is stored on Stack ?

Question

When I execute a stored procedure first time, it (for example) takes 3 minutes to execute. Once it is precompiled, it takes 1 minute to execute.

After 3 months (without using the SP further), when I try to execute it again it takes 3 minutes.

My question

Is the execution path stored on stack? Or it is due to some other factor?. Correct me if I am wrong in understanding.

Answer 1

As per http://technet.microsoft.com/en-us/library/ms181055%28SQL.90%29.aspx

it seems that the plan will be cache initialy, but would be removed after a period of time as other plans are cached.

Answer 2

The time difference is of course not because of the time taken to create the execution plan. In the worst case that may take a second or so, if all the statistics has to be loaded from disk...

The time difference is most likely because the tables and indexes has to be loaded from disk the first time, and is cached for the following executions.

Answer 3

It can be removed from cache for many reasons. usually:

• Memory pressure
• Statistics and index rebuilds
• Server restart (of course)

Performance difference is often table load time too (index, stats, data etc)

it's not compiled in the c# or c++ sense

Answer 4

The execution plan is stored in the cache as others have pointed out and even table data is stored in the cache an will stay there as long as possible. The compile time is not the issue in your case.

it may be:

• the stored procedure is using a different execution plan
• your indexes are fragmented
• the statistics are out of date, causing an incorrect execution plan
• you do not have enough memory

try to use the followin code Before you execute your stored procedure

SET STATISTICS IO ON

and look to see if your procedure uses physical read or logical reads. If it is logical it uses the cache to get the data