SQL Server 2012:SQL Server architecture--The life cycle of a query (part 1th)

Original: SQL Server 2012:SQL Server architecture--The life cycle of a query (part 1th)

To reduce the scope of the read operation, this article first looks at a simple select query and then introduces additional procedures related to performing the update operation. Finally, you will read that SQL Server uses the terminology and processes associated with the Restore tool when optimizing performance.

relational and storage engines

, SQL Server is divided into 2 main engines: the relational engine and the storage engine . The relational engine is sometimes referred to as the query processor because its main function is query optimization and execution. It contains a command parser that examines the query syntax and prepares the query tree; the query optimizer is undoubtedly the crown jewel in any database system; The query executor is responsible for execution (query plan).

The storage Engine is responsible for all data input and output management. It contains input and output requests for processing modifications such as rows, indexes, pages, allocations, and row versions , and the buffer manager , which deals with the SQL Server main memory user buffer pool . It also contains transaction managers that handle data locks used to maintain consistency (ACID) and to manage transaction logs.

Buffer Pool

The other major components you need to know before entering the query life cycle are the buffer pool, which is the largest memory user in SQL Server. The buffer pool contains all the different caches in SQL Server, including the plan cache and the data cache, and the plan cache is then referred to in the section after the query through its life cycle.

A simple select query

In this example, the query details used are not important-there is no simple SELECT statement for join, because you are simply initiating a simple read request. Starting with the client, where you first touch the component is SQL Server network interface (SNI).

SQL Server network Interface (SNI)

SQL Server network Interface (SNI) is the protocol layer that establishes client and server-side network connections. It consists of a series of APIs used by both the database engine and the SQL Server local client (SQL Server Native Client:snac). SNI represents the network library that was established in SQL Server 2000 and the Microsoft Data Access component that is included in the operating system (Microsoft Database access COMPONENTS:MDAC).

SNI is not directly configured; you want to configure the network protocol on both the client and server side. SQL Server supports the following protocols:

• Shared memory-simple, fast, shared memory is the default protocol used to connect to a local (same computer as SQL Server) to run the client. Can only be used locally, there is no configuration property, and is always the first attempt when connecting from a local machine.
• tcp/ip--This is the most commonly used access protocol for SQL Server. It allows you to connect to SQL Server by specifying the IP address and port number. Typically, it happens automatically when you specify a connection instance. Your internal name resolution system resolves the host name of the instance name to an IP address, regardless of whether you connect to the default TCP 1433 port for the default instance or use the SQL Browse service to locate the correct port for the named instance using UDP 1434.
• Named Pipes (Named Pipes)--tcp/ip and Named pipes (Named Pipes) are compatible protocols that are used in the schema. Named Pipes (Named Pipes) are developed for use in local area networks (LANs), but can also be used less efficiently for low-speed networking, such as wide area network (WANS).

Using Named pipes (Named Pipes) first needs to make it work for SQL Server Configuration Manager (SQL Server Config Manager) (if you want to connect remotely), and then create a use named pipe (Named Pipes) A SQL Server alias that is connected to the server as a protocol.

The Named pipe (Named Pipes) uses the TCP 445 port, and in any firewall between the 2 computers, make sure that the port is open, including Windows Firewall.

• The via--Virtual interface adapter (Vsan Interface Adapter:via) is a high-performance communication protocol between two systems that requires specially crafted hardware at both the terminal and dedicated connection ports.

As with Named Pipes (Named Pipes), using the VIA protocol first requires that it take effect for SQL Server Configuration Manager (SQL Server Config Manager) and then creates a virtual interface adapter that uses Interface Adapter:via) is a SQL Server alias that is connected to the server as a protocol. Although SQL Server 2012 also supports the VIA protocol, it will be removed from a later version because it is necessary to avoid this protocol being installed at the time of the new installation.

Regardless of which protocol is used, once the connection is established, the SQL Server network interface (SNI) creates a full connection on the server with the tabular Data Flow (TDS) endpoint (described below), which is used to send requests and receive data. The purpose of following a query through its life cycle here is that you send a SELECT statement and wait for the result set to be received.

Tabular Data Flow endpoint (Tabular Data Stream (TDS) endpoints)

TDS is all Microsoft's first protocol designed by Sybase to interact with the database server. With a network protocol such as TCP/IP once the connection is connected, the link to the associated TDS endpoint is created, which acts as a communication point between the client and the server side.

Each network protocol has a TDS endpoint, and one is used by the private network connection (dedicated Administrator Connection:dac). Once connectivity is created, TDS messages are used as communication between the client and server side.

The SELECT statement is sent to SQL Server as a TDS message through the TCP/IP protocol (TCP/IP is the default protocol).

protocol layers (protocal layer)

When the protocol layer in SQL Server (protocal layer) receives your TDS package, it reverses the work of the SQL Server network Interface (SNI), unpacking the package to find out what the request contains. The protocol layer (Protocal layer) is also responsible for packaging the results and status messages and sending them back to the client as TDS messages.

Our SELECT statement is marked as a "SQL command" type message in the TDS package, as it passes to the next component, Command Parser (Parser), and begins the path to execution.

Shows where our query is now. On the client side, the statement is packaged in the TDS package by the SQL Server network Interface (SNI) and sent to the protocol layer (protocal layer) in SQL Server, where it is unpacked, identified as a SQL command, and this code is SQL The Server network interface (SNI) is sent to the command Parser.

Command Parser (Parser)

The role of the command parser (COMAMND Parser) is to process T-SQL language events (language events). It first checks the syntax and returns any syntax errors to the client through the protocol Layer (Protocol layer). If the syntax is valid, then the next step is to generate a query plan or find a plan that already exists. The query plan contains the details of how SQL Server will execute this code. It is often called the Execution plan (execution).

To check the query plan, the command parser (COMAMND Parser) generates a hash of T-SQL (hash) and checks the plan cache to determine if an appropriate plan already exists. The plan cache is the area in the buffer pool that is used to cache the query plan. If a match is found, the plan is read from the cache and passed to the query executor (Executor) to execute. (the next article will describe what happens if a match is not found.) ）

SQL Server 2012:SQL Server architecture--The life cycle of a query (part 1th)