1- Protocol layer
2- Relational Engine -> It has the SQL Server components that determine what
exactly a query should do and how it can be done better.
It is responsible for executing user queries by
requesting data from the storage engine and processing the results that are
returned.
A- Cmd parser -> Check the syntax and semantic errors, end up
generating a query tree
B- Optimizer -> creates an execution plan for the user's query.
The query cost is calculated based on factors such as CPU usage, memory usage, and
input/output needs.
It has different phases
Phase 0 - Pre-Optimization Stage: There could
only be one practical and viable plan, known as a trivial plan and it does not make
sense to go searching and spend resources for some plan and find the same plan.
Phase 1 - Includes the search for a simple plan
and a complex plan. Simple plan search: The previous data of the column and index
involved in the query will be used for statistical analysis.
This usually consists of, among
others, one index per table.
If the simple plan is not found, a
more complex plan is sought. Involves multiple index per table.
Phase 2 -Parallel Processing and Optimization.
If none of the above strategies work, the Optimizer looks for parallel processing
possibilities. If this is still not possible, then the phase begins.
end of optimization. Now, the
ultimate goal of optimization is to find all other possible options to execute the
query in the best way.
C- Query Executor -> Provides an execution plan for the data
retrieval logic required for execution. Once data is received from Storage Engine,
the result is published to the protocol layer. Finally, the data is sent to the end
user.
3- Storage engine -> store data on a storage system such as disk or SAN and
retrieve the data when necessary
Types of files that are stored: Physically
stores data in the form of data pages, and each data page has a size of 8 KB, which
forms the smallest storage unit in SQL Server.
These data
pages are logically grouped to form extensions.
Primary
File: All db contain a primary file. The extension is .MDF. They store information
and important data related to tables, views, triggers, etc.
Secondary
File: The database may or may not contain several secondary files. contains user-
specific data. Extension is .ndf
Log file
(Write ahead logs): Extension is .LDF used for transaction management. It performs
the important task of rolling back uncommitted transactions.
A- Access Method -> interface between query runner and Buffer
Manager/Transaction Logs.
It does not perform any execution.
The first action is to determine if the query
is:
Select Statement (DDL) SELECT Statement
statement, the query is passed to the Buffer Manager for further processing.
Non- Select Statement (DDL & DML) NON-SELECT,
the query is passed to the Transaction Manager. This mainly includes the UPDATE
statement.
SCHEME
TRANSACTION MANAGER <-------------------->
Access Methods <--------------------> Buffer Manager
Non Select
Statment Select Statment
B- Transaction manager
4 - Buffer Manager -> manages the main functions of the following modules
* Plan Cache -> two things can happen
1- That the plan query
exists in cache, then only that is used
2- That the plan query
does not exist in cache
* Data Parsing: Buffer cache & Data storage ->
Buffer manager provides access to the necessary data.
Below are two possible
approaches depending on whether data exists in the data cache or not
* Buffer Cache – Soft Parsing:
searches the buffer for data in the data cache. This is faster.
* Data Storage – Hard Parsing:
If data is not present in Buffer Manager, the required data is searched in Data
Storage.
* Dirty Page: Stored as
Transaction Manager processing logic.
5- Transaction log (related to transaction manager)
