Cached Query plans?

Question

Query plans are stored in the plan cache for both views and ordinary SQL

ok.

Once and for all : How does it help me ?

Even if I have the Query plans in the cache for a query : Once I run the query he WILL scan the whole table/s + aggregates+.... .

and if i run it tomorrow - It will scan the whole table/s + aggregates again ....

there's can't be a situation like this : " AH !!! I have Data in cache , so Ill take it from there.......( because maybe the table has changed...)

so , where is the true benefit ?

I seems to be missing something.

thank you.

It's not data that's cached, but query plans. Do you know what a query plan *is* ? — AakashM, Apr 04 '12 at 16:21
@AakashM yeah I know. I just cant see how it can help me while it is cached. — Royi Namir, Apr 04 '12 at 16:22
It helps you because if you run the same query again, you don't have to pay the cost of compiling the query plan again. Compilation can be a significant part of the performance involved with an overall query execution. — Aaron Bertrand, Apr 04 '12 at 16:24

AakashM · Accepted Answer · 2012-04-04T16:36:16.013

Suppose we have a query such as

SELECT *
FROM 
    A 
    INNER JOIN B ON -- omitted
    INNER JOIN C ON -- omitted
    -- omitted
    INNER JOIN Q ON -- omitted

with however many tables that is. Obviously, the order these joins are performed in will affect performance. Also, deciding the best order, given the table statistics, also takes an amount of time.

By caching the query plan, we can pay the cost of deciding the best order just once - every subsequent time the query is run, we already know to first take K, join it to E, then to H, and so on.

Of course, this means that a significant change in the data statistics invalidates our plan, but caching anything always involves a trade-off.

A resource you may find useful for learning more about the hows and whys of query planning is SQL Coach - start with THE Analogy.

score 2 · Answer 2 · answered Apr 04 '12 at 16:26

2

The answer is that the query plan is cached to prevent the cost of compiling the query plan every time. The second time you run the query (or another that can use the same plan) it doesn't have to pay to run the compilation process all over again, it just pulls the plan from the cache.

answered Apr 04 '12 at 16:26

Aaron Bertrand

272,866
37
466
490

thanks for reply. is it that important ? the whole job is to actually RUN the query and not "look at the plan"...? – Royi Namir Apr 04 '12 at 16:28
1

Yes, it can be *quite* important. And this is the way that SQL Server works. They did this because it is important. – Aaron Bertrand Apr 04 '12 at 16:29

score 1 · Answer 3 · answered Apr 04 '12 at 16:27

Put simply, an execution plan is an explanation of how the query will be done, not the actual data involved in the query (so an execution plan can be applied over and over again as you re-run a query).

An analogy would be to say that an execution plan is similar to a recipe - it is the method of getting the data/making the meal, not the data/meal itself.

The improvement is that it takes time for the DB engine to work out the execution plan for a query, so if its cached you don't need that overhead next time you run the same query.

score 0 · Answer 4 · answered Apr 04 '12 at 16:30

When you submit a query to SQL, it goes through some steps to display the result. The main ones are parsing, algebrizer and query optimizer.

The query optimizer is the responsible to build an execution plan, or select one from the cache and as I understand the process of building a plan is very expensive, so its better if you can reuse one.

The mains point is that the exec plan doesn't contain the data itself, only a way of retrieving it from the BD. So once the plan is "defined", it gets passed to the storage engine and used to retrieve the data.

Cached Query plans?

4 Answers4