Database upper layer components

I have read the inside sqlserver2005 T-SQL query this book of people know the logic of the T-SQL execution order, I write this article is to let do the database optimization work of friends understand sqlserver upper components how to deal with T-SQL statements.

The database processing component is divided into query engine and storage engine.

1. on the network protocol layer, few of you know about these protocols. They are divided into four Protocols. After installing the sqlserver instance, you can see the following in the Configuration Manager:

(1) Memory Sharing Protocol (shared memory), which is enabled by default when you install the instance and is also the default pipe specified by sqlserver.

(2) Named Pipe (Named Pipes). The instance is used for LAN communication and can connect to the database through the server alias.

(3) TCP/IP, which is the most commonly used protocol. When you use SSMs to connect to a database, IP addresses are used to connect to the database. When you connect to a server, the default port number is port 1433 (unless you change the port number). In addition, if the server has multiple instances, IP \ Instance name is required unless you connect to the default instance.

(4) via, virtual local area network, which requires a dedicated channel.

2. At the TDS layer, this is also a form packet protocol that sends requests and receives data. It establishes a connection with the server and encapsulates the T-SQL command as a TDS packet sent to the server.

3, oledb protocol layer, this will execute the opposite thing with the TDS layer, first the TDS package unwrapped and interpreted as a T-SQL statement passed to the database engine. Here, the transfer of the T-SQL statement is complete.

4. query compiler, this will process the T-SQL statement, if the statement syntax is wrong, will return to the oledb layer error information.

5. the query analyzer accepts the query statements passed by the query compiler and searches for the query plan cache in the buffer zone. If yes, it directly passes the plan to the query executor, if not, a query plan tree is generated. each node in the tree is a T-SQL Execution step. Generate a query schedule and pass it to the query executor.

6. query the executor and execute the query. It will access the access method layer.

7. Access Method layer. This is an interface layer that provides interfaces for accessing data.

8. cache layer, including plan cache + data cache. when accessing data, the access method first obtains data in the data cache area. If there is no data in the data cache, read data from the hard disk to the data cache area, and then return it to the query executor. It can be said that all SQL server data operations are processed in the memory area. PS: when no queried data exists in the data cache memory area, the data will be read from the hard disk to the memory. This process is called a hard page error, which involves the memory hit rate.

Data Query Engine: Query compiler, query analyzer, and query executor.

Storage engine: access method, cache layer.

We are doing database optimization, usually 1. T-SQL statement optimization, 2. INDEX OPTIMIZATION, 3. Table Partitioning, 4. Table sharding, 5. read/write splitting, 6. hardware optimization. I am writing this article. I hope that you can understand these things and analyze what aspects can be used to optimize the database, so that you can have a wider vision.