SQL Server connection BASICS (notes)

SQL Server Connect basic knowledge (notes) The SQL Server Query analyzer is connected to the Enterprise Manager through ODBC. Client net-LibraryNet-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. Supported client protocols include TCP/IP, named pipe, nwlink, multi-protocol (RPC), and other protocols. ConnectionWhen 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. Connection memorySQL 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. EventClient Requests After connection are generally divided into two broad categories: Language events and Remote Procedure Calls (RPC). dynamic queries executed using sp_executesql are compared with exec () to avoid unnecessary compilation and resource consumption. TDSRPC, 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 ". 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-LibraryOn 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 clientWhen 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. SummaryDespite 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 constructing and adjusting your own SQL Server Client application, the following data-related issues are worth noting: l 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 ). 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. L 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. L The default network packet size configured on the server and the network packet size specified by the client during connection will 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. L 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. L 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 ). L when a client problem occurs and you suspect it may be related to the object library or API used to connect to the server, one of the troubleshooting techniques that can be used is 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. L Similarly, changing the net-library in use is sometimes helpful when troubleshooting connection problems. 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. Original post address