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

Answer 2

NO! Not a chance!

The difference is elsewhere!
The amount of time to come up with a query plan is in the order of a few seconds at best (worse).

In all likelihood, the difference is due to either:

• cached data
• presence/absence of locks from other queries/processes
• different data context upon the second run of the query (referenced SQL objects with fewer rows etc.)

The difference mentioned: 3 hours, down to 1 minute or so is so drastic, that I don't think that cached data alone could explain it.
That's where having more info about the query would help...
For example, and even though it is the very same query ran twice, it may have a different behavior upon the second time (for example if this is an update/insert/delete type query, it may not need to modify so many rows). Even a SELECT only query could run differently because the underlying data was modified (by other queries/processes).

Here are a few suggestions to find out more:

• examine (statically) the execution plan itself. See if there is indeed something in there that is likely to be so costly as 3 hours.
• re-run the query with "statistics on" and by clearing (or not) the caches before each run.

The statements to clear caches are (there might be other ways to do so with newer version of SQL-Server):

dbcc freeproccache
go
dbcc dropcleanbuffers
go

Answer 3

Theoretically, when run for the first time (a "cold run"), the table and index pages your query required could have been located on disk. The query fetched them and the engine put them into the cache.

The second run of the query (a "hot run") used only the data from the cache which is of course much faster.

However, 3 hours is more than enough to populate even the largest cache, so this hardly can be the case.

Most probably, the other execution plan was used in the second run (maybe due to the statistics update).

Answer 4

No. Most likely the first time your query was blocked (eg. by another session, or by a growth event), while the second time was not.

To give an example take this query: INSERT INTO Table (Field) VALUES (1). Simple query, but when you run it first time, the database was full and had to grow. The database is 750GB and left at default growth of 10%, so it has to create 75GB because the SQL Server service account was not granted the Perform Volume Maintenance Tasks, so the growth lasts some 30 minutes. then you run the query again and it takes less than a second.

Point here is not that you had or had not a growth event occurring during execution. Point is that if the query lasts 3 hours, you need to understand why. Activity Monitor is a great start. Reading up a white paper like Performance Tunning Wait Queues would be even better.