Below are the 5 must know components/features of SQL Server Architecture as per me. To set the expectations clear about this post, in this article, I have given a brief overview of few components of SQL Server architecture, also note that each of these components may have several sub-components too but those are not covered here. To get in-depth knowledge of architecture I recommend going through MSDN website and other useful websites online. The main motto of this article is to cover few concepts of SQL Server Architecture that are asked in most interviews.
Storage Engine:
Its role is to store and retrieve data in database files. Every database will have minimum 2 types of files ‘.mdf (data file)’ and ‘.ldf (log file)’. Database files are for storing data and log files are for write-ahead logging.
The basic storage unit of SQL Server is called as ‘Page’, each page will be of 8KB size. ‘Extent’ is a group of 8 pages, so each extent would of 64KB size. Data inserted into a database will be stored in these pages. Here one thing to note is different types of data are stored in different pages ex: images, XML data are stored in LOB pages and so on;
Relational Engine:
It is also called as Query Processor. To put in simple terms this component determines the best way to execute the queries, executes them and returns back the result to the user. To execute the query it always tries to use the cheapest way i.e.., it tries to execute the queries by using minimal resources. It is a cost-based optimizer, so for every query before executing it, it determines what are possible ways of executing it and estimates the cost of each way and select the cheapest way among all.
Buffer Management:
It performs two mechanisms: the buffer manager to access and update database pages, and buffer cache (also called buffer pool), to reduce database file I/O. So basically buffer manager reads data pages from the disk and places them in the buffer cache(buffer pool) and if any modifications are done to the page which is already in buffer cache then buffer manager writes back those pages to the disk. Any page will remain in buffer cache as long as there is enough free space to accommodate new pages in it, if there is a need to copy new data pages to buffer cache and there is not enough space then the existing pages are flushed back to disk based on LRU algorithm. Only if the data of a page is modified they are written back to disk.
Lazy Writer:
Lazy writer is a process that keeps monitoring the free space available in Buffer Cache. If at any time there is not enough free space in buffer cache ‘Lazy Writer’ writes the dirty pages in the buffer cache to the disk based on LRU(Least Recently Used) algorithm. Usually during every checkpoint the dirty pages in buffer cache are written to disk but in between 2 checkpoints if it is found that there is space issue or space needed for new pages to come then Lazy writer will clear the dirty pages from buffer cache and writes them to disk.
SQLOS:
It is a layer between SQL Server and Windows OS. It takes care of tasks like scheduling, memory management, buffer management, deadlock detection, exception handling, extended events and I/O within SQL. This provides an API (Application Program Interface) to different layers when required by the operating resources. In simple terms, SQLOS manages the resources within SQL Server same way a Windows OS does in general.
Please add in the comment section the other important components of SQL Server Architecture according to you, I will try to include them.
No comments:
Post a Comment