September 30, 2011 at 3:08 am
Hi all
I was recording plan_handles last night that were being used in TempDB with the hope I would be able to see these plans this morning to analyse. When I use the handles to query sys.dm_exec_query_plan I get nothing back, obviously the plan has been flushed from the cache.
My question is around the plan cache and data cache. Are these one in the same thing? If I check the page life expectancy then does this impact on both the data cache and plan cache, such that if my PLE is low then this could be a reason for my execution plans being flushed?
Many thanks
September 30, 2011 at 4:13 am
The primary purpose of a SQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. And because disk I/O operations can consume many resources and take a relatively long time to finish, SQL Server focuses on making I/O highly efficient. Buffer management is a key component in achieving this efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.
Compiled plans are stored into a part of SQL Server's memory called plan cache. Plan cache is searched for possible plan reuse opportunities. If a plan reuse for a batch happens, its compilation costs are avoided.
September 30, 2011 at 4:34 am
Hi
So what about PLE then does this affect both caches? or just the buffer cache?
September 30, 2011 at 5:03 am
Ok I think I have found some information stating that PLE just affects the buffer pool. However I now have a follow up question on this. If my physical memory in SYS.DM_OS_SYS_INFO is 8588959744 bytes and my bpool_commit_target is only set at 200000 then why when I have so much available memory is my PLE extremely low?
I thought I had read that the buffer pool can expand as necessary?
September 30, 2011 at 2:02 pm
Kwisatz78 (9/30/2011)
Ok I think I have found some information stating that PLE just affects the buffer pool. However I now have a follow up question on this. If my physical memory in SYS.DM_OS_SYS_INFO is 8588959744 bytes and my bpool_commit_target is only set at 200000 then why when I have so much available memory is my PLE extremely low?I thought I had read that the buffer pool can expand as necessary?
What is max memory set to? The buffer pool will be able to expand up to the max-mem value.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 4, 2011 at 2:27 am
My server is SQL 2005 x86 and AWE is not switched on, so I guess this is why its stuck at 200,000?
October 4, 2011 at 8:47 am
Kwisatz78 (10/4/2011)
My server is SQL 2005 x86 and AWE is not switched on, so I guess this is why its stuck at 200,000?
Correct.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply