Buffer Pool - Buffer Cache - Buffer Cache hit ratio (Confusion between three)
-
What i know is
Buffer Pool: It is a large portion in SQL Server where it is a collection of 8KB Buffers which allocates memory for data, free pages and query plan cache.
so now i got a question that Buffer cache? What is Buffer Cache? does is store the required pages/plan cache to access faster than buffer pool i.e. which are used/has frequent access are stored in buffer cache.
Where does the query or procedure plan cache are stored in buffer pool or buffer cache?
Kindly give an answer that makes me clarity how and what gets stored in memory/buffer pool.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Buffer cache is just another name for the buffer pool, both names means the same thing: 8k data pages stored in memory. Buffer cache hit ratio is the percentage of pages found in the buffer cache (memory) without having to read them from disk.
-
Thanks for answering,
Now i understood there will be a buffer pool with data, free pages with 8KB buffers and plan cache and data cache where SQL will load the data from disk into the buffer pool and again it will load the required data i.e. required for the specific query and keeps it in buffer/data cache and this retrieving process hit ratio from buffer pool to buffer cache/data cache is buffer cache hit ratio.
Is my above understanding true.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (6/16/2016)
Thanks for answering,Now i understood there will be a buffer pool with data, free pages with 8KB buffers and plan cache and data cache where SQL will load the data from disk into the buffer pool and again it will load the required data i.e. required for the specific query and keeps it in buffer/data cache and this retrieving process hit ratio from buffer pool to buffer cache/data cache is buffer cache hit ratio.
Is my above understanding true.
Basically yes, the buffer pool is the common name for both the plan cache (also called procedure cache) and the buffer cache (also called data cache). How often SQL Server finds what it's looking for in the buffer pool compared to having to retreive it from disk decides the cache hit ratio.
-
"The Buffer Cache is another name for the buffer pool.
The buffer cache hit ratio is the percentage of pages found in the buffer cache without having to read them from disk
The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval. It is used in place of system memory that is used for caching table index data pages as they are modified or read from disk.
The SSD (Solid-State Drive) is an extension feature of Buffer Pool Extension.
"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply