Caching SQL execution plans

Question

I know SQL server 2005 does some amount of execution plan caching, but would that be enough to create a difference of hours between the same query being run twice? The first time it takes 3 hours the next time it takes 1 min? Is that even possible?

Answer 1

SQL Server will not just cache the execution plan but also the data

if you do

SET STATISTICS IO ON

and look at the output you will see logical reads and physical reads, logical reads are from RAM, physical reads are from disk. So the first time you will see a number for physical reads while if you run it again you should see a value for logical reads

3 hours seems long, could also be because of blocking/locking, stale stats etc