Execution plan in memory | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Execution plan in memory

Hi, I have a question, can we store the execution plan of a SP always in memory (Cache)? My problem is I have a complex procedure which is taking 3-6 mins for the first time and if we run immidiately (or if the execution plan is in Cache) it is taking only 30-60 sec. This procedure is having 20 complex update statements on transaction tables. We already worked on performance/execution plans and created indexes. We are not able to increase the performance more than this. Now I am thinking if I can keep the execution plan of this SP in memory that will runs fast. Please give your suggstions. Thanks
Raj
As far I know, no.
After first execution and if memory is plenty, execution plan will be in cache.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Yes the plan will be stored in cache.
You can check them by quering system table. USE MASTER
GO
select * from syscacheobjects DBCC FREEPROCCACHE
to free up cache table.
quote:Originally posted by [email protected] Hi, I have a question, can we store the execution plan of a SP always in memory (Cache)? My problem is I have a complex procedure which is taking 3-6 mins for the first time and if we run immidiately (or if the execution plan is in Cache) it is taking only 30-60 sec. This procedure is having 20 complex update statements on transaction tables. We already worked on performance/execution plans and created indexes. We are not able to increase the performance more than this. Now I am thinking if I can keep the execution plan of this SP in memory that will runs fast. Please give your suggstions. Thanks
Raj
The performance improvement you see comes from data being in the cache not stored procedure. I don’t think sp (re)compilation(s) takes minutes even for large and complex stored procedure.
More if you have done all tuning task.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
]]>