Skip to main content

Buffer Management of SQL Server


Buffer Management of SQL Server:
SQL Server uses buffer pages for any changes or requests for the data on the database. These buffer pages are 8KB pages on RAM that minimizes disc Input/Output. Any 8KB page can be buffered in memory, and set of all pages currently buffered is called as buffer cache. The amount of memory available to SQL Servers decides how many pages will be cached in memory. The buffer cache is managed by Buffer Manager. Either reading from or Writing to any page on the disc happens at two levels. The data that should be either read/Written is first copied to the buffer cache and the read/write operation is carried out on the data that is currently on the Buffer Cache. The edited data is updated on the disc by the buffer manager only if the in-memory cache has not been referenced for some time. While writing pages back to the disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written with a checksum while they are written to the disc so that while the data is referred back any time, the checksum mismatch would detect the corruption of the data.

Labels:

Comments

Post a Comment

Popular posts from this blog

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql'

As huge a database grows, that tedious are regular DBA activities become. Besides, a warehouse database, can grow with no bounds to challenge.  There was a patching on one of our databases, SP3 for SQL Server 2008 R2, which was failing due to the error below. As the error says, MASTER the heart of SQL Server  “Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 2627, state 1, severity 14. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. Cannot recover the master database. SQL Server is unable to run. Restore master from a full
Post a Comment
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a
Post a Comment
Read more