what is Buffer cash and Log Cache? Can you Explain it?



what is Buffer cash and Log Cache? Can you Explain it?..

Answer / samba shiva reddy . m

Like all database servers, Microsoft SQL Server thrives on memory. Each instance of Microsoft SQL Server has its own memory address space. The SQL Server address space (Virtual memory allocation by Windows) consists of two main components: executable code and memory pool. Let's examine each of these components in more details.

SQL Server executable code

Executable code is basically SQL Server's own EXEs and DDLs that are loaded into the address space. The following is breakdown of the various components:

SQL Server Engine
Open Data Services
Server Net-Libraries
Distributed Queries
Extended Stored Procedures (DLLs)
OLE Automation Stored Procedures
SQL Server memory pool

The memory pool is the unit of memory that an instance of SQL Server uses to create and manage data structures relating to client requests. The following data structures are allocated in the memory pool of an instance of SQL Server:

Buffer Cache: This is the pool of memory pages into which data pages are read. An important indicator of the performance of the buffer cache is the Buffer Cache Hit Ratio performance counter. It indicates the percentage of data pages found in the buffer cache as opposed to disk. A value of 95% indicates that pages were found in memory 95% of the time. The other 5% required physical disk access. A consistent value below 90% indicates that more physical memory is needed on the server.
Procedure Cache: This is the pool of memory pages containing the execution plans for all Transact-SQL statements currently executing in the instance. An important indicator of the performance of the procedure cache is the Procedure Cache Hit Ratio performance counter. It indicates the percentage of execution plan pages found in memory as opposed to disk.
Log Caches: This is the pool of memory used to read and write log pages. Each log has a set of cache pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers.
Connection Context: Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for stored procedures, cursor positioning information, and tables currently being referenced.
System-level Data Structures: These are data structures that hold data global to the instance, such as database descriptors and the lock table.
The buffer cache, procedure cache, and log caches are the only memory elements whose size is controlled by SQL Server.

A very important aspect to watch for is whether SQL Server is using the maximum memory available on the system (assuming the system is dedicated to SQL Server). A system with a fully utilized memory may be prone to performance bottlenecks when competition for resources increases. Prepared Transact-SQL statements, for example, may suffer when the procedure cache is unable to expand due to fully utilized buffer caches.

Analyzing cache hit ratios over time with Teratrax Performance Monitor

Teratrax Performance Monitor tracks the history of the buffer and procedure cache hit ratios so you can view the trends of the ratios over time. Performance Monitor provides two counters for cache hit ratios: Cache Hit Ratio (Buffer cache) and Proc Cache Hit Ratio (Procedure cache). It also provides a variety of views ranging from real-time charting of cache hit ratios all the way to daily, weekly, and monthly charts.

Is This Answer Correct ?    2 Yes 0 No

Post New Answer

More SQL Server Interview Questions

Tell me what do we need queues in sql service broker?

0 Answers  


How to find the source of a table in sql server?

0 Answers  


how to control the amount of free space in your index pages? : Sql server database administration

0 Answers  


What is a View ? Can we insert, Update and delete a view?

0 Answers   Arigo Infotech,


1. What are the grouping function in SQL ? 2. If base table of a view deleted means, what will happen while we querying on view ? will give any error ? 3. Difference between DROP, DELETE, TRUNCATE table ? 4. What is constraints and types ? 5. What is max size of nchar & nvarchar ? 6. Define ROLLBACK, COMMIT, SAVE POINT 7. How non-clustered better ? or rank the Clustered, Non-Clustered and Table scan in performance wise 8. Select 10 rows from a table ? 9. Define DML, DDL, DCL, DTL commands ? 10. What is mean by NULL value ? NULL means "" or 0 or undefined ? 11. Default constraints ? 12. Can we have more then primary Key in table ? 13. Type of integrity ? Entity, Referential, Domain ?

10 Answers   Perot Systems,






what is the diffrence between Snap Shot and Transaction Replication

2 Answers   CSC,


Questions regarding Raiseerror?

1 Answers  


Please explain that what are the basic functions for master, msdb, model, tempdb and resource databases? : SQL Server Architecture

0 Answers  


What is the use of stored procedure?

0 Answers  


What are truncate options available in sql server? : sql server database administration

0 Answers  


what is nonclustered index

0 Answers  


Why it is recommended to avoid referencing a floating point column in the where clause?

0 Answers  


Categories