"T-SQL Advanced" 02. Understanding the underlying principles of SQL queries

This series of "T-SQL" is mainly for T-SQL summary.

T-SQL Basics

"T-SQL Basics" 01. Single-Table query-several SQL query questions

"T-SQL Basics" 02. Join Query

"T-SQL Basics" 03. Subqueries

"T-SQL Basics" 04. Table Expression-Previous

"T-SQL Basics" 04. Table Expression-Next

"T-SQL Basics" 05. Set operation

"T-SQL Basics" 06. Perspective, inverse perspective, grouping set

"T-SQL Basics" 07. Data modification

"T-SQL Fundamentals" 08. Transactions and concurrency

"T-SQL Basics" 09. Programmable objects

T-SQL Advanced

"T-SQL Advanced" 01. Easy-to-use SQL tvp~~ exclusive [add-delete-change-check] Example

"T-SQL Advanced" 02. Understanding the underlying principles of SQL queries

T-SQL Performance tuning

Usage and performance issues with "T-SQL performance Tuning" 01.TempDB

Keep updating ... Welcome to follow me!

I. Components of SQL Server

1. Relational engine: The primary role is to optimize and execute queries.

Contains three main components:

(1) Command parser: Check syntax and transform query tree.

(2) Query executor: Optimize the query.

(3) query optimizer: Responsible for executing the query.

2. Storage Engine: Manage all data and the IO involved

Contains three main components:

(1) Transaction manager: Manages data through locks and maintains the acid properties of transactions.

(2) Data access method: Handles I/O requests for rows, indexes, pages, row versions, spatial allocations, and so on.

(3) Buffer Manager: Manages the primary memory consumption component of SQL Server, buffer Pool.

3.Buffer Pool

Contains all the caches for SQL Server. such as plan caching and data caching.

4. Transaction log

Records all changes to a transaction. An important component that guarantees the ACID properties of a transaction.

5. Data files

The physical storage file for the database.

6.SQL Server network interface

The protocol layer that establishes the network connection between the client and the server

Second, the underlying principle of the query

1. When a client executes a T-SQL statement to a SQL Server server, it first arrives at the server's network interface, and there is a protocol layer between the network interface and the client.

2. Establish a connection between the client and the network interface. Use the Microsoft communication format, called a Tabular Data Flow (TDS) packet, to format the communication data.

3. The client sends a TDS packet to the protocol layer. After the protocol layer receives the TDS packet, it extracts and analyzes the request contained in the package.

4. The command parser parses the T-SQL statement. The command parser does several things:

(1) Check the syntax. A syntax error was found to be returned to the client. The following steps do not execute.

(2) Check if there is an execution plan cache in the buffer pool that should be a T-SQL statement.

(3) If a cached execution plan is found, it is read directly from the execution plan cache and transmitted to the query executor for execution.

(4) If the execution plan cache is not found, it is optimized in the query executor and generates an execution plan, which is stored in buffer pool.

5. Query optimizer optimized SQL statements

When there is no execution plan for the SQL statement in the buffer pool, it is necessary to pass the SQL to the query optimizer, parse the SQL statement with a certain algorithm, and produce one or more candidate execution plans. Select the least expensive plan as the final execution plan. The execution plan is then passed to the query executor.

6. Query Executor execution Query

The query executor passes the execution plan through the OLE DB interface to the data access method of the storage engine.

7. Data access methods generate execution code

The data access method executes the code that plans to generate SQL Server actionable data and does not actually execute the code, which is passed to the buffer manager for execution.

8. The buffer Manager reads the data.

This data is checked first in the buffer pool's data cache, and if present, returns the result to the storage engine's data access method, and if it does not exist, the data is read from the disk (data file) and placed in the data cache, and the read data is returned to the storage engine's data access method.

9. For reading data, a shared lock is requested and the transaction manager assigns a shared lock to the read operation.

10. The data access method of the storage engine returns the results of the query to the query executor of the relational engine.

11. The query executor returns the results to the protocol layer.

12. The protocol layer encapsulates the data into TDS packets and the protocol layer passes the TDS packet to the client.

"T-SQL Advanced" 02. Understanding the underlying principles of SQL queries