How to store query execution plan so that they can

问题:

My applications runs queries against a sql server database.

In many cases I can see the benefit of an execution plan: for example I click for the first time on a button to

SELECT * from Tasks
WHERE IdUser = 24 AND
  DATE < '12/12/2010' and DATE > '01/01/2010'

it takes 15 seconds the first time, 8 seconds the following times.

EDIT: I USE PARAMETRIZED QUERIES.

So I have a 7 seconds improvement the second time.

Now as I run the application again (so I do a new database connection) the first time it will take 15 seconds, the second time 7...

How is it possible to tell SQL Server to store the execution plans, at least to remember them for same days? Or however how can I get benefit of already calculated execution plans? If different users run the same query is it a way to tell sql server to be smart enough to use the same execution plan, even if in that case probably the IdUser will be different.

In software I have some parameters, so may be the next execution of the query will have different MinDate and MaxDate, but will this affect the query plan?

回答1:

Use parametrised queries to maximise the chances that the plan will be cached

SELECT * from MYTasks 
WHERE IdUser = @UserId AND DATE < @enddate and DATE > @startdate

SQL Server does do auto parametrisation but can be quite conservative about this.

You can get some insight into plan reuse from the following

SELECT usecounts, cacheobjtype, objtype, text, query_plan, value as set_options
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where text like '%MYTasks%' and attribute='set_options'

回答2:

SQL Server will do this for you - any execution plan is stored for as long as there is space available.

However, you can do a lot to increase the chances of execution plans being stored and reused - what you need to take care is to create identical SQL queries - anything down to an extra space can cause SQL Server to consider two queries to be different and not reuse an existing query plan.

So therefore:

• be consistent in your queries - always spell out things in identical manner (watch out for capitalization / non-capitalization, whitespaces, dbo. prefixes etc.)

• use parameters instead of literal values; if you use literal values your query plans will not be reused, and they unnecessarily fill up the plan cache.

• try to avoid SELECT * - this means the query optimizer has less options - since you want all columns, it typically has to do a scan on the clustered index. If you specify the three, five, six columns you really need, there's a chance there might be an index that covers the query (contains all the columns you're interested in) and therefore, the query analyze could use that index and do an index scan / index seek on that (and reuse that plan).

So instead of your SQL now, use this instead:

SELECT (list of fields)
FROM dbo.MYTasks 
WHERE DATE < @EndDate and DATE > @StartDate

That should increase query plan reuse significantly.

回答3:

Have you run the SQL Server Profiler? If so, are you sure the extra time is taken in compiling the statement and generating execution plans?

From your problem description, the additional 7 seconds could be time to setup the initial connection to the database. Maybe you've ruled this out already. I don't know.

An additional concern is the data cache. The first time you access data, SQL Server loads it into its cache. This can reduce the time of subsequent queries (assuming it can hold all the data).