Can I stop sp_reset_connection being called to improve performance?

Rory_8479 0
Viewed 17241

My profiler trace shows that exec sp_reset_connection is being called between every sql batch or procedure call. There are reasons for it, but can I prevent it from being called, if I'm confident that it's unnecessary, to improve performance?

UPDATE: The reason I imagine this could improve performance is twofold:

  1. SQL Server doesn't need to reset the connection state. I think this would be a relatively negligible improvement.
  2. Reduced network latency because the client doesn't need to send down an exec sp_reset_connection, wait for response, then send whatever sql it really wants to execute.

The second benefit is the one I'm interested in, because in my architecture the clients are sometimes some distance from the database. If every sql batch or rpc requires a double round-trip this doubles the impact of any network latency. Eliminating such double calls could potentially improve performance.

Yes there are lots of other things I could do to improve performance like re-architect the app, and I'm a big fan of solving the root cause of problems, but in this case I just want to know if it's possible to prevent sp_reset_connection to be called. Then I can test if there is any performance improvement and properly assess the risks of not calling this.

This prompts another question: does the network communication with sp_reset_connection really occur like I outlined above? i.e. Does the client send exec sp_reset_connection, wait for a response, then send the real sql? Or does it all go in one chunk?

sp-reset-connection sql-server performance
4 Answers

This Q/A could be helpful: What does "exec sp_reset_connection" mean in Sql Server Profiler?

However, I did a quick test using Entity Framework and MS-SQL 2008 R2. It shows that "exec sp_reset_connection" isn't time consuming after the first call:

for (int i = 0; i < n; i++)
{
    using (ObjectContext context = new myEF())
    {
                
        DateTime timeStartOpenConnection = DateTime.Now;
        context.Connection.Open();
        Console.WriteLine();
        Console.WriteLine("Opening connection time waste: {0} ticks.", (DateTime.Now - timeStartOpenConnection).Ticks);

        ObjectSet<myEntity> query = context.CreateObjectSet<myEntity>();
        DateTime timeStart = DateTime.Now;
        myEntity e = query.OrderByDescending(x => x.EventDate).Skip(i).Take(1).SingleOrDefault<myEntity>();
        Console.Write("{0}. Created By {1} on {2}... ", e.ID, e.CreatedBy, e.EventDate);
        Console.WriteLine("({0} ticks).", (DateTime.Now - timeStart).Ticks);

        DateTime timeStartCloseConnection = DateTime.Now;
        context.Connection.Close();
        context.Connection.Dispose();
        Console.WriteLine("Closing connection time waste: {0} ticks.", (DateTime.Now - timeStartCloseConnection).Ticks);
        Console.WriteLine();
    }
}

And output was this:

Opening connection time waste: 5390101 ticks. 585. Created By sa on 12/20/2011 2:18:23 PM... (2560183 ticks). Closing connection time waste: 0 ticks.

Opening connection time waste: 0 ticks. 584. Created By sa on 12/20/2011 2:18:20 PM... (1730173 ticks). Closing connection time waste: 0 ticks.

Opening connection time waste: 0 ticks. 583. Created By sa on 12/20/2011 2:18:17 PM... (710071 ticks). Closing connection time waste: 0 ticks.

Opening connection time waste: 0 ticks. 582. Created By sa on 12/20/2011 2:18:14 PM... (720072 ticks). Closing connection time waste: 0 ticks.

Opening connection time waste: 0 ticks. 581. Created By sa on 12/20/2011 2:18:09 PM... (740074 ticks). Closing connection time waste: 0 ticks.

So, the final conclusion is: Don't worry about "exec sp_reset_connection"! It wastes nothing.

user_5383870
Related