How to save a query execution plan so that you can use it later

Question

My applications run queries against a SQL Server database.

In many cases, I see the advantage of the execution plan: for example, I press the button for the first time on the button

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

it takes 15 seconds for the first time, 8 seconds for the next time points.

EDIT: I USE PARAMETRIZED REQUESTS.

So, the second time I have an improvement of 7 seconds.

Now, when I start the application again (so that I am making a new connection to the database), the first time it takes 15 seconds, the second time 7 ...

How can you tell SQL Server to store execution plans, at least to remember them for the same days? Or, how can I benefit from already calculated implementation plans? If different users run the same query, this is a way to tell SQL Server to be smart enough to use the same execution plan, even if IdUser is different in this case.

I have some parameters in the software, so maybe the next query execution will have different MinDate and MaxDate, but will this affect the query plan?

Answer 1

Use parameterized queries to maximize the chances of caching a plan

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

SQL Server , .

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'

Answer 2

SQL Server - , .

, , , . , SQL- - , , SQL Server, , , .

, :

• - ( /, , dbo. ..)

• ; , , .

• SELECT * - , - , . , , , , , , ( ), index scan/index ( ).

, SQL :

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

.

Answer 3

SQL Server Profiler? , , ?

, 7 . , . .

An additional problem is the data cache. When you access data for the first time, SQL Server loads it into its cache. This can reduce the time of subsequent requests (provided that it can store all the data).