introduction
The top of the stack is an API or an object library layer. The application is connected to Microsoft® SQL Server by an API function or interface disclosed by the object library. The API examples for accessing SQL Server include ODBC and DB-Library. Objects for accessing SQL Server include OLE DB, ADO, and ADO.NET. Since ADO finally communicates with the server, the Windows application actually uses only two commonly used object libraries when communicating with SQL Server, ie OLE DB and ADO.NET. Since the connection is performed by ADO or ADO.NET is usually more common than connecting through ODBC (but SQL Server's query analyzer and enterprise manager is still connected via ODBC), this article will be from ADO / OLE DB and ADO.NET. This section describes the client of the SQL Server connection architecture. Today, most applications are connected to SQL Server through an object library (rather than ODBC or similar API).
Back to top
ADO and OLE DB
The OLE DB client (also known as the user) communicates with the server and other backend programs through the client provider. This provider is a set of COM components (one or more) for converting application requests to network process communication (IPC) requests. In the case of using SQL Server, the most commonly used OLE DB provider is SQLOLEDB, which is Microsoft provides the OLE DB provider provided by SQL Server. SQLOLEDB is attached to SQL Server and is installed as part of the Microsoft Data Access Component (MDAC) library.
In order to communicate with SQL Server using ADO, the application first uses the Connection object to establish a connection with the server. ADO's Connection object accepts a connection string that specifies the OLE DB provider to use and the parameters passed to it. If the application is connected to SQL Server using the SQLOLEDB provider, "SQLOLDB" will be displayed in the string.
The ADO application can also connect to SQL Server via ODBC. To do this, the application will use the OLE DB provider for ODBC and specify an ODBC data source that references the target SQL Server in its connection string. In this case, the application communicates with the OLE DB while the ODBC's OLE DB provider calls the corresponding ODBC API to sessions with SQL Server.
Back to top
ADO.NET
ADO.NET applications are usually connected to SQL Server using .NET Framework Data Provider for SQL Server. The native provider allows the ADO.NET object to communicate directly with SQL Server. Typically, the application uses the SQLConnection object to establish a connection, and then send a command to the server using the SQLCommand object and receives the result of the server returns. SqlDataAdapter and SqlDataReader classes are usually used with SQLCommand to interact with SQL Server via managed code applications.
Through the OLEDBConnection class, the ADO.NET application can also interact with SQL Server using the SQLOL Server using the Sqloledb OLE DB provider. In addition, they can access SQL Server using ODBC through the ODBCConnection class. Therefore, only by hosted code, you have three different ways to access SQL Server from the application. From the perspective of troubleshooting, it is very useful because it helps you will be able to attribute the problem associated with the connection to a specific data access layer or library. Back to top
Client Net-Library
The next layer in the stack is Net-Library. Net-librage provides a channel between the API or object library (the application uses it with SQL Server) and network protocol (for network exchange data). SQL Server provides Net-Library for all major network protocols. These libraries sends a request from the client to SQL Server in a transparent manner and returns the response emitted by the server to the client. You can use the client network utility of SQL Server to configure Net-Library for specific clients. Supported client protocols include TCP / IP, Named Pipes, NWLINK, Multi-Protocol (RPC), and some other protocols.
A Net-Library, which is particularly worth mentioning, is shared memory Net-Library. As the name suggests, the Net-Library uses Windows shared memory functions to communicate between SQL Server clients and servers. Obviously, this means that the client and server must be on the same physical computer.
Since it is able to bypass the physical network stack, shared memory Net-Library is much more than other Net-Library. Access to the shared memory area is protected by synchronous objects, so the communication speed between the client and the server is primarily limited to Windows to scheduling the kernel object, and the ability to perform data replication between the process and the shared memory area.
You can specify a time period or (local) as your computer name when connecting, indicating using shared memory Net-Library. You can also add prefix LPCs for computer / instance names when connecting, indicating to use shared memory Net-Library.
Note that even if the SQL Server connected to the same computer, shared memory Net-Library is not necessarily 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 integral architecture of the application, you should always test a comprehensive test of a given technical solution before it can determine whether it has good scalability and whether it is faster than other methods.
Back to top
connection
When the client is connected, the SQL Server's User Mode Scheduler (UMS) component assigns it to a specific planner. When startup, SQL Server creates a separate UMS program program for each CPU on your system. When the client is connected to the server, these clients will assign a plan program with the minimum number of connections. After the connection, the client will not replace the plan program - it will always be controlled by the specified planning program until the connection is disconnected.
This is important for applications that build multiple connections with the server. If the application performance is poor, or the average allocation is not available on its multiple connections, it may cause unnecessary CPU resource contention between the application, and other connections are actually idle. .
For example, the application has four connections to SQL Server running on the dual processor computer, and connections 1 and 3 belong to processor 0, connection 2 and 4 belong to processor 1. If most of the application is executed by connecting 1 and 3, the two connections will compete for CPU 0, and the CPU 1 may actually be idle. In this case, the application can only disconnect some connection or reconnect some connections, and hope that connects 1 and 3 belong to different CPUs (unable to specify the processor affiliation when connected), or re-on its connection Assign a workload so that each connection workload is more balanced. Of course, the latter case is far better than the previous situation. Back to top
Connect memory
SQL Server retains three packet buffers for each connection for the client request. 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 small for 8 KB, the memory of these packets will be provided by the SQL Server buffer pool. Otherwise, the memory will be assigned by the MemToleave area of SQL Server.
It is worth mentioning that the default network packet size of .NET Framework Data Provider for SQL Server is 8KB, so buffers associated with managed code client connections are typically provided by the MemToleave area of SQL Server. The typical ADO application is different, and their default packet size is 4 KB, so the buffer will be assigned by the SQL Server buffer pool.
Back to top
event
The connected client requests are usually divided into two broad categories: language events and remote processes calls. Although there are other categories, most of the requests sent by the SQL Server client to the server are composed of one of the following two types: Language events are sent from the client to a set of T-SQL. For example, if the Ado Command object (its CommandText property is set to T-SQL query, the CommandType property is set to adcmdText), the query will be submitted to the server as a language event. Similarly, if the CommandType is set to AdcmdTable and calls the execute method, the ADO will generate an internal query (it will select all columns in the table of the CommandText property identifier) and submit it as a language event to the server. On the other hand, if the CommandType is set to AdStoredProc, calling Execute will make the ADO submit a remote procedure call request to the server to perform the stored procedures listed in the CommandText property.
Why do you care about submitting request as a language event or as an RPC to the server? Typically, this is because the RPC features more excellent, especially when repeated calls have different screens. Although SQL Server can automatically request a normal language event request, this ability is very limited. It never tries to automatically parameterize certain types of queries. This may result in a substantially the same query to produce different execution, thereby only because of these different executions provide different values, resulting in a cost of white waste of plan compilation on the server. This is not the result you want - you want to compile a new plan for the first time for queries, and then repeat the program for execution with different parameters.
The RPC supports the program reuse by explicitting parameterized queries (rather than dependent server parameterized queries). After the first execution of the process generates a plan, the subsequent execution will automatically reuse the plan, even if the parameter values thereof are different. With the RPC call stored procedure but also saves the execution time and CPU resources required by the program compilation, it also enhances the utilization of SQL Server memory resources because it avoids waste redundancy. Memory. When performing dynamic T-SQL, SP_EXECUTESQL is often preferred instead of EXEC () is also for the same reason. SP_EXECUTESQL works: Create a stored procedure using the specified query, then call it with the supplied parameters. Unlike EXEC (), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and support program reuse. Dynamic queries performed using sp_executesql are more than unnecessary compilation and resource consumption more than using an Exec () query.
Back to top
TDS
Sending the RPC, language events, and other types of requests from the client to SQL Server, are formatted as a SQL Server specific data format called Table Data Flow (TDS). TDS is "Language" used between SQL Server clients and servers. For its exact format, it will not be introduced, but if the client wants to communicate with SQL Server, TDS must be used.
Currently, SQL Server supports three versions of TDS: TDS 8.0 (for SQL 2000 clients), TDS 7.0 (for SQL Server 7.0 client) and TDS 4.2 (for SQL Server 4.2, 6.0, and 6.5 clients). The version of all SQL Server 2000 features is fully supported only TDS 8.0. Other versions remain backward compatible.
Back to top
Server NET-LIBRARY
At the server side, the client request is initially received by SQL Server to listen for a specific network protocol. These listeners are constructed by the network library on the server and the server-side NET-library (providing a pipe between them with the server). You can use the SQL Server network utility to configure the protocol of the server. SQL Server supports the same range of network protocols with the client (except for processing clusters). For clustered SQL Server, only TCP / IP and named pipes are available.
SQL Server Sets a thread for each network protocol used by the listening to the client, and uses Windows I / O to complete the port mechanism waiting and valid processing request. When receiving a TDS packet from the network, the NET-Library listener recomples them into their original client requests and passes these requests to the command processing layer of SQL Server, i.e., Open Data Services (ODS).
Back to top
Return the result to the client
The server will use the network stack used to initially receive the request when preparing the result of the specific client request. It sends results to the corresponding network protocol by server-side NET-LIBRARY, which will then return to the client through the network in TDS format.
On the client, the client Net-Library reserves the TDS packet received from the server from the IPC layer and continues to forward it to the API or object library initialized the request.
Back to top
summary
Although all components are involved, the round-trip process between the SQL Server client and the server is quite fast - especially when using memory Net-Library, the subcond response time is very common. When building and adjusting your own SQL Server client application, the following issues related to data is worth paying attention:
