Microsoft SQL Server Query Processor internal mechanism and structure

Summary: This article describes how Microsoft SQL Server queries are handled on a client computer, how various clients interact with SQL Server, and what SQL Server needs to do to handle requests from client programs.

Brief introduction

The Microsoft (R) SQL Server (TM) internal mechanism and structure is a very large topic, so this article is limited to issues of interest to program developers and focuses on issues not thoroughly discussed in other sources. When discussing the structure of SQL Server, we mainly look at the processing of clients, study how different client programs interact with SQL Server, and how SQL Server handles requests from clients. There are other sources of information that discuss other aspects of SQL Server, particularly Inside SQL Server 7.0, published by Microsoft Press, and the author is Ron Soukup and Kalen Delaney, which discusses SQL Se in great detail RVer the internal mechanism and processing methods of the storage engine, but the query processor discussion is not deep enough. This article is filling this gap.

We expect this article to help readers write better applications. Through this article, the reader will be able to improve the performance of the program to get a new inspiration, to create a new understanding.

SQL Server is a client/server system

For years, SQL Server has been considered a client/server system. In fact, Sybase DataServer, which developed the original SQL Server, was the first commercial relational database system developed as a client/server system. So what does that mean? This does not just mean that SQL Server is a two-tier system. Traditionally, a two-tier system means that the client application is running on one machine and sending requests to a server on another computer. For SQL Server, the client/server means that the component of SQL Server, the client API section, resides at the far end of the processing structure and is separate from the server component itself.

In a typical two-tier model, the client program resides on the desktop, has a large number of client application logic and business logic, and makes a direct request to the database system. The client then gets the data returned by the server in response to those requests.

The same model is used in the three-tier system. For years, SQL Server has been used in transactional monitoring systems, such as BEA's Tuxedo and Compaq's acmsxp, which used a typical three-tier model as early as 20 or 30 years ago. The three-tier model dominates today's web-based application system, which is represented by the Microsoft MTS and the new COM + 1.0. From a SQL Server perspective, the client program in a three-tier solution is placed on the middle tier. The middle tier interacts directly with the database. The actual desktop, or thin client (Thin client), uses other mechanisms and typically interacts directly with the middle tier, rather than directly interacting with the database system. 

Client structure

From a structural point of view, the SQL Server relational servers component itself does not really care about where the client program runs. In fact, SQL Server is still a client/server model, even if you run the application on the same machine running SQL Server. The server runs a separate multithreaded process that serves requests from clients, regardless of where the client is located. The client program code itself is a separate DLL running inside the client application, and the actual interface to SQL Server is the "Tabular data stream" (Tabular data Stream, TDS) protocol that talks between the client and the server.

A common question is "what is the native interface of SQL Server?" "For a long time, many developers have been reluctant to use an interface like ODBC because they think the client API developed by Sybase, or db-library, is the native interface of SQL Server." In fact, the SQL Server relational servers themselves do not have native APIs, and its interface is the communication flow protocol TDS between the client and the server. TDS encapsulates the SQL statements that the client sends to the server and encapsulates the processing results that the server returns to the client. Any API that deals directly with TDS is the native interface of SQL Server.