Polish your eyes to see the general introduction of SQLServer

Source: Internet
Author: User
Tags sql server query

Maybe you don't need to understand the internal mechanism of SQLServer, you can still complete CRUD, and it may be well done, maybe you don't need to study the architecture design of SQLServer, you can also design the architecture you need based on your project experience. Maybe you will say that constant demand changes have exhausted your energy, you may say that you do not need to have a deep understanding of SQLServer for the current project situation. Maybe you will also say that I like to study a product of a certain company, maybe you will think that SQLServer is too huge to start ......

But there are always some problems that will prompt us (developers who use SQLServer as the main storage data container) to begin to study SQLServer. For example, your database slows down as the data volume increases. For example, your database often fails inexplicably. For example, when you optimize and learn databases based on various lists on the Internet or books, they are found to be unreliable in many places. For example, when you optimize SQL statements, you will find that you do not understand the specific meaning of many SQLServer output information... Wait for a second message, and you need to polish your eyes to learn SQLServer systematically. Instead of simply looking at your eyes.

A friend in the garden wrote an article <do programmers need the underlying knowledge?>, in which he tried to open the box around him and thought it was quite reasonable. After studying SQLServer, you will find that such a well-designed product is worthy of our research. The following describes SQL Server in terms of the SQL Server product structure, SQL Server database engine structure, and SQL Server execution model.

I. SQLServer2008 Product Structure

The main reason for this introduction is that many people do not understand SQL Server products as a whole. The following sections mainly copy msdn, and some of them add their own understandings. Before the introduction, it is necessary to describe OLAP and OLTP. OLTP: online transaction processing. This is used in most database-based projects. It records the specific transaction of the system. OLAP, Online Analytical Processing. This is often closely related to BI. A simple understanding of this is one of the core technologies of BI. The following Analysis Services and Integration Services belong to the OLAP category.

 

Database EngineIs a core service used to store, process, and protect data. The database engine provides controlled access and fast transaction processing to meet the requirements of the most demanding data consumption applications in the enterprise. The database engine also provides a lot of support to maintain high availability. This component is the core and bottom layer of SQLServer, and other components depend on the database engine.

Analysis Services(Multidimensional data)You can design, create, and manage multidimensional structures that contain data aggregated from other data sources (such as relational databases) to support OLAP. Of course, you can implement multi-dimensional data without using this component, but it makes it more convenient and effective.

Analysis Services(Data Mining)This allows you to design, create, and visualize data mining models. By using a variety of industry-standard data mining algorithms, these mining models can be constructed based on other data sources. With this component, you can obtain effective information about enterprise decision-making from data.

Integration ServicesIs a platform for generating high-performance data integration solutions, including packages that provide extraction, conversion, and loading (ETL) Processing for data warehouses.

CopyIs a group of technologies used to replicate and distribute data and database objects between databases, and then perform synchronization operations between databases to maintain consistency. When using replication, data can be distributed to different locations and distributed to remote users or mobile users through LAN and WAN, dial-up connections, wireless connections, and the Internet. It can be used in the design of master-slave.

Reporting ServicesEnterprise-level Web reports allow you to create tables that extract data from multiple data sources, publish tables of various formats, and centrally manage security and subscription.

Service BrokerHelps developers generate secure and scalable database applications. This new database engine technology provides a message-based communication platform that enables independent application components to be executed as a whole. Service Broker includes a basic structure that can be used for asynchronous programming. This structure can be used for applications in a single database or in a single instance, or for distributed applications.

Ii. SQLServer2008 database engine structure

Obviously, the database engine is very complex, and we need to spend a lot of effort on details. According to the title, this article roughly introduces:

1. Protocol Layer)

When an application communicates with the SQL Server database engine, the application programming interface provided by the protocol layer uses Microsoft's customized tabular data stream (TDS) package to standardize the communication format. This layer provides an interface for applications to access SQL Server.

SQL Server Network Interface (SNI)

SNI is a protocol for establishing network connections between the Server and the client. It provides a set of API functions used by the database engine and the SQL Server Client. SNI replaces the Net-Libraries component and MDAC component in SQL Server2000. This is mainly because MDAC was released along with Windows. It is a headache for the SQLServer team to coordinate MDAC in Windows in the later stage of SQLServer2005 development. So they decided on the SNI solution, which means that this part of code is released along with SQLServer. SQL Server supports four Protocols: Shared memory, TCP/IP, Named Pipes, and Virtual Interface Adapter (. Once a connection is established, SNI creates a secure connection to the server's TDS breakpoint to request and return data.

Table Data Stream endpoint (TDS)

TDS is a proprietary intellectual property protocol of Microsoft. It was originally designed by Sybase. SQL Server creates an endpoint for each of the four protocols it supports during installation. If the protocol is activated, all users can use this protocol. There is also an endpoint dedicated for the dedicated administrator connection (DAC. An SQL statement is sent to SQL Server in the form of a TDS message through a TCP/IP connection.

Protocol Layer)

Once the protocol layer receives the TDS package, it will work in reverse settlement to find the contained request. The protocol layer is also responsible for packaging results and status messages and returning them to the client in the form of TDS messages.

2. Relational Engine)

