SQL Server Architecture

  • Protocol level
  • Relational Motor
  • Storage motor

PROTOCOL LEVEL — SNI

TCP / IP

  • In SQL Management Studio — to connect via TCP \ IP the “Server name” parameter must be “Computer \ Server instance”.
  • SQL Server uses port 1433 in TCP / IP.

NAMED PIPES

WHAT IS TDS?

  • TDS stands for “Tabular Data Stream”.
  • All three protocols use TDS packages. TDS is encapsulated in network packets. This allows data to be transferred from the client machine to the server machine.
  • TDS was first developed by Sybase and now belongs to Microsoft.

RELATIONAL ENGINE

CMD PARSER

SYNTAX CHECK:

  • Like any other programming language, MS SQL also has a predefined set of Keywords. In addition, SQL Server has its own grammar, which is understood by the SQL server.
  • SELECT, INSERT, UPDATE, and many others belong to predefined lists of MS SQL Keywords.
  • CMD Parser performs syntax checking. If user input does not follow syntax or language grammar rules, it returns an error.
  • This is performed by the normalizer.
  • In its simplest form, it checks if the column name and the requested table name exist in the scheme. And if it exists, connect it to Query. This is also known as Binding.
  • The complexity increases when user queries contain VIEW. The normalizer performs the substitution with a view definition saved inside and many more.
    Let’s solve this with the example below –
  • At this stage, another execution tree is created where you can execute a query.
  • Note that all different trees have the same desired result.

STEP 0: SEARCH FOR A TRIVIAL PLAN:

  • This is also called the pre-optimization stage.
  • In some cases, there may be only one practical, workable plan known as a trivial plan. It is not necessary to create an optimized plan. The reason is that searching for more will lead to finding the same execution plan at runtime. This is also due to the additional cost of finding an optimized plan, which was not needed at all.
  • If no trivial plan is found, phase 1 begins.

STAGE 1: SEARCH FOR TRANSACTION PROCESSING PLANS

  • This includes finding a simple and complex plan.
  • Simple plan search: the past data of the column and index involved in the query will be used for statistical analysis. This usually consists of but is not limited to, one index per table.
  • However, if a simple plan is not found, a more complex plan is searched. It includes several indexes per table.

STEP 2: PARALLEL PROCESSING AND OPTIMIZATION

  • If none of the above strategies work, the Optimizer looks for possibilities of parallel processing. This depends on the capabilities and configuration of the machine.
  • If this is still not possible, the final optimization phase starts. Now, the ultimate goal of optimization is to find all other possible options to best fulfill the request. The final phase of optimization The algorithms are owned by Microsoft.

QUERY EXECUTOR

STORAGE ENGINE

FILE TYPES

  • Each database contains one main file.
  • All important data related to tables, views, triggers, etc., are stored here.
  • There is an extension. mdf usually but there can be any extension.
  • The database may or may not contain several secondary files.
  • This is optional and contains user-specific data.
  • There is an extension. ndf usually, but it can be any extension.
  • Also known as Recording in front of magazines.
  • There is an extension. LDF
  • Used to manage transactions.
  • It is used to recover from any unwanted instances. Perform the important task of reverting to uncommitted transactions.

ACCESS METHOD

  • Select the request (DDL)
  • Operator without choice (DDL and DML)
  • If the query is DDL, SELECT operator, the query is sent to Buffer Manager for further processing.
  • And if a DDL query, NON-SELECT operator, the query is transferred to the Transaction Manager. This mainly includes the UPDATE operator.

BUFFER MANAGER

  • Cache plan
  • Data parsing: buffer cache and data storage
  • Dirty page

CACHE PLAN

SYNTACTIC DATA ANALYSIS: BUFFER CACHE AND DATA STORAGE

TRANSACTION MANAGER

LOG MANAGER

  • The Log Manager tracks all updates made in the system through the transaction logs.
  • The journals have a sequential number of journals with a transaction identifier and a record of data changes.
  • This is used to track the completed transaction and rollback the transaction.

LOCK MANAGER

  • During a transaction, associated data in the data warehouse is in a locked state. This process is processed by the lock manager.
  • This process ensures consistency and isolation of data. Also known as ACID properties.

EXECUTION PROCESS

  • The Journal Manager shall start the registration and the Lock Manager shall block the related data.
  • The copy of the data shall be saved in the buffer cache.
  • A copy of the data to be updated shall be saved in the Car Registration Journal buffer and all events shall update the data in the data buffer.
  • The pages on which the data is stored shall also be called “dirty pages”.
  • Checkpoint registration and queue recording: This process starts and marks all pages from the dirty pages to the disk, but the page remains in the cache. The frequency is about 1 run per minute. But the page is first moved to the log file data page from the buffer log. This is known as entry to the journal.
  • Lazy writer: a dirty page may remain in memory. When an SQL server detects a huge load and a new transaction requires buffer memory, it releases the dirty pages from the cache. It runs on LRU, the smallest algorithm recently used to clear a page from buffer pool to disk.

SUMMARY:

  1. There are three types of client-server architecture: 1) Shared memory 2) TCP / IP 3) Named channels
  2. TDS, developed by Sybase and currently owned by Microsoft, is a package that is encapsulated in network packets to transfer data from a client computer to a server computer.
  3. The relational engine has three main components:
  • CMD Parser: it is responsible for syntax and semantic errors and finally generates a query tree.
  • Optimizer: the role of the Optimizer is to find the cheapest, not the best, and most economical execution plan.
  • Request Executer: The Request Executer invokes the access method and provides an execution plan for the data extraction logic needed for execution.
  • Access method: this component Defines whether the query is “Select” or “Not selected”. Calls Buffer and Transfer Manager respectively.
  • Buffer Manager: Buffer Manager manages the main functions for Plan Cache, Darling & Dirty Page.
  • Transaction Manager: This is a transaction manager without selections through log and lock managers. It also facilitates the important implementation of Write Ahead and Lazy Writers logging.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store