MS SQL DBA Interview Questions and Answers | MS SQL Server Architecture - Question 4

Join this channel to get access to perks:

Join the Telegram Group for the scripts and assistance:

MS SQL Server Architecture:
MS SQL Server is a client-server architecture and mainly divided into two parts
1. Relational Engine
2. Storage Engine
1. The Request Comes from Through Sql Server Network Interface in The form Of The TDS (Tabular Data Stream) Packets.
2. The SNI On the Sql Server Unwrap the TDS Packed and Passed SqlCmds To The Command Parser
3. The cmd Parser Will Check for Syntax Errors and Pass the Query to Optimizer
4. The Optimizer Will Prepare Plans and Pick the Less Cost Plan and Forward That Plan to Query Executer
5. The Query Executer the Query by Accessing the Access Methods, Either It a Select or Update Statement
6. If It Is Select Query Directly It Access the Buffer Cache for Data. If The Data Is Not Available In Buffer Cache. Then It Picks Form Data File. Fetching Data from Data File Is Called Hardening
7. If It Is Not a Select Query. Then Transaction Manger Will Handle That Query.
8. The Transaction Manager Has Again Two Components.
• Log Manager
• Lock Manager
9. The Log Manager Assign a Sequential Number Called LSN (Log Sequence Number) For Every Transaction.
10. The Lock Manager Places a Lock on Every Transactions Until the Transactions Get Complete
11. The Buffer Manager Had Buffer Pool. The Buffer Pool Is Having Two Internal Components
• Plan Cache
• Data Cache
12. The Plans Which Are Executed Are Stored in Plan Cache.
Its Uses LRU (Least Recent Used) Algorithm to Delete Plans Which Are Not Being Used from A Long Time
13. Data Cache Is the Place Where Modifications Done to Data.
The Pages Are Modified and Not Written to Disk. That Pages We Called It as Dirty Pages.
14. The Data Cache Is Cleared When on PLE (Page Life Expectancy) And Memory Pressure
15. When The Transaction Is Gets Completed Checkpoint Flush the Dirty Pages to Data File.

You Can Also Download the scripts from below folder
We strongly believe there is always chance of betterment, so suggestions are most welcome.

Happy learning, and All the Best in your professional journey!

The journey of improvement is ongoing and never be an end.

#azuresql #azure #sqldba #sqlserverdba #sql #sqlserver #sqlserverdeveloper #performance #performancetuning #performanceoptimization #mssql #mssqlserver #mssqlserverdba

Thank you!
MS SQL DBA Tech Support

[chandra-vz]

Thank you Harsha for your support
Can we expect more and more like this?

[contributorfactz]

Hello Harsha. Amazing content and support. Btw Scripts and video are helpful. Please upload more scenario based interview questions with practicle approach to it. Thank you keep sharing your WOW Knowledge.