0

Stored procedures are compiled on first use.

There are options to clear cache: 

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
--To Verify whether the cache is emptied
--DBCC PROCCACHE

or to recompile or to reduce recompilations.

But is it possible to force frequently used stored procedures' execution plans be pre-cached and stay in memory?

I know how to do it in ADO.NET, i.e. from outside of SQL Server, but this question is how to do inside SQL Server - to be launched with the start of SQL Server itself.

(*) For example, I see in SSMS Activity Monitor a running process (Task State: RUNNING, Command: SELECT) that is continuously executing T-SQL (according to Profiler) in context of tempdb database though SQL Server Agent is disabled and SQL Server is not loaded by anything, see "Details of session 54" in "Where are all those SQL Server sessions from?".

How would I do the similar resident process (or, rather, auto-starting by SQL Server start service or session) periodically recycling stored procedure?

Related question:
Stored procedure executes slowly on first run

Update:
Might be I should have forked this question in 2 but my main curiosity is how to have periodic/ looping activity with SQL Server Agent disabled?
How was it made with mentioned above RUNNING SELECT session (*)?

Update2:
Frequently I observe considerable delays while executing stored procedures querying very small amount of data which cannot be explained only through necessity to read huge amounts of data.

Can we consider this - considerable delays on insignificantly small data - as context of this question?

Community
  • 1
  • 1
Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106
  • As opposed to the execution plan being cached, more often than not, it's having the data cached in memory that offers the biggest gain in performance (hence ideal world = have all the data in memory) as that prevents disk being hit – AdaTheDev Oct 26 '10 at 11:29
  • 1
    @vgv8: why would you want to do that? – Mitch Wheat Oct 26 '10 at 11:29
  • Also note to be aware of, sometimes you may want a sproc to be recompiled instead of using a plan from the cache – AdaTheDev Oct 26 '10 at 11:33
  • Near duplicate, see this question: http://stackoverflow.com/questions/2002576/sql-server-cache-question – AdaTheDev Oct 26 '10 at 11:40
  • @Mitch Wheat, I cannot want to make something impossible. Also, wanting to understand or having (un)healthy curiosity is not synonym of wanting to make. Curiosity is the engine of innovation and progress. Have I correctly answered your question? – Gennady Vanin Геннадий Ванин Oct 26 '10 at 12:25
  • @vgv8: I wasn't suggesting that curiosity was a bad thing. Forcing a query plan to stay in the cache could actually be detrimental in some circumstances. – Mitch Wheat Oct 26 '10 at 14:28

2 Answers2

0

Just execute it from a script. You could do this after any sql server restart. If they are frequently used, it shouldn't be much of a problem after that.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • 2
    won't guarantee that it stays in the cache though, unless it is hit continuously over and above other activity – Mitch Wheat Oct 26 '10 at 11:29
0

Seems like this question eventually got answered in:

Update: These tips will do the trick:

Community
  • 1
  • 1
Gennady Vanin Геннадий Ванин
  • 10,136
  • 11
  • 75
  • 106