Brief architectural overview of several RDBMS - Microsoft SQL Server

  • Print
Details
Published on Saturday, 04 January 2014 17:53
Page 3 of 6

 

Microsoft SQL Server

microsoft sql server architecture

   When an application communicates with the SQL Server Database Engine, the application programming interfaces (APIs) exposed by the protocol layer formats the communication using a Microsoft-defined format called a tabular data stream (TDS) packet. There are Net-Libraries on both the server and client computers that encapsulate the TDS packet inside a standard communication protocol, such as TCP/IP or Named Pipes. On the server side of the communication, the Net-Libraries are part of the Database Engine, and that protocol layer.

   The database engine component has two subcomponents:

  • Storage Engine  - responsible for storage and retrieval of data;
  • Query Processor – includes the components of SQL Server that determine what your query needs to do and the best way to do it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

   The SQLOS layer lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine are taken care of by the SQL OS. It is a highly configurable operating system with powerful API (application programming interface). It enables automatic locality and advanced parallelism. SQL OS provides various operating system services.

   The query processor components are:

  • Command Parser - handles Transact-SQL language events sent to SQL Server. It checks for proper syntax and translates Transact-SQL commands into an internal format that can be operated on. This internal format is known as a query tree;
  • Query Optimizer - takes the query tree from the command parser and prepares it for execution. Statements that can’t be optimized, such as flow-of-control and DDL commands, are compiled into an internal form. The statements that are optimizable are marked as such and then passed to the optimizer. The optimizer is mainly concerned with the DML statement SELECT, INSERT, UPDATE, and DELETE, which can be processed in more than one way, and it is the optimizer’s job to determine which of the many possible ways is the best. It compiles an entire command batch, optimizes queries that are optimizable, and checks security. The query optimization and compilation result in an execution plan;

  • SQL Manager - responsible for everything related to managing stored procedures and their plans. It determines when a stored procedure needs recompilation, and it manages the caching of procedure plans so that other processes can reuse them.
    The SQL manager also handles auto parameterization of queries. In SQL Server 2008, certain kinds of ad hoc queries are treated as if they were parameterized stored procedures, and query plans are generated and saved for them    ;

  • Database Manager - handles access to the metadata needed for query compilation and optimization, making it clear that none of these separate modules can be run completely separately from the others. The metadata is stored as data and is managed by the storage engine, but metadata elements such as the data types of columns and the available indexes on a table must be available during the query compilation and optimization phase, before actual query execution starts;

  • The Query Executor - runs the execution plan that the optimizer produced, acting as a dispatcher for all the commands in the execution plan. This module steps through each command of the execution plan until the batch is complete. Most of the commands require interaction with the storage engine to modify or retrieve data and to managetransactions and locking.
Share