Shulou(Shulou.com)05/31 Report--

This article to share with you is about the principle of SQL Server internal operation mechanism is what, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some gains, not much to say, follow Xiaobian to see it.

(i) Slow query

* , temporary tables, table joins, subqueries, etc. caused by query slow problem, can you solve it?

(ii) Memory leak

The following query for 8 minutes and 2 seconds, and then memory overflow, you know the problem?

SELECT * FROM BigDataTest

(3) I often hear the following concepts, can you solve them?

Transactions and locks (please refer to my other article: Talking about SQL Server transactions and locks (Part 1)https://www.cnblogs.com/wangjiming/p/8396986.html, ACID, isolation level, dirty read, separate tables and libraries, horizontal split, vertical split, high concurrency, etc.

SQL Server Architecture Abstract

Overview of SQL Server Architecture

SQL Server core architecture, roughly including six parts: client access tools, SQL Server Network Interface (SNI), relational engine, storage engine,

Disk and buffer pool. The following is an overview of SQL Server core architecture.

SQL Server Client Access Tools

SQL Server client access tools provide remote access technology, which is based on certain protocols with SQL Server servers, enabling them to remotely access databases as if they were operating locally, as we often do.

Microsoft SQL Server Management Studio。

SQL Server client access tools are more, of which the more popular ones are Microsoft SQL Server Management Studio and Navicat(Navicat is also more commonly used in MySQL). As for other tools, this article will not list them. Interested readers can inquire about them.

â-o February (2)

SQL Server Network Protocol, also known as SQL Server Network Interface (SNI), it is a bridge that constitutes the communication between the client and the server, and it can communicate with the SQL Server server based on a certain protocol. For example, we input a query statement SELECT * FROM BigDataTest on the client side. This statement can be parsed by the server only if the client and the server are based on a certain protocol. Otherwise, it is considered invalid.

SQL Server Network Protocol consists of a set of APIs that are invoked by SQL Server database engines and SQL Server native clients, such as implementing the most basic CRUD communications.

SQL Server Network Interface (SNI) only needs to configure network protocols on the client and server side. It supports the following protocols:

(1)shared memory

(2)TCP/IP

(3)named pipe

(4)VIA

(iii) Relationship engine

Relational engine, also known as query engine, its main function is responsible for processing SQL statements, its core components are composed of three parts: command analyzer, query optimizer and query executor.

(1)Command parser: responsible for parsing the T-SQL statement passed by the client, such as the client passing a SQL statement: SELECT * FROM BigDataTest, it will check the syntax structure of the statement, if the syntax

Error, it will return the error to the protocol layer, and then the protocol layer will return the error to the client; if the syntax structure is correct, it will generate a query plan according to the query command or find an existing query plan (first look in the buffer pool plan cache, if found, directly to the query executor execution, if not found, it will generate a T-SQL-based query tree, and then pass it to the query optimizer optimization)

(2)Query Optimizer: Responsible for optimizing the T-SQL query tree generated by the command parser (resource-based optimization, not time-based optimization), and then passing the final optimization results to the query executor for execution. Query Optimizer is based on

An optimizer for "resource cost," an algorithm that evaluates multiple executable query ways and selects the solution with cost *** as the optimization result, which is then output to the query executor to generate a query plan. Note that the query optimizer is "resource cost *** based" rather than "scheme *** based," that is, the final optimization result of the query optimizer may not be the *** scheme, but it must be the resource cost ** scheme.

(3)Query Executor: Responsible for executing queries. If the query executor receives the SQL statement SELECT * FROM BigDataTest from the command parser or query optimizer, it is passed through the OLE DB interface to the storage engine and then to the storage engine's access method.

(iv) Storage engine

Storage engine, essentially managing resource storage, its core components include three parts: access method, transaction manager and buffer manager.

(1)Access method: The access method is essentially an interface for the query executor to call (the interface provides all the code to retrieve the data, and the actual execution of the interface is performed by the buffer manager). If the query executor passes an SQL statement:

SELECT * FROM BigDataTest, after receiving the request command, the access method will call the buffer manager, and the buffer manager will call the plan cache of the buffer pool, find the corresponding result set in the plan cache, and then return it to the relational engine.

(2)Buffer manager: for access method call, manage buffer pool, query corresponding resources in buffer pool and return result set for access method to return to relationship engine.

(3)Transaction manager: mainly responsible for transaction management (ACID management) and high concurrency management (lock), it includes two core components (log manager and lock manager), lock manager is responsible for providing concurrent data access, setting isolation level, etc.; log manager is responsible for recording all access method operations, such as basic CRUD.

(v) Buffer pool

Buffer pool resides in memory and is a bridge between disk and buffer manager. In SQL Server, all resource queries are performed in memory, that is, in buffer pool. If buffer pool receives a SQL statement passed by buffer manager: SELECT * FROM BigDataTest, buffer manager data cache first from the disk database to meet the criteria of the result set, and then put in the buffer pool data buffer, and then returned to the buffer manager in the form of a result set, for access methods returned to the query executor of the relational engine, and then returned to the protocol layer, and then returned to the client. Note that the data in the buffer pool is operated on here, not the data in the disk DB, and the buffer pool data is not immediately written to the disk, so the query result is inconsistent with the result in BD, which is called dirty read.

Buffer pool mainly includes two parts: plan cache (generating execution plan is very time-consuming and resource-consuming, and plan cache is mainly used to store execution plan for subsequent use) and data cache (usually the capacity of cache pool ***, consuming memory ***, data pages read from disk can be called as long as they are placed here)

(vi) Disk

Disk is primarily used to store persistent resources such as log resources, database resources, and cache pool persistence support.

III. Complete process of a query

The following is a relatively complete query process, that is, the second part of the query statement: SELECT * FROM BigDataTest The whole process.

The above is what the principle of SQL Server internal operation mechanism is, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.