How does the transaction log record a transaction\ How does the data file records a transaction.\How SQL query goes through.
Following is the process which a SQL Query goes through.
1.Once the SQL query is fired at the application end from there it is converted into TDS (Tabular
Data Stream) Packets using ODBC or OLEDB or SNAC (SQL Native Client) or SNI (SQL Server Network Interface).
These are data access protocols which are used to access all types of data and grind them into TDS packets to be encapsulated within network packets to be travelled through network protocols from one endpoint to other.
2. Once TDS packets reach the server endpoint, the SNI decapsulates those packets into SQL commands.
3.The commands are passed through Query Parser or Command Parser and it checks the query for any Syntactical (Syntax) or Semantical (Logical) errors and if any error occurs it returns the error to the N/W protocol layer.
4.If it passes through the command parser, the next step is to generate a query plan.
The query optimizer selects a cost effective plan provided to it using Query Tree (It uses certain algorithms to generate different query plans and presents it to the Query Optimizer).
5.Query optimizer then selects a cost effective plan and presents the query to Query Executor.
To execute a plan Query Executor needs data as well. So it passes the request to the Access Methods which is a collection of codes which provides an interface to retrieve data and present it to Query Executor after valid conversion using OLEDB. It itself does not do this work, rather it asks the buffer manager for the data.
6.Here first all corresponding data pages will move from disk to buffer pool. This operation of reading data pages from disk to memory is known as physical\Read IO.
But if we running the same query again then there is no need to read data pages from disk to buffer pool because all the data pages are already in buffer pool. This operation is known as Logical IO.
If the data is there in the Buffer pool, Access methods fetch those data pages and pass them to the query executor to execute the query. If the query plan is already in the Plan cache the executor uses that plan.
Note: Changes are not directly made to the data pages on the disk.
7.The work of access method is to check if the query is select or non-select (DML).
If the query is non-select (DML)the access methods contact Transaction Manager. Transaction Manager has two components,
1.Log Manager:
Logs the events that will be modifying the data into Log Buffer in Buffer Pool.
2.Lock Manager:
Assigns a lock on that transaction to provide data consistency and isolation.
8.Transaction Manager generates a Log Sequential Number(LSN) for that transaction and records the events that will be modifying the data in Log Buffer.
and the Transaction will make changes in the buffer cache using only locking mechanism to be isolated from any other transaction who wants to modify the same data.
Once this is committed, the transaction manager will tell the buffer manager that the transaction is persisted - buffer manager then changes the pages in memory (a dirty page).
These modified pages reside in the buffer cache and are known as Dirty Pages, as they are not written to the disk as of yet.
9.Now there is a checkpoint process that is an automatic recurring event in SQL server and runs in the background.
When checkpoint happens, it flushes all the dirty pages (Modified Pages) to the disk and marks the pages as clean (pages not modified since last fetch) in the buffer cache, but does not deallocate those pages from cache. Before it does that the Log records are pushed into a Virtual Log File (data page of log file) with the LSN in Transaction Log from Log Buffer. This process of writing to the Log File before writing to the disk is known as Write Ahead Logging.
10.Lazy Writer, again a background process, also flushes the pages out of the buffer pool to the disk.
When the SQL server comes under the memory pressure, lazy writer deallocates the pages which are residing there unused, also the clean pages from the memory,
and writes the dirty pages to the disk to be able to make some memory space for other operations.
Following is the process which a SQL Query goes through.
1.Once the SQL query is fired at the application end from there it is converted into TDS (Tabular
Data Stream) Packets using ODBC or OLEDB or SNAC (SQL Native Client) or SNI (SQL Server Network Interface).
These are data access protocols which are used to access all types of data and grind them into TDS packets to be encapsulated within network packets to be travelled through network protocols from one endpoint to other.
2. Once TDS packets reach the server endpoint, the SNI decapsulates those packets into SQL commands.
3.The commands are passed through Query Parser or Command Parser and it checks the query for any Syntactical (Syntax) or Semantical (Logical) errors and if any error occurs it returns the error to the N/W protocol layer.
4.If it passes through the command parser, the next step is to generate a query plan.
The query optimizer selects a cost effective plan provided to it using Query Tree (It uses certain algorithms to generate different query plans and presents it to the Query Optimizer).
5.Query optimizer then selects a cost effective plan and presents the query to Query Executor.
To execute a plan Query Executor needs data as well. So it passes the request to the Access Methods which is a collection of codes which provides an interface to retrieve data and present it to Query Executor after valid conversion using OLEDB. It itself does not do this work, rather it asks the buffer manager for the data.
6.Here first all corresponding data pages will move from disk to buffer pool. This operation of reading data pages from disk to memory is known as physical\Read IO.
But if we running the same query again then there is no need to read data pages from disk to buffer pool because all the data pages are already in buffer pool. This operation is known as Logical IO.
If the data is there in the Buffer pool, Access methods fetch those data pages and pass them to the query executor to execute the query. If the query plan is already in the Plan cache the executor uses that plan.
Note: Changes are not directly made to the data pages on the disk.
7.The work of access method is to check if the query is select or non-select (DML).
If the query is non-select (DML)the access methods contact Transaction Manager. Transaction Manager has two components,
1.Log Manager:
Logs the events that will be modifying the data into Log Buffer in Buffer Pool.
2.Lock Manager:
Assigns a lock on that transaction to provide data consistency and isolation.
8.Transaction Manager generates a Log Sequential Number(LSN) for that transaction and records the events that will be modifying the data in Log Buffer.
and the Transaction will make changes in the buffer cache using only locking mechanism to be isolated from any other transaction who wants to modify the same data.
Once this is committed, the transaction manager will tell the buffer manager that the transaction is persisted - buffer manager then changes the pages in memory (a dirty page).
These modified pages reside in the buffer cache and are known as Dirty Pages, as they are not written to the disk as of yet.
9.Now there is a checkpoint process that is an automatic recurring event in SQL server and runs in the background.
When checkpoint happens, it flushes all the dirty pages (Modified Pages) to the disk and marks the pages as clean (pages not modified since last fetch) in the buffer cache, but does not deallocate those pages from cache. Before it does that the Log records are pushed into a Virtual Log File (data page of log file) with the LSN in Transaction Log from Log Buffer. This process of writing to the Log File before writing to the disk is known as Write Ahead Logging.
10.Lazy Writer, again a background process, also flushes the pages out of the buffer pool to the disk.
When the SQL server comes under the memory pressure, lazy writer deallocates the pages which are residing there unused, also the clean pages from the memory,
and writes the dirty pages to the disk to be able to make some memory space for other operations.
No comments:
Post a Comment