SQL Server 2008 Engine Components

First let's look at the engine components of SQL Server2008, SQLServer2008 have four components: protocols, relational engines, storage engines, and Sqlos.

Protocol layer (Protocol Layer)

When an application communicates with the SQL Server database engine, the application programming interface provided by the protocol layer uses Microsoft's custom tabular Data stream (TDS) package to standardize the communication format. The significance of this layer is to provide an interface to the application to access SQL Server.

SQL Server Network Interface (short SNI)

SNI is a protocol that establishes a network connection between the server and the client, providing a set of API functions that are used by the database engine and SQL Server clients. SNI replaces the Net-libraries components and MDAC components under SQL Server2000.

SQL Server supports four protocols such as shared memory (shared memory), TCP/IP, Named pipes (Named pipes), Virtual interface adapters (dummy Interface Adapter, via).

Once the connection is established, SNI creates a secure connection to the server's TDs breakpoint, which is used to request and return the data.

Table Format Data Flow endpoint (tabular, TDS)

TDS is a Microsoft private protocol that interacts with the database, and SQL Server creates an endpoint for each of the four protocols it supports when it is installed, and if the protocol is activated, then all users can use the protocol. There is also an endpoint specifically set for the dedicated administrator connection (DAC).

An SQL statement is sent to SQL Server as a TDS message over a TCP/IP connection.

Protocol layer (Protocol Layer)

Once the protocol layer receives the TDS packet, it works in a reverse reconciliation to find the included request. The protocol layer is also responsible for packaging the results and status messages and returning the client in the form of TDS messages.

Relational engine (relational Engine)

The relational engine becomes the query processor, including the SQL Server component that determines what the query is doing and how best to do the operations. The relational engine is also responsible for executing the query when requesting data from the storage engine and processing the returned results.

Command parser (CMD Parser)

The command parser handles T-SQL language events that are sent to the server. It first checks the T-SQL syntax and returns any error message client, and if the syntax is valid, it further generates an execution plan or looks for an existing execution plan. The command parser emits a matching demand from the T-SQL hash value to the plan cache in the buffer pool to check for the existence of the execution schedule and, if not, to translate T-SQL into an internal format that can be executed, that is, the query tree.

Query Optimizer (Optimizer)

The query optimizer obtains the query tree from the command parser and prepares it for its actual execution.

The first step in building an execution plan is to normalize each query, which is likely to break down a single query into multiple granularity-appropriate queries. Then optimize, the SQL Server query optimizer is cost-based, and it chooses the lowest-cost execution plan, using some internal metrics (memory requirements, CPU utilization, and number of I/O requirements) as a basis for selection. Additionally, the query optimizer considers the type of the request statement, examines the amount of data in the affected tables, indexes in the table, and SQL Server statistics.

SQL Manager

The SQL Manager is responsible for managing transactions related to stored procedures and their schedules, and for managing the automation parameters of queries.

Database Manager

Database Manager manages the access to metadata that is required for query compilation and query optimization.

Query Executor (Executor)

The query executor runs the execution plan generated by the query optimizer, just as the dispatcher is responsible for scheduling all the commands in the execution plan.

Storage Engine (Storage Engine)

The storage engine includes access methods, transaction management, and buffer manager.

Access method (Access Methods)

The access method code is invoked when SQL Server needs to locate the database. It provides a set of code to create and request scans of data pages and index pages, and to return the prepared OLE DB data rowset to the relational engine. The access method does not actually operate, it is only responsible for making requests to the buffer manager.

Transaction manager (Transaction Manager)

The transaction manager includes two components: Log manager and lock manager.

The lock manager is responsible for concurrent protection of data and management based on specific isolation levels. The Log manager is responsible for recording the transaction log in advance in the log file, thereby protecting the data.

Buffers Manager (Buffer manager)

The buffer manager is used to manage the distribution of data pages in the buffer memory pool.