Hello Guys,
To learn any technology, we should clear
about basics of technology, other knowledge we can achieve with experience and
by handling different issues. Same principle applies to SQL server if you know
architecture of SQL server, whether you are SQL developer or DBA you can take
correct decision quickly when you are handling any issue.
In this post, I have tried to explain SQL server
architecture in detail which will help you to understand SQL server more. So
let’s begin...
Above is the basic diagram of SQL
architecture. We can divide SQL engine in 2 parts – Relational engine which
perform SQL OS operations and storage engine which process data at actual
storage level.
1) Relational Engine:
Relational engine performs
SQL OS operations means, this part of SQL engine where actual SQL server
transactions received and operated.
Relational engine contains
below blocks or operators.
a) Network Protocol layer:
Here actual client – server communication happens at
default port 1433. For this Microsoft has developed API called SNI (SQL server
Network Interface). Communication happens in the form of TDS (Tabular Data
String developed by Sybase and later adopted by MS), means whatever query
client execute it gets converted in TDS and transferred to SQL. Same for SQL
server whatever data derived converts in TDS and transfers to client.
Now SQL server have 4 types of network architecture/protocols:
I. Shared Memory: This the default protocol used to connect to a client
residing locally (i.e. on the same machine).
II. TCP/IP: This protocol helps us to connect to the SQL server over the
network via an IP address and a port number. The default TCP port used by SQL
Server is 1433.
III. Named Pipes: Originally developed for LAN, this protocol is similar to TCP/IP,
but is efficient for slower networks like WAN. It uses port number 445.
IV. VIA: Virtual Interface Adapter, is a protocol used for high
performance communication between two connected systems, with the help of
specialized hardware on both ends.
b) Command Parser:
Network layer coverts query received as TDS into
language event and pass it to command parser. Command parser performs syntax
and semantic checks, if query is incorrect gives respective error back to the
client from error library which contains more than 50k defined errors. If query
is correct command parser passes it to Optimizer in the form of query tree.
c) Optimizer:
Optimizer is the most important part of relational
engine because it defines performance of query execution. When optimizer receives
query tree it checks all statistics and all execution plan available from plan
cache. If query is new it creates new execution plan and pass it to query
executer for execution.
d) Executer:
Executer executes query on storage engine with best
execution plan provided by optimizer and transfers derived data back to client.
Storage engine operates
actual physical data on disk/storage and memory.
Storage engine also
contains below operators/units.
a) Access Methods:
As we know SQL server works as write log ahead,
transactions which includes Update/Insert/Delete are written to log file via
log manager before writing it to data file. Where in Select, data is derived
direct from data file via buffer manager. So Access method checks whether
transaction contains SELECT or Update/Insert/Delete and accordingly route it to
buffer manager or log manager.
b) Buffer manager:
SQL server does not read or write data directly in disk,
it calls pages’ in memory and performs operation there this is called as hard
parsing. Sometimes data may present in data cache already due to recent
transaction so data directly read from memory this is called Soft parsing.
SQL server always wants to keep all data in Memory to
access easily, but it’s not possible due to limited RAM so periodically
checkpoint runs which moves dirty pages again to disk on LRU basis (Least
Recently Used pages).
Now there are two different functions performs similar
activity – Checkpoint and Lazy writer both moves pages from memory to disk but
there is below difference
Checkpoint is executed periodically with purpose to shorten database recovery
after crash down.
Where lazy writer
executed only when there is memory pressure with purpose to lower down pressure
on memory.
Buffer/cache also have some part reserved for plan cache
which optimizer use to refer recently used execution plans.
So buffer manager operates all these activities in
memory and manages transaction to use memory efficiently.
c) Log manager:
This manages logs and locks when every transaction written
to log file.
d) Log file:
Log file keeps logging every Update/Insert/Delete
transaction and it gets truncated after checkpoint or log file space usage
reaches to 70% if database in simple recovery mode, where after log backup in
full recovery mode.
Each transaction is written sequentially on log file as
record and sequence is maintained with LSN (Log Sequence Number).
Log file is logically divided into small VLFs (Virtual
log files) We can’t predict size of VLFs but number of VLFs can predict when
log file created according to size of log file as below.
|
Size of log File
|
Number of VLFs
|
|
Less than 1MB
|
2
|
|
1 MB to 64 MB
|
4
|
|
64 MB to 1 GB
|
8
|
|
More than 1 GB
|
16
|
Later number VLFs can varied according to size of auto
growth set for log file and transactions. VLFs is topic could be studied in
more details but it’s not part of our post for now so just described at basic.
e) Data file:
Data file is where actual data stored on the disk, where
basic unit where data stored is PAGE of 8KB size. 8 Pages are logically grouped
together and called as extent which is size of 8x8KB = 64 KB.
Now there are two types of extent mixed extent and
uniform extent.
Pages in the mixed extent are shared by two or more
different objects (tables or indexes) where pages in uniform extent are used
only for one object.
For better performance uniform extent is always better.
Till SQL version 2014 it could be controlled with trace flag 1118. Enabling
trace flag starts using uniform extent. From SQL server 2016 onward uniform
extent is enabled by default.
Below is the structure of page
Header:
Out of 8KB in page initial 96 bytes are reserved for
system information of page like page number, page type, allocation ID etc.
Row offset:
Row offset’s each unit is of 2 bytes and added at the
end of pages. One entry of row offset mapped to one row on page and each row
offset gives information how far 1st byte of each row is from start
of page.
Data
Other part o page contains respective page data.
Below are the types of
pages which we will discuss in another post.
- Data page
- Index page
- LOB (Large Object pages) for text or image
- GAM (Global Allocation Map)
- SGAM (Shared Global Allocation Map)
- PFS (Page free space)
- IAM (Index Allocation Map)
- BCM (Bulk Change Map)
- DCM (Differential Change Map)
No comments:
Post a Comment