The relational engine becomes a query processor, including the SQL Server component used to determine the operations to be performed for a query and the best way to perform these operations. At the same time, the relational engine is also responsible for the execution of queries when requesting data from the storage engine and processing the returned results.

Command analyzer (Cmd Parser)

The command analyzer handles T-SQL language events sent to SQL Server. It checks the T-SQL syntax first and returns any error message to the client. If the syntax is valid, it will further generate an execution plan or look for an existing execution plan. The command parser sends a match request to the Plan Cache located in the buffer pool through the T-SQL hash to check whether the execution Plan exists; if not, translates the T-SQL into an internal format that can be executed, that is, the query tree.

Query Optimizer)

The query optimizer retrieves the query tree from the command parser and prepares for its actual execution. The first step to generate an execution plan is to normalize each query. the normalization process may split a single query into multiple appropriate queries with appropriate granularity. Then optimize the SQL Server Query Optimizer based on the cost. It will choose the execution plan that considers the cost to be the lowest within a reasonable time range, it uses some internal indicators (memory requirements, CPU utilization, and I/O requirements) as the basis for selection. In addition, the query optimizer checks the types of request statements, the data volume of affected tables, indexes in tables, and SQL Server statistics. This part can be said to be the most intelligent and responsible part of SQLServer.

SQL Manager

The SQL manager is responsible for managing the transactions related to stored procedures and their plans, and managing the automatic parameters of queries.

Database Manager

The Database Manager manages the metadata access required for query compilation and query optimization. For example, access to the SQL syntax library.

Query Executor)

The query executor runs the execution plan generated by the query optimizer and interacts with the storage engine according to the steps in the execution plan to retrieve or modify data.

Iii. Storage Engine)

The storage engine includes the access method, transaction management, and buffer manager.

Access Methods)

When SQL Server needs to locate the database, it will call the access method code. It provides a set of code to create and request data pages and index pages, and returns the prepared ole db data row sets to the relational engine. The access method is only responsible for sending requests to the buffer manager. Not only does the access method ensure high-speed processing of SQL Server, but it also predicts which data page index pages will be processed soon.

Transaction Manager)

The transaction Manager includes two components: log manager and lock manager.

The lock manager is responsible for data concurrency protection and management based on specific isolation levels.

Log Manager records transaction logs in log files in advance to protect data. All data changes to access method code requests must be recorded in logs. This method is called pre-write logs. This is the only operation that always writes data to a disk (such as inserting, updating, deleting, allocating release zones and pages, and creating and deleting indexes. It records the changes to the data page after the modification operation, so it is difficult for us to find meaningful information. Unless you use third-party tools.

Buffer Manager)

The buffer manager is used to manage the distribution of data pages in the buffer memory pool. All operations on data are first performed in the buffer zone. Then, the checkpoint or inertia writer in SQLOS is synchronized to the disk. This ensures that SQLServer responds to requests faster. After the operation is complete, the result is returned to the access method.

Iv. Buffer Pool

The buffer pool is the most memory-consuming part of SQLServer. It mainly includes the execution plan cache and data cache.

V. SQLOS

It can be understood as the resource manager of SQLServer, which is mainly responsible for some interactive operations with the operating system. Such as memory allocation, resource scheduling, I/O operations, process thread management, synchronization, and latches. This part will not be expanded here. Later I will write a special chapter.

Iii. SQL Server execution model

We can see that this execution model also refers to what happens when the entire SQLServer server receives requests. However, as in the previous section, SQLServer is often too complex to describe clearly in a chapter. This figure illustrates the execution model of SQLServer. Before analyzing this image, you must clarify a problem. SQLServer is a product based on the C/S structure. This is clearly divided into the client and the server. Generally, the MSSMS for database management and the system for database access belong to the SQLServer client. The SQLServer server is mainly composed of some service methods.

Because it is C/S, all client requests must be transmitted to the server for execution. In this case, the protocol for communication between the two parties is called SNI after SQLServer2005. The package format is TDS. The client's TDS is transmitted to the SQLServer server through the Protocol supported by SQLServer. a component on the server is called the connection listening service, which is listening for the request port. It is responsible for listening for new connections, clearing failed connections, and returning result sets, messages, and statuses to the client. The connection listening service sends the package to the protocol layer. The protocol layer unpacks the package, extracts the SQL statements to be executed, and submits them to the relational engine. After processing, the relational engine generates an execution plan, and execute the plan. Once the relational engine develops and executes a work plan, a task object is created. The task object must be associated with an idle working thread. After a worker thread is associated with a new task, the status is set to initialization. After Initialization is complete, the working thread becomes runable. The worker thread is ready to run as long as there is an idle scheduler. If you encounter a timer wait, request resource wait, and I/O wait during execution, it will be suspended and moved to the corresponding list. SQLServer adopts non-preemptible scheduling. A thread will complete the operation of this task. There is no context switch. Of course, if it is not SQL server code, preemptible scheduling is adopted, such as extended storage process.

This is the end of today's analysis. If your description is inappropriate, please note that. Common progress is the final principle.

 

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.