Basic SQL Server connection knowledge

Basic SQL Server connection knowledge

Abstract:The SQL Server 2000 client connects to SQL Server through a stack consisting of APIS, object libraries, and protocols. Ken Henderson will introduce each component of the stack one by one and explain how they work and how they interact.

Introduction

The top of the stack is the API or object library layer. Applications connect to Microsoft SQL Server through APIS or interfaces exposed by the object library. The APIs used to access SQL Server include ODBC and DB-Library. Examples of object libraries used to access SQL Server include OLE DB, ADO, and ADO. NET. Since ADO eventually uses ole db to communicate with the Server, Windows applications only use two common object libraries, ole db and ADO. NET, to communicate with SQL Server. Due to the use of ADO or ADO. NET connections are more common than ODBC connections (however, SQL Server's query analyzer and Enterprise Manager are still connected through ODBC). Therefore, this article will refer to ADO/OLE DB and ADO.. NET. Today, most applications connect to SQL Server through an object library instead of ODBC or similar APIs.

ADO and OLE DB

The ole db client (also called a user) communicates with the server and other backend programs through the client provider. This provider is a group of COM components (one or more) used to convert application requests to inter-network process Communication (IPC) requests. When SQL Server is used, the most common ole db Provider is SQLOLEDB, which is an ole db Provider provided by Microsoft for SQL Server. SQLOLEDB is attached to SQL Server and installed as part of the Microsoft Data Access Component (MDAC) Library.

To use ADO to communicate with SQL Server, the application first uses the Connection object to establish a Connection with the Server. The Connection object of ADO accepts a Connection string, which specifies the ole db provider to be used and the parameters passed to it. If the application uses the SQLOLEDB provider to connect to SQL Server, "SQLOLEDB" is displayed in this string ".

The ADO application can also connect to SQL Server through ODBC. To this end, the application uses the ole db provider for ODBC and specifies to reference the ODBC Data Source of the target SQL Server in its connection string. In this case, the application communicates with ole db, while the ole db provider of ODBC calls the corresponding odbc api for session with SQL Server.

ADO. NET

ADO. NET applications usually use. NET Framework Data Provider for SQL Server to connect to SQL Server. The local provider enables the ADO. NET object to communicate directly with SQL Server. Generally, an application uses the SqlConnection object to establish a connection, then uses the SqlCommand object to send commands to the server, and receives the results returned by the server. The SqlDataAdapter and SqlDataReader classes are usually used with SqlCommand to interact with SQL Server through hosted code applications.

Through the OleDbConnection class, ADO. NET applications can also use the sqloledb ole db provider to interact with SQL Server. In addition, they can use ODBC to access SQL Server through the OdbcConnection class. Therefore, only by hosting code, you have three different methods to access SQL Server from the application. From the perspective of troubleshooting, it is very useful to understand these methods, because it helps you to resolve connection-related problems to a specific data access layer or database.

Client Net-Library

The next layer in the stack is Net-Library. Net-Library provides a channel between the API or object Library (the application uses it to communicate with SQL Server) and the network protocol (used to exchange data with the network. SQL Server provides Net-Library for all major network protocols. These libraries transparently send requests sent from the client to SQL Server and return the responses sent from the Server to the client. You can use the SQL Server Client Network utility to configure the Net-Library for a specific client. The supported client protocols include TCP/IP, named pipe, NWLink, multi-protocol (RPC), and other protocols.

In particular, Net-Library is a shared-memory Net-Library. As the name suggests, the Net-Library uses the Windows Shared Memory Function to communicate between the SQL Server client and the Server. Obviously, this means that the client and server must be on the same physical computer.

Because it can bypass the physical network stack, the shared memory Net-Library is much faster than other Net-libraries. Access to the shared memory area is protected by synchronization objects. Therefore, the communication speed between the client and the server is mainly limited by Windows's ability to schedule kernel objects, and the ability to replicate data between processes and shared memory areas.

You can specify a certain period of time or (local) as your computer name during connection to indicate the use of shared memory Net-Library. You can also add a prefix to the computer/Instance name during connection.Lpc:To indicate the use of the shared memory. Net-Library.

Note: Even if you connect to the SQL Server on the same computer, the shared memory Net-Library may not be the best connection option. In some cases, the direct connection between the client and the server may limit its scalability. Like other elements in the overall architecture of an application, you should always perform a comprehensive test of the given technical solution before you can determine whether it has good scalability and is faster than other methods.

Connection

When the client is connected, the user mode scheduler (UMS) component of SQL Server assigns it to a specific scheduler. At startup, SQL Server creates a separate UMS Scheduler for each CPU on the system. When the client connects to the server, these clients are assigned to the scheduler with the least number of connections. After the connection, the client will not change the scheduler-it will always be controlled by the specified scheduler until the connection is disconnected.

This is important for applications that establish multiple connections with the server. If the application has poor performance or cannot evenly allocate work on multiple connections, unnecessary CPU resource contention may occur between some connections of the application, other connections are actually idle.

For example, an application establishes four connections with the SQL Server running on a dual-processor computer. Connection 1 and 3 belong to processor 0, connection 2 and 4 belong to processor 1. If most of the application's work is performed through connections 1 and 3, the two connections will compete for CPU 0, while CPU 1 may still be idle. In this case, the application can only disconnect some connections or reconnect some connections, and want to connect 1 and 3 to belong to different CPUs (the processor membership cannot be specified during connection ), or re-allocate the workload on its connection, so that the workload of each connection is more balanced. Of course, the latter is far better than the former.

Connection memory

SQL Server reserves three data packet buffers for each connection requested by the client. The size of each buffer depends on the default network packet size specified by the sp_configure stored procedure. If the default network packet size is smaller than 8 KB, the memory of these packets will be provided by the SQL Server Buffer Pool. Otherwise, the memory will be allocated by the MemToLeave region of SQL Server.

It is worth mentioning that ,. NET Framework Data Provider for SQL Server's default network packet size is 8 KB. Therefore, the buffer associated with the connection to the managed code client is generally provided by the MemToLeave region of SQL Server. The typical ADO applications are different. Their default data packet size is 4 KB, so the buffer zone will be allocated by the SQL Server Buffer Pool.

Event

Client Requests After connection are generally divided into two broad categories: Language events and remote process calls. Despite the existence of other categories, most requests sent from the SQL Server client to the Server are composed of one of the following two types: A language event is a group of T-SQL statements sent from the client to the Server. For example, if you call the Execute method of the ADO Command object whose CommandText property is set to T-SQL query and the CommandType property is set to ad1_text, the query is committed to the server as a language event. Similarly, if you set CommandType to adCmdTable and call the Execute method, ADO generates an internal query (it selects all columns in the table identified by the CommandText attribute ), and submit it to the server as a language event. On the other hand, if CommandType is set to adStoredProc, calling Execute will enable ADO to submit a Remote Procedure Call request to the server to Execute the stored procedure listed in the CommandText attribute.

Why do I need to submit requests to the server as language events or as RPC? Generally, this is because RPC has better functions, especially when the same query with different screening values is called repeatedly. Although SQL Server can automatically parameterize common language Event requests, this capability is very limited. It never tries to automatically parameterize certain types of queries. This may lead to different executions for basically the same query, so that only these different executions provide different values, resulting in a waste of plan compilation costs on the server. This is usually not the expected result-you want to compile a new plan for the first execution of the query, and then reuse the plan for execution with different parameters.

RPC supports repeated use by explicitly parameterized queries instead of relying on server parameterized queries. After a plan is generated for the first execution of the Process, subsequent execution will automatically reuse the plan, even if they provide different parameter values. Compared with calling a stored procedure through a language event, calling a stored procedure through RPC not only saves the execution time and CPU resources required for the planned compilation, but also enhances the utilization of the memory resources of the SQL Server, this avoids the memory wasted by redundant execution plans.

Usually sp_executesql rather than EXEC () is preferred when performing a dynamic T-SQL for the same reason. Sp_executesql is used to create a stored procedure using the specified query and call it using the provided parameters. Unlike EXEC (), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and support reuse of plans. Dynamic queries executed using sp_executesql can avoid unnecessary compilation and resource consumption to a greater extent than queries using EXEC.

TDS

RPC, language events, and other types of requests sent from the client to SQL Server are formatted as SQL Server-specific data formats called table data stream (TDS. TDS is a language used between the SQL Server client and the Server ". The exact format is not described. However, if the client needs to communicate with SQL Server, it must use TDS.

Currently, SQL Server supports three versions of TDS: TDS 8.0 (applicable to SQL 2000 clients) and TDS 7.0 (applicable to SQL Server 7.0 clients) and TDS 4.2 (applicable to SQL Server 4.2, 6.0, and 6.5 clients ). Only TDS 2000 is supported for all SQL Server 8.0 versions. Other versions are backward compatible.

Server-side Net-Library

On the Server side, client requests are initially received by the listener established by SQL Server to listen on specific network protocols. These listeners are composed of the network Library on the server and the Net-Library on the server (providing pipelines between them and the server. You can use the SQL Server network utility to configure the protocol for Server listening. SQL Server and the client support the same network protocol (except for cluster processing ). For clustered SQL Server, only TCP/IP and named pipes are available.

SQL Server sets a thread for each network protocol used to listen for client requests, and uses Windows I/O to complete the port mechanism to wait and effectively process requests. When TDS packets are received from the network, the Net-Library listener reassembles them into their original client requests and passes these requests to the SQL Server command processing layer, that is, Open Data Service (ODS ).

Return the result to the client

When the server is preparing to return the results of a specific client request, the network stack used to initially receive the request will be used. It sends the results to the corresponding network protocol through the server-side Net-Library, and then the results will be returned to the client in the network in the TDS format.

On the client, the client Net-Library reassembles the TDS data packets received from the server from the IPC layer and forwards them to the API or object Library that initializes the request.

Summary

Despite all the components involved, the round-trip process between the SQL Server client and the Server is quite fast-especially when the memory Net-Library is used, the sub-second response time is very common. When you build and adjust your own SQL Server Client applications, the following data-related issues are worth noting:

•	If the application runs on the same computer as SQL Server, we recommend that you use the shared memory Net-Library (if it is not used yet ). Net-Library connections based on shared memory are usually much faster than other types of connections. While paying attention to the above content, we should also always fully test the solution and compare it with other feasible solutions so as to determine whether it is indeed better or faster. It is better than words.
•	Because the client will be assigned to a specific UMS scheduler during the first connection, and will not be able to get rid of the scheduler control only after the connection is disconnected, therefore, it is important to ensure that the workload is evenly distributed over connections established between applications and servers. Unbalanced workloads can cause unnecessary CPU contention and reduce resource usage.
•	The default network packet size configured on the server and the network packet size specified by the client during connection directly affect the memory size required for the server and the pool for memory allocation. Keep this in mind when configuring the scalability and speed of the server. Remember that by default, the network packet size of the ADO. NET application is larger than that of the ADO application.
•	Generally, RPC rather than non-verbal events should be preferred when sending requests to the server. To this end, you should set the corresponding properties in the used ADO or ADO. NET object.
•	When performing a dynamic T-SQL, use sp_executesql instead of EXEC () whenever possible (). The only exception is that when the EXEC () function is used to connect a query segment to a dynamic query string, the size of the string exceeds the storage size of a single local variable (this is rare ).
•	When a client problem occurs and you suspect it may be related to the object library or API used to connect to the server, you can use a troubleshooting technique to change the client mechanism used, in this way, the problem can be attributed to a specific component. For example, if you upgrade MDAC and start to see error 17805 in the SQL Server Error Log, this indicates that the format of the TDS data packet sent by the client ADO application is incorrect. You may try to convert your application to the ole db provider of ODBC. If you can easily achieve this, check whether the problem is related to the SQLOLEDB provider. On the contrary, if an ADO-based application keeps connected through ODBC, you can switch to SQLOLEDB to see if this can solve the problem or at least help you narrow down the problem.
•	Similarly, changing the Net-Library in use is sometimes helpful when troubleshooting connection issues. If TCP/IP is used, it may be worth a try to name the pipe. For example, if a problem occurs on the DHCP Server and there is no valid IP address, you cannot use TCP/IP to connect to the SQL Server. By switching to the named pipe, you can quickly resolve the problem to a specific TCP/IP factor. On the other hand, if the same problem persists after switching to the Net Library, You can exclude the Net-Library problem. The cause may be that the server is shut down or the network infrastructure between you and the server cannot work properly. Finally, you can easily change the Net-Library used by the application without changing the application itself, which provides you with a tool to help narrow the scope of the problem. Although it is not feasible to use a specific Net-Library in the long run, enabling the client to temporarily use it can help you narrow down the scope of connection problems.

 

References

The Guru's Guide to SQL Server Architecture and Internals(English)

SQL Server for Developers

Ken HendersonHe lives with his wife and children on the outskirts of Dallas, Texas. He has compiled eight books covering various technical topics, including The recently released "The Guru's Guide to SQL Server Architecture and Internals" (Addison-Wesley, 2003 ). Ken is a loyal fan of the Dallas Mavericks team and enjoys sports, gardening, and child care in his spare time.