Let me first say it is best to use SqlClient in .NET applications for SQL Server data access. As a managed provider, SqlClient will be faster OLE DB or ODBC from managed .NET code, especially with large result sets.
It is expected to see the login and logout events in a SQL Trace, indicating the client has opened and closed a SQL connection. The RPC completed for sp_reset_connection indicates reuse of a pooled connection, resetting the session to the same state of a new physical connection (except isolation level).
Note that SQL Trace doesn't show the network interactions when connection pooling is used. Consider the open late, close early pattern used by many applications:
- open connection
- execute query
- close connection
The overhead of establishing the network connection and authentication is incurred only during the initial connection with connection pooling. A connection close only returns the connection to the pool and a subsequent open only retrieves an unused connection from the pool. Both are strictly client-side and do not send a network request to SQL Server. It is only when a query is executed on the pooled connection that a request is sent to SQL Server. This single request results in the following trace events:
- logout
- rpc completed for sp_reset_connection
- login
- rpc or batch completed for the application query
Because this occurs as a single request, there are no additional expensive network calls with connection pooling.
