Database Internals [Good Reads]

Please download and read white paper 'OLTP Through the Looking Glass, and What We Found There'

The whitepaper provides more details on current architecture of OLTP RDBMS Systems. I can closely relate it with MSSQL Server. It covers the key architecture details. The Paper evaluates options by eliminating some of the features based on current hardware systems and demonstrates significant performance gains

 

At a beginner level some of architecture keywords / concepts good to learn are

• WAL (Write Ahead Logging)
• Log Manager
• Buffer Manager
• Two Phase Locking
• Concurrency 
• Eventual Consistency
• Transaction Manager

I am posting some relavant simple explanations of the same based on MSDN documentation. WAL -

 

Write Ahead Logging (Source - Link)

 

 

Buffer Manager - Page is fetched to main memory through Buffer Manager (Source - Link)

  

Two Phase Locking - A detailed note of how transaction is executed from Brent Ozar Post

 

Concurrency Control - Governed by Isolation levels, Earlier discussed in same blog

 

Log Manager - Manages Logging, WAL

 

Eventual Consistency - Over a period of time the data will be consitent across different nodes (databases) distributed over network. Read More @ Consistency models in nonrelational dbs 

 

 

More Reads  

• ColumnStores vs. RowStores: How Different Are They Really?
• Materialization Strategies in a Column-Oriented DBMS
• Query Execution in Column-Oriented Database Systems

 

Happy Reading!!!