Microsoft SQL Server is a well known RDBMS & is used to manage large amounts of data.
The Database Engine is the core component of SQL Server.
The Database Engine stores data, process as per requirement and also prevent the data from external threads and vulnerability.
This article will give you the idea about various components of the SQL server / database engine, idea of internal processes and their work.
1. Query Optimiser:-
The responsible of query optimiser is to generate Optimised Execution Plan for the queries.
Query Optimiser analyses query and generates multiple execution plan which are evaluated on the basis of cost and performance characteristics.
After that Query Optimiser selects best Execution Plan & executes the query.
The optimiser considers available resources, workload, statistics and indexes during the estimation of query cost.
2. Query Processor:-
Query Processor executes all SQL queries & return results to end user.
When we run a query, goes through multiple phases like cmd parsing, optimisation and execution.
a) CMD Parshing:-
In Parshing Phase, it checks syntax & semantics of the query and in case of any error, throws message to the end user.
It is also very complex and secret part of SQL server.
This phase generates an optimised execution plan for the query.
Also known as "Cost-Based" optimiser. It means, Optimiser will check the best query plan which should be cost effective also.
Optimiser always tries to find best plan for the query to execute & fetch the result.
It works in three search phases:
i) 0th Phase - In this phase, looks for nested loop joins.
It stops if the cost of the plan is <0.2. The outcome plan from phase is known as transaction processing or TP Plan.
ii) 1st Phase - Looks for common patterns. It stops if the cost of plan is <1.0. The outcome plan from phase is known as quick plan.
iii) 2nd Phase - Looks at parallelism & indexed views.
c) Query Executor:- In this phase, it executes the query & fetches the data from database and returns the results to the end user.
During the execution of query, it uses query plan & takes help of storage engine.
3. Storage Engine:-
Storage Engine helps to store and retrieves data from storage devices. It also manages the data files and indexes which are used to store and organise data. Storage engine also manages concurrency which ensures multiple users can access & modify the same data without any conflicts.
It uses multiple algorithms to optimise data storage and retrieve data smoothly.
a) Access Method:-
Access method provides storage structure for our data & indexes and also provides interface to access the data as well as modify the required data.
Actually it sends request to Buffer Manager to fetch the required data.
b) Transaction Manager:-
Basically, transaction manager is having two components which are as follows:
i) Lock Manager - Manages concurrency for the data.
ii) Log Manager - It writes the changes in the transaction log which is also known as Write-Ahead Logging.
c) Buffer Manager:-
Buffer Manager handles buffer pool.
Suppose, we want to read few rows. Buffer Manager checks whether the required data is available in buffer pool/cache or not. If it is available, the required data are returned back to access method. Suppose the requested data is not available in buffer pool/cache, Buffer Manager fetches the data from the disk & stores it in the buffer pool/cache and returns the data to Access Method.
More coming Soon.....
