SQL server Architecture: SQL Server Architecture is defined by a Client Which initiates or open a connection to SQL Server using a SQL Query − SQL query which is high level language. The connection to SQL Server involves Network protocols like TCP/IP used for connections which are in WAN connectivity to the Server, Shared memory is used for local connections, Named pipes is used for connections which are in LAN connectivity.
SQL Server is the server where SQL Services are installed and databases is hosted.
Architectural components which makes SQL Server work.
SQL OS lies in-between the Windows OS and SQL Server. All the activities performed on database engine are executed using SQL OS. SQL OS handles the memory management of SQL like buffer pool, log buffer. It also handles deadlock detection using the locking structure.
contains a Query parser (check syntax of the query), optimizer (prepare the best execution plan) and executor (the query is executed with the help of execution plan). The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables and tables which are distributed across multiple servers. Below diagram from microsoft page, covers how SQL Server processes queries and optimizes query reuse through execution plan caching.
3. Storage Engine
Storage of databases can be done on Local disk, SAN, etc.
Checkpoint is an internal process that writes all dirty pages (modified pages in Buffer Cache) from Buffer Cache to Physical Database Disks. It also writes the log records from log buffer to physical Database log file. This process is called as Hardening of dirty pages. Checkpoints can be both Automatic or manual or internal (Shutdown or alter database commands like recovery model changes)
5.Lazy Writer Process
Pushes dirty pages to disk to free up memory in the buffer pool unlike Checkpoint. This happens when SQL server comes under memory pressure.If Lazy Writer normally indicates memory bottleneck.
Some of other important SQL Server Architecture components are covered below
SQL Server Memory Architecture
Windows memory contains Virtual Address Space which is shared by Kernel mode (Operating system) and User mode (Applications like SQL Server). SQL Server “User address space” is broken into two regions: MemToLeave and Buffer Pool which are determined by SQL Server during startup.
is a very key component in achieving high I/O efficiency by storing data in memory/ buffer pool to reduce database file I/O. The buffer cache (also called as data cache) and procedure cache are two key components in SQL Server.
- Buffer cache holds the data pages in memory for faster data access from memory.
- Procedure cache stores the query execution plans to minimize the number of times that query plans have to be generated.DBCC PROCCACHE statement is very helpful in finding Procedure Cache content.
- Log cache − Similar to Buffer cache, Log cache stores log pages in memory before writing to disk.
SQL Server File Structure:
Database files are grouped together in File groups for administration purposes. No Data file can be a member of more than one file group. Log files are never part of a file group. There are two types of file groups in SQL Server which are Primary and User-defined. Primary file group contains the primary data file and any other files not specifically assigned to another file group.
Databases have three types of files – Primary data file, Secondary data file, and Log file.
Every database in SQL Server has one primary data file which acts as pointers to the other files in the database. The recommended extension for this file is .mdf.
Secondary data file is a file other than the primary data file in that database. We can also have multiple secondary data files. The recommended extension for secondary data file is .ndf.
Log files store the log information which is used to recover the database. Database must have at least one log file. The recommended extension for log file is .ldf.
The location of all the files in a database are recorded in both master database of SQL Server and also as mentioned above in the primary file for the database.
Files can have both Logical name which is useful T-SQL statements. Physical name is the OS_file_name. Data and Log files can be placed on either FAT or NTFS file systems.
The limitation is 32,767 files maximum in one database.
Extents are basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages or 64KB. SQL Server has two types of extents – Uniform and Mixed. Uniform extents are made up of only single object. Mixed extents are shared by up to eight objects.
It is the fundamental unit of data storage in SQL Server. The size of the page is 8KB. The start of each page is 96 byte header used to store system information such as type of page, amount of free space on the page and object id of the object owning/owner of the page.
There are around 9 types of data pages in SQL Server.
- Data − Data rows/entries.
- Index − Index entries.
- Test or Image − Text, image and ntext data.
- GAM − holds Information about allocated extents.
- SGAM − holds Information about allocated extents at system level.
- Page Free Space (PFS) − page free space information.
- Index Allocation Map (IAM) − holds Information about extents used by a table or index.
- Bulk Changed Map (BCM) − holds Information about extents modified by bulk operations since the last backup log statement.
- Differential Changed Map (DCM) − holds Information about extents that have changed since the last backup database statement.
SQL Server Log File Architecture
The SQL Server transaction log holds log records which are identified by Log Sequence Number (LSN). Each log record contains the ID of the transaction that it belongs to.
Database Log File is very helpful in recovery of database to roll the logical operation forward that is the operation is performed again or To roll the logical operation back, the reverse logical operation is performed.
The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file.
It is recommended to assign log files a size close to the final size required by database. SQL Server uses a write ahead log (WAL) which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties of SQL Server transaction.
Please also read my SQL Server Architecture article here.