SQL Server layers run from the network protocols at the top to the files at the bottom. The diagram below shows the main ones: protocols, the query processor, the storage engine and SQLOS. It is a generic picture of the engine.
What Each Layer Does
Clients reach SQL Server through the external protocols: Shared Memory, Named Pipes, TCP/IP and the Virtual Interface Adapter (VIA). The database engine sits behind them and has two parts.
The query processor turns a query into a plan and runs it. It holds the parser, the optimizer, the SQL manager, the database manager and the query executor. The storage engine reads and writes the data. It holds transaction services and the file, buffer and lock managers. Its access methods cover rows, indexes, versions, pages and allocations. Utilities such as bulk load, DBCC and backup and restore live here too.
SQLOS sits under both parts. It provides the thread scheduler, the memory manager, the buffer pool and the I/O manager. Background worker threads, such as the lazy writer and the resource monitor, run on top of it.
Physical Architecture
The physical structure of a database is divided into the MDF and the LDF. The MDF part contains the file group, the data files, tables and indexes, extents and pages. The LDF file contains the transaction log.
Physical architecture is about how the data is stored in the file system. Pages, extents and database files all belong to it.
Logical Architecture
Logical architecture is about how the data is grouped and shown to the end user. It contains tables, constraints, stored procedures, functions and other objects.
Pages and Extents
The page is the smallest unit of storage in SQL Server, and it is 8 KB. An extent holds 8 adjacent pages. There are two types of extents: uniform and mixed.
A new table or index is usually allocated its pages from mixed extents. Once it grows past 64 KB, or 8 pages, its storage changes from mixed to uniform extents.
A layer is not a product, it is a job inside the engine.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
20 Comments. Leave new
Hi Pinal,
It seems like in all the SQL Server DBA interviews they ask can you explain the SQL Server Architecture. Dose that mean they are expecting to explain the above architecture is full i mean each component ??
Many Thanks,
Arun
hi pinal ,
just give me explanation about architecture diagram..
Hi Pinal,
Need to know more about the architecture from you.
Regards
Manish
Hi Pinal,
Could you briefly explain the above diagram.
Regards
Ramesh
I required explanation because i want it in my project
Hi Pinal Dave,
please give me explanation about each topic in above diagram.
Hi Pinal Dave,
please give me explanation about each topic in above diagram.
From
Tirumala
Hi Pinal,
Would request you to provide the brief explanation of the above SQL server architecture diagram.
Thanks & Regards,
Sarika
Hi Pinal,
I attended your today’s (Teched) session in Pune, and it was a very nice experience to be there. I learned so many new thing.
Thanks a lot for all your efforts for the community.
And I am looking forward to attend your upcoming session in coming future.
and once more I would like to request you to please write some more about about the above mentioned SQL Server architecture.
Thanks
Manish
Hi Pinal
i am working as a sql server developer.
u r blog will helps and give more stuff who are intrested to know about database.
how funny u see even i dodnt know sql server architecture
but i am working as a sql server developer
please give me explanation for the above architecture
Thanks Pinal thanks for u r blog
u konw u r the hero for our people, like me most of the people
we admire u
Hi Pinal,
Please give me the explaination of architecture diagram…
Thanks
shall we install sql server in linux
@Pavan, this is one of the drawback of Sql server. i.e., it doesn’t support any other OS except Windows Versions.
Hey Pavan,
No I believe, you can’t install SQL Server in linux.
Thanks
Manish
Vishwa
even i was asked in interview how query fetches data from Database engine
plz explain the above diagram..
For the folks who are interested in the ‘explanation’ of the above diagram, the diagram is straight (the only difference is above is colorful) from Kalen Delaney’s book “inside Microsoft SQL Server 2000” Chapter 3.1. Looks for the complete explanation there.
great prashant,
Pinal you have done great job .