This is the 27th day of my participation in Gwen Challenge.

Hello, I am Wukong.

Star: github.com/Jackson0714… Personal website: www.passjava.cn

Database topics

  • Why NoSQL when you have MySQL?
  • TempDB usage and performance issues
  • Table expressions (1)
  • Table expressions (2)
  • Explain SQL set operations in detail
  • Explain SQL single table query
  • Explain join queries in SQL
  • Explain subqueries in SQL
  • Explain SQL set operations in detail
  • Explain table value parameters in SQL

A, SQL Server components

1. Relational engine: the main function is to optimize and execute queries.

Contains three components:

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

(2) Query optimizer: optimize the query.

(3) Query executor: responsible for executing the query.

2. Storage engine: Manages all data and related I/OS

Contains three components:

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

(2) Data access methods: processing I/O requests for rows, indexes, pages, row versions, space allocation, etc.

(3) Buffer manager: Manages the Buffer Pool, the main memory consuming component of SQL Server.

3.Buffer Pool

Contains all caches for SQL Server. Such as schedule caching and data caching.

4. Transaction logging

Log all changes to the transaction. An important component that ensures the ACID properties of a transaction.

5. Data files

The physical storage file of the database.

6.SQL Server network interface

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

Second, the underlying principle of query

1. When the client executes a T-SQL statement to the SQL Server Server, the system first reaches the network interface of the Server. There is a protocol layer between the network interface and the client.

2. Establish a connection between the client and the network interface. Use a Microsoft communication format called tabular Format Data Flow (TDS) packets to format communication data.

3. The client sends the TDS packet to the protocol layer. After receiving the TDS package, the protocol layer decompresses and analyzes what requests are contained in the package.

4. Command parsers to parse T-SQL statements. The command parser does several things:

(1) Check grammar. Any syntax errors are returned to the client. The following steps are not performed.

(2) Check whether there is a cache of execution plans for T-SQL statements in the Buffer Pool.

(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, the query executor will optimize and generate the execution plan, which will be stored in the Buffer Pool.

5. The query optimizer optimizes SQL statements

If there is no execution plan for the SQL statement in the Buffer Pool, the SQL statement needs to be sent to the query optimizer to analyze the SQL statement through certain algorithms and generate 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. The query executor performs the query

Query the data access method by which the executor passes the execution plan to the storage engine through the OLE DB interface.

7. Data access method generates execution code

The data access method executes the code planned to generate SQL Server actionable data, without actually executing the code and passing it to the buffer manager for execution.

The buffer manager reads the data.

The presence of such data is checked in the buffer pool’s data cache, and if so, the result is returned to the storage engine’s data access method. If not, the data is read from the disk (data file) and put into the data cache, and the read data is returned to the storage engine’s data access method.

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

10. The data access method of the storage engine returns the query result to the query executor of the relationship engine.

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

12. The protocol layer encapsulates data into TDS packets, and then sends the TDS packets to the client.

References:

Msdn.microsoft.com/zh-cn/libra…

“The Art of SQL Server Performance Optimization and Management”

About the author: Wu Kong, 8 years of experience in Internet development and architecture, explains distributed, architecture design, Java core technology with stories. “JVM performance optimization practice” column author, open source “Spring Cloud practice PassJava” project, independently developed a PMP brush small program.

I am Wukong, strive to become stronger, become super Saiya people!