Execution Time Slower with each Iteration of the same SPROC

Question

Running the same Stored Procedure from C# .Net application over a network gets progressively slower with each subsequent execution. It appears to take twice the amount of time as the previous execution (up to a max value; read on). The execution time becomes progressively slower until 1 of 2 scenarios happens, at which point the first execution of the SPROC is "fast" again.

1. If an SqlConnection is opened and remains open during all testing, the SPROC gets progressively slower until any other SPROC or query is run.
2. If an SqlConnection is opened and closed around each execution, the SPROC gets progressively slower until at least 8 minutes has passed.

This only happens with a few Stored Procedures. One is a simple SELECT query with 2 JOINs, (SPROC 1) another is a massive 1600+ line SPROC (SPROC 2).

The execution times appear to never go beyond exactly 60 seconds for SPROC 1 and 67 seconds for SPROC 2. SPROC 1 takes less than a second to execute initially, and SPROC 2 takes 7 seconds initially.

This also only happens if the SPROC is run using the same SqlConnection in the application. As soon as 2 separate SqlConnection objects are used, they behave the same as stated above, but are independent. Running the SPROC multiple times on SqlConnection1 gets progressively slower, but the first time the same SPROC is run on SqlConnection2, it's "fast". It will then also get slower when run multiple times on SqlConnection2.

This does not happen if the application is run on the same computer with SQL Server 2008 R2 installed (running Windows Server 2008). The execution time is always consistent.

Running the Stored Procedure from within Management Studio also does not get slower with each execution; it is always consistent.

Clearing the execution plan cache (in SQL Server) has no effect on the observed behavior.

It has taken quite a few days to narrow down this issue originally observed in a much larger application, in order to create a test app to easily test/reproduce it.

From what I've read here, there is a timeout of between 4 and 8 minutes (after SqlConnection.Close() is called in code) at which point it closes the database connection to the data source. This appears to be in line with the scenario 2 I mentioned above.

This leads me to believe it is related to the SqlConnection used (and the underlying database connection to the data source) since connection pooling is enabled in my case, but why am I observing this behavior, and how do I fix it?

We are using the .Net 2.0 Framework, if that makes any difference.

There are many fine details listed above, so please let me know if I need to clarify anything.

The only Stack Overflow question with any similarities is this, but was unrelated to my issue.

Edit: The following code is executed in my WinForms test app on startup:

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();

connectionStringBuilder.DataSource = m_DataSource;
connectionStringBuilder.InitialCatalog = m_InitialCatalog;
connectionStringBuilder.UserID = m_UserID;
connectionStringBuilder.Password = m_Password;
connectionStringBuilder.IntegratedSecurity = false;
connectionString = connectionStringBuilder.ConnectionString;

m_DatabaseConnection = new SqlConnection(connectionString);

I then have 2 buttons; one of which calls SPROC 1 mentioned above, and the other calls a different SPROC which does not have the same slowdown issue. The following code is executed on either button click (only difference being the SPROC name):

m_DatabaseConnection.Open();
m_DatabaseCommand = new SqlCommand("GetCompanies", m_DatabaseConnection);
m_DatabaseCommand.Parameters.AddWithValue("@StatusID", StatusID);
m_DatabaseCommand.CommandType = CommandType.StoredProcedure;
m_DatabaseCommand.CommandTimeout = 0;

SqlDataAdapter databaseDataAdapter = new SqlDataAdapter(m_DatabaseCommand);
DataSet databaseDataSet = new DataSet();
databaseDataAdapter.Fill(databaseDataSet);
m_DatabaseConnection.Close();

Answer 1

Here are my ideas to debug this problem:

• Try calling SqlConnection.ClearAllPools() after Disposing the connection. If this fixes the problem, the problem is tied to a particular connection for sure.
• Next, enclose the SPROC in an explicit transaction.
• Next, call SqlConnection.ClearAllPools() before invoking the SPROC.
• How much data does the SPROC return?
• Please post the C# code you are using to open the connection and execute the SPROC.
• Create a standalone console app that is reproducing the behavior you are seeing. This will (likely) prove that something in your app is the problem because the console app will run just fine.