June 11, 2013 at 3:01 am
Why does the plan remove from cache?I mean what is the cause of removing the plan from cache?
I thought Update statistics causes it but I ran a query with sp_executesql (parameter query)
the plan was in cache I checked with this query:
SELECT ( SELECT query_plan
FROM sys.dm_exec_query_plan(qs.plan_handle)),est.text,creation_time,last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
ORDER BY creation_time
But after
UPDATE STATISTICS [dbo].[Table]
WITH FULLSCAN
the plan was still in cache .
June 11, 2013 at 3:25 am
As far as im aware the Plans get removed from the Cache when
1) Stats are updated, thus invalidating existing plans.
2) the table is altered
3) Indexes are rebuilt/added/deleted
4) SQL Instance is restarted
5) DBCC FREEPROCCACHE is run
Plans will be replaced if you use the WITH RECOMPILE on an SP or use hints.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 11, 2013 at 4:50 am
I have 2 database.One of them is an archive DB and it is read only.The main db has insert ,update and select statement on it, and also an update statistics job on a table with full scan.
The problem is that all the query on the archive db is ok and use the optimal plan,but on main db the plan for a query with special parameter is random,one time it is optimal and another time it is a bad plan that causes time out.(parameter sniffing)
None of the reasons that you mentioned occured,just update stats and it is a job that runs at 2 AM.But some times the plan suddenly changes during the day .
I also read that "Execution plans remain in the procedure cache as long as there is enough memory to store them.When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache" but why the plan for archive db doesnt change at all?
June 11, 2013 at 7:12 am
The reason the plan for the Archive DB doesnt change is because its read only, where as the 'live' db will have data being added/removed during the day so it could invalidate the plan that is cached, and the stats could well be out of date on any of the tables in the query.
A stop gap while you get to the bottom of the issues could be to use the OPTION(RECOMPILE) on the Select statement that casues the problem, to see if it helps, however this is generally a short term option while a more robust solution can be identified.
Not sure if you've read Gail Shaws 3 part blog on the subject of parameter sniffing but just in case you havent http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 13, 2013 at 1:03 am
Thanks Jason
My query is a simple select statement via sp_executesql but linq does not suport option(optimize for unknown) or OPTION(RECOMPILE).
Is it possible that the plan in cache suddenly removed because of auto update statistics or insufficient memory?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply