Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-12-04 : 04:30:04
|
| Hi all, We need few clarifications with regard to the caching in SQL Server.1. When does the SQL Server clears the CACHE or at which event the CACHE gets cleared (specifically execution plans)? 2. Is there any way to retain the execution plans in the CACHE without clearing them ? 3. Can we rely upon the SQL Server CACHE for better performance or the system? We have a stored procedure which takes 25 sec and when the execution plan is cached it takes only 5 sec. So can we rely on execution plan caching in the production environment? Thanks & Regards,Hari Haran Arulmozhi |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-04 : 04:44:19
|
| 1. It depends upon number of queries fired against server, memory allocated to SQL server and work load on SQL Server in general.2. No3. Yes, caching of execution plan definitely boosts the performance.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2007-12-04 : 11:48:50
|
| 2. I would expand a little on this - yes, by using them. Expensive plans are retained, less expensive plans removed. Expense is based on how complex the plan is and how often it is used. As such you can rely on SQL Server to retain the stuff you need and dump stuff you don't.More info:http://sqlblog.com/blogs/kalen_delaney/archive/2007/11/04/did-you-know-sp2-does-not-limit-the-amount-of-plan-cache-you-can-have.aspx |
|
|
|
|
|
|
SQL Server Cache.