Can I stop sp_reset_connection being called to improve performance?

Question

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?

Loyalty

Can you describe the performance problem you are trying to solve? – Reorder

Answer 1

If you're using .NET to connect to SQL Server, disabling of the extra reset call was disabled as of .NET 3.5 -- see here. (The property remains, but it does nothing.)

I guess Microsoft realized (as someone did experimentally here) that opening the door to avoid the reset was far more dangerous than it was to get a (likely) small performance gain. Can't say I blame them.

---

Does the client send exec sp_reset_connection, wait for a response, then send the real sql?

EDIT: I was wrong -- see here -- the answer is no.

Summary: there is a special bit set in a TDS message that specifies that the connection should be reset, and SQL Server executes sp_reset_connection automatically. It appears as a separate batch in Profiler and would always be executed before the actual query you wanted to execute, so my test was invalid.

Yes, it's sent in a separate batch.

I put together a little C# test program to demonstrate this because I was curious:

using System.Data.SqlClient;

(...)

private void Form1_Load(object sender, EventArgs e)
{
    SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
    csb.DataSource = @"MyInstanceName";
    csb.IntegratedSecurity = true;
    csb.InitialCatalog = "master";
    csb.ApplicationName = "blarg";

    for (int i = 0; i < 2; i++)
        _RunQuery(csb);
}

private void _RunQuery(SqlConnectionStringBuilder csb)
{
    using (SqlConnection conn = new SqlConnection(csb.ToString()))
    {
        conn.Open();

        SqlCommand cmd = new SqlCommand("WAITFOR DELAY '00:00:05'", conn);

        cmd.ExecuteNonQuery();
    }
}

Start Profiler and attach it to your instance of choice, filtering on the dummy application name I provided. Then, put a breakpoint on the cmd.ExecuteNonQuery(); line and run the program.

The first time you step over, just the query runs, and all you get is the SQL:BatchCompleted event after the 5 second wait. When the breakpoint hits the second time, all you see in profiler is still just the one event. When you step over again, you immediately see the exec sp_reset_connection event, and then the SQL:BatchCompleted event shows up after the delay.

The only way to get rid of the exec sp_reset_connection call (which may or may not be a legitimate performance problem for you) would be to turn off .NET's connection pooling. And if you're planning to do that, you'd likely want to build your own connection pooling mechanism, because just turning it off and doing nothing else will probably hurt more overall than taking the hit of the extra roundtrip, and you will have to deal with the correctness issues manually.

Insignia

thanks for this. However, I don't think your test app proves that exec sp_reset_connection is sent in a separate batch: it could just be sent down the wire along with every call except the first one. My main concern was whether the call effectively doubles the network latency. e.g. consider a ludicrous scenario: if it takes 100ms to run each sql batch but takes 5000ms for the network roundtrip then will it take 5100ms to run each batch or will it take 10100ms? It seems hard to create a good test to prove this either way. – Loyalty

And therefore it seems that the answer is: no, you can't turn off exec sp_reset_connection without turning off connection pooling and no, it doesn't require an additional roundtrip to the server so doesn't double the impact of network latency on performance. At some point when I'm feeling very energetic I might use Wireshark to confirm that it really doesn't add an additional message roundtrip on the network, but that reference and msdn.microsoft.com/en-us/library/dd358342(v=prot.13) strongly suggests it doesn't. – Loyalty

Answer 2

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.

Haematoxylon

Answer 3

Personally, I'd leave it.

Given what it does, I want to make sure I have no temp tables in scope or transactions left open.

To be fair, you will gain a bigger performance boost by not running profiler against your production database. And do you have any numbers or articles or recommendations about what you can gain from this please?

Tippets

No, I don't have any numbers. I want to turn it off so I can measure performance and get some :) If there's a reason it would definitely not improve performance that'd be good to know too. Any thoughts? – Loyalty

Answer 4

Just keep the connection open instead of returning it to the pool, and execute all commands on that one connection.

Laoag

This would be a good solution, but would require significant reengineering of my code so isn't currently an option. – Loyalty