0

I'm reviewing the performance of a .NET application that sends 1,000s of consecutive select statements to a Microsoft SQL Server 2014 Instance. Ping time to server from client is 70 ms or higher across a WAN.

The client application is using Ole DB to connect to the SQL database.

Almost every single query SQL Profiler shows this pattern

  • Audit:Login
  • SQL:BatchStarting (Single SELECT statement)
  • SQL:BatchCompleted
  • Audit:Logout
  • RPC:Completed - exec sp_reset_connection

This repeats thousands of times. Is it possible the application could be re-coded to avoid constant login / logout, saving expensive network calls

Or is it expected to see this information for every query.

  • Not sure DBA can do something. I believe there is a way in an app to keep a single connection. However, there might a deeper problem in App design. – Slava Murygin Dec 21 '18 at 13:53
1

If Audit:Login EventSubclass=2, then this is expected, and shouldn't be an issue. The client is just reusing a connection from a connection pool and "resetting" the connection to clear any impersonations, temp tables, session settings, etc.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.