SQL Server Architecture Diagram

Hey guys, In this blog I am going to explain you about SQL Server Architecture Diagram 

In almost every SQL DBA interview, this is the question which thrown as a hard ball from interviewer.

Below is high level SQL Server Architecture Diagram which is hard to explain in telephonic interview as well as face to face some time.

So I have divided this in three broader category which can be explain easily...

1. Protocol Layer
2. Storage Layer
3. OS Layer 

1.  Protocol Layer

 

A.       Connection Protocol –                                                                                                 

Connection Protocols are of three types –

Shared Memory - MS SQL server provides SHARED MEMORY PROTOCOL. Here CLIENT and MS SQL server run on the same machine. Both can communicate via Shared Memory protocol.

TCP/IP – MS SQL SERVER provides the capability to interact via TCP/IP protocol, where CLIENT and MS SQL Server are remote to each other and installed on a separate machine.

Named Piped – MS SQL SERVER provides the capability to interact via the Named Pipe protocol. Here the CLIENT and MS SQL SERVER are in connection via LAN.

A.       Query Processing – 

Query Processing calls Access Method. It provides an execution plan for data fetching logic required for execution. Once data is received from Storage Engine, the result gets published to the Protocol layer. Finally, data is sent to the end user.

2.   Storage Layer

A.       Lock Manager –

During Transaction, the associated data in Data Storage is in the Lock state. This 

process is handled by Lock Manager. This process ensures data consistency 

and isolation. 

B.       Transaction Manager – 

Transaction Manager is invoked when access method determines that Query is a non-Select statement.

C.       File Manager – 

Every database has one primary data file. We can give any extension for the primary data file but the recommended extension is .mdf.

Secondary data file is a file other than the primary data file in that database. Some databases may have multiple secondary data files. Some databases may not have a single secondary data file. Recommended extension for secondary data file is .ndf.

Log files hold all of the log information used to recover the database. Database must have at least one log file. We can have multiple log files for one database. The recommended extension for log file is .ldf.

D.       Buffer Manager – 

Buffer Manager looks for Data in Buffer in Data cache. If present, then this Data is used by Query Executor. This improves the performance as the number of I/O operation is reduced when fetching data from the cache as compared to fetching data from Data storage.

3. OS Layer

A.       Scheduler Manager –

A scheduler manager worker threads that require processing time to carry out work on behalf of tasks. Each scheduler is mapped to an individual processor (CPU). The time worker can remain active in a scheduler is called the OS quantum, with a maximum of 4 ms. After its quantum time expires, a worker yields it’s time to other workers that need to access CPU resources, and changes its state. 

B.   Memory Manager –

SQL Server dynamically acquires and frees memory as required. Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.

C.       Lazy Writer –

The Dirty page can remain in memory. When SQL server observes a huge load and Buffer memory is needed for a new transaction, it frees up Dirty Pages from the cache. 

D.       Deadlock –

A SQL Server Deadlock is a special concurrency problem in which two transactions block the progress of each other. The first transaction has a lock on some database object that the other transaction wants to access, and vice versa.

A.       I/O Manager –

Disk I/O operations by the buffer manager have the following characteristics:

·       All I/O are performed asynchronously, which allows the calling thread to continue processing while the I/O operation takes place in the background.

·       All I/O are issued in the calling threads unless the affinity I/O option is in use. The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs.

·       Multiple page I/O are accomplished with scatter-gather I/O, which allows data to be transferred into or out of noncontiguous areas of memory.