SQL Server Architecture
SQL Server is Microsoft RDMS that works on a client-server architecture. Here, End-user sends a request and SQL Server accepts, process, and share the result with the user.
This seems a very simple process from frontend however there are multiple processes runs in the background to fulfill this request. Microsoft SQL Server Architecture is a very complex internal mechanism with 3 major components.
- Network Protocols (SNI – SQL Server Network Interface)
- Database Engine
- Storage Engine
- Relation Engine
SQLOS
- Network Protocols (SNI – SQL Server Network Interface): SQL Server Network Interface is a network protocol layer to connect DB instance. SQL Server Supports 3 protocols for network connectivity and VIA is a hardware-based obsolete protocol. You will found VIA in new SQL versions. You can enable the required protocol from the SQL Server configuration manager.
Shared Memory: Shared Memory is the simplest protocol and required ZERO configuration. This works on the same machine on which SQL Server is installed, no client-server communication using share memory is possible.
TCP/IP: TCP/IP is the most widely used protocol for client-server connectivity. You need to enable TCP/IP protocol from the SQL Server Configuration Manager. By default, SQL Server runs on 1433 TCP port however you can change as and when required.
Named Pipes: Names Pipe protocol is designed for LAN (Local Area Network). You need to enable Named Pipes from the SQL Server Configuration Manager. Names Piped can be used for local and remote systems on the same LAN. SQL Server can be connected using named pipe by mentioning for the default SQL Server instance is \\.\pipe\sql\query, and \\.\pipe\MSSQL$<instancename>\sql\query .
- Database Engine: Database Engine is the core of SQL Server architecture, this provides a layer between Client connecting using the available protocol and SQL OS (the core internal of SQL). DB engine represents the logical architecture of the SQL server which consists of or works with physical architecture and relation engine to execute user requests.
Logical architecture is Tables, Indexes, Views, Store Procedures, Functions, Triggers, and other logical entities that group data for user representation.
- Storage Engine: Storage Engine consists of Physical Database architecture along with access and buffer manager. A storage engine is responsible for physical data storage and access as and when needed.
- Physical Database architecture is a description of how actual data is stored in SQL. This has 2 layers. In Layer 1, Data in SQL server stored in a database that resides on files at the OS files system. Database files are divided into data and log files. Data files consist of data\ index\ views and log file consist of transaction-level details for recovery perspective.
Types of Database files:-
Primary– The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
Secondary– Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended file name extension for secondary data files is .ndf.
Transaction Log– The transaction log files hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.
Each file has 2 names, a logical name used by SQL Server to identify the file and OS file name which is the physical file name on OS drive. Both can be identical or different.
In Layer 2, This is the smallest layer which represents Pages and Extents storage of database files at the disk level.
Extents are a combination of 8 pages (8X8KB) of size 64KB. This is the initial storage unit assign to table\index. Extents are of 2 types, Uniform extends which assign to single objects and Mixed extends which is shared by multiple objects(maximum by 8).
Pages are the smallest storage unit in SQL Server of size 8KB. Each page reserved 96 bytes for system information and the rest of the data. We have 9 types of pages in SQL Server.
- Data – Saves only Data excluding ntext, text and images
- Index − Index Details
- Text\Image − ntext, text, and images
- GAM – GAM means Global allocation Map saves Information for allocated extents
- SGAM − Shared Global Allocation Map saves Information for allocated mixed extents
- Page Free Space (PFS) – Saves Information about free pages
- Index Allocation Map (IAM) – Details of extents used for table or index.
- Bulk Changed Map (BCM) – BCM pages used to save extent details modified by bulk operations since last log backup
- Differential Changed Map (DCM) − DCM pages used to save extent details modified since last differential backup
- Access Methods: Access methods are ways to access data in SQL Server. In Technical terms, Access methods are the type of commands used to access data and desired results.
DDL: Data Definition Language to create, alter and drop DB objects
DML: Data Definition Language to select, insert, modify and delete data
- Buffer Manager: Buffer Manager is one of the critical aspects of Database system performance. Any user request or data processing cannot work directly on storage. Data needs to move in the buffer for any changes and access. Buffer consist of:-
- Plan Cache – It saves executing plans of executed queries for future references
- Data Pages – Data Pages available in buffer for user requests. This can unmodified pages and dirty pages. Dirty pages are one which got modified in the buffer but did not write back to disk.
- Buffer Pool – Cache used for data processing and query execution
- Transaction Services: Transaction Services works between data storage and a transaction log file(.ldf). It controls transaction details and modified data logging to ensure recovery by dealing with transaction file (.LDF)
- Lock Manager: Each data access and modification required adequate lock on data. These data lock needs to be upgraded to the next level or released as and when required by the system based on user request. Lock Manager governs this process and helps in maintaining consistency and isolation.
- Relation Engine: Relation engine is responsible for evaluating user requests \ SQL Commands and performs execution. As described in the image, any SQL Query needs to go from multiple processes for execution.
- Parser – Parse the query or command for syntax and T-SQL
- Algebrizer – Resolve all object names and bind them
- Query Optimizer – Generate execution plans on the basis on stats and chose best one for execution
- Query Execution – Actual query execution. Actual execution plan may change now and same will be stored in the plan cache for future reference
- Query output – Output will be shared with the user
- SQL OS: SQL OS was introduced n SQL 2005. Before SQL 2005, SQL Server was preferred only for small and medium load applications. Microsoft enhances SQL server to handle High-end enterprise DB level with SQL 2005. SQL OS is a layer between the SQL Server DB engine and the Windows Operating system.
Why Microsoft feel the need for SQL OS?
We know Windows OS is the layer with end-user and hardware. OS is an abstract layer with convert user requests in hardware singles and makes execution. SQL OS is similar to Windows OS. Microsft feels the SQL engine needs a layer between the SQL engine and OS to deals with Memory Management. scheduling, threading, NUMA in a more controlled way with windows OS.
Primary Functions of SQL OS:
- Thread Scheduler: SQL OS is responsible for scheduling CPU thread for SQL operations
- Synchronization Services: SQL is a multithreaded application. SQL OS governs synchronization between threads
- I/O Manager: I/O is time taking process as it depends on disk speed and type. SQL OS keeps monitoring I/O operations and signal threads when completed
- External API: SQL Server provides a feature of controls and working with CLR (DLLs) and MDAC (Open Data Source Connections). SQL OS is responsible to manage it.
- Thread Management: SQL OS Control Lazy write process to maintain free pages in buffer. This is the primary function for buffer management. SQL OS also responsible for Monitor, Detect, and Management of deadlocks.
Reference: Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN:-
Subscribe YouTube Channel Decode ITES
http://www.facebook.com/mssqlfun
Other Linked Profiles:-
