4

How can I keep a query execution plan in SQL Server's cache and use it several times?

When I execute a query for the first time it takes 7 seconds. After that it takes only 1 second. This query is running once on daily basis.

Is there any way I can keep the execution plan and use it every day?

Improve this question
0

2 Answers 2

Reset to default
3

AFAIK, you cannot force a plan to stay in cache. However, a query can be thrown out of the cache for several reasons. Read a blog about execution plans. It states some reasons why execution plans get invalidated:

  • Changing the structure or schema of a table referenced by the query
  • Changing an index used by the query
  • Dropping an index used by the query
  • Updating the statistics used by the query
  • Calling the function, sp_recompile
  • Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
  • For tables with triggers, significant growth of the inserted or deleted tables
  • Mixing DDL and DML within a single query, often called a deferred compile
  • Changing the SET options within the execution of the query
  • Changing the structure or schema of temporary tables used by the query
  • Changes to dynamic views used by the query
  • Changes to cursor options within the query
  • Changes to a remote rowset, like in a distributed partitioned view
  • When using client side cursors, if the FOR BROWSE options are changed

Another pitfall could be that the query changes. This can happen if the where clause changes (e.g. you filter by date) and you don't use bind

Thinking about, you haven't stated that you checked whether the execution plan is still in cache or not. You should query the cache to figure that out. However, since you say your query runs only once a day, the plan might just be expired. The blog actually mentions the formula on how sql server determines when to expire a plan:

If the following criteria are met, the plan is removed from memory:

  • More memory is required by the system
  • The "age" of the plan has reached zero
  • The plan isn't currently being referenced by an existing connection

In general, 7 seconds is not to bad if you have a big report. It takes longer to get a coffee. Does it create any other issue than 6 seconds of wasted productivity for an end user?

Improve this answer
3
  • No, its not wasted any productivity, but this is called by EntityFramework. (It is stored procedure), and not giving us very good performace, while executed from EntityFramework.
    – Virul Patel
    Commented Mar 10, 2015 at 20:12
  • 4
    EF may give you issues like that. Some of our .NET guys have experienced the same issues. Plan guides do work but can be a bear to manage once you get a bunch of them. It all depends on if your query is slow because of the plan (bad plan caused by paramater sniffing, etc) or because the relevant data is not in your buffer pool.
    – Kris Gruttemeyer
    Commented Mar 10, 2015 at 20:23
  • 2
    Entity Framework is great for devs, but not tuning and managing performance - Totally Agree
    – Virul Patel
    Commented Mar 11, 2015 at 2:10
0

It's probably not the execution plan that's making it go faster. It's the data.

After you run a query, SQL Server may keep the data in cache. Therefore, it doesn't have to read from disk to get the information, instead it can pull it from RAM, which is much faster.

While your execution plan may also be stored in the plan cache, I highly doubt compiling an execution plan is increasing your query execution time seven-fold.

The plan and data will automatically stay in memory, if you use them frequently enough, and if there isn't any "memory pressure" (caused by too little physical memory) on the server. You cannot really force a plan or data to stay fixed in cache - it will be tossed out if memory is too tight.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.