K
Kevin_E
I am load testing a client-server application and see intermittent timeouts
in the BeginTransaction method. The server is close to I/O bound but SQL
Server is generally responding to queries in less than 4 seconds and is using
25% CPU about 25% of the time and probably averaging closer to 18% on a
dual-dual-core box. According to my log file, the timeout must happen in
less than 16 seconds (20:21:16.890 is time stamp of previously logged trace
line at top of method, 20.21.32.000 is time stamp of timeout exception).
I know I can bump SqlConnection.ConnectionTimeout up from the default of 15
seconds, but I would like to also understand what can cause a loaded yet
healthy server to take so long to set up a transaction on 1 out of 1000 or so
attempts. BeginTransaction must be blocking on something but I am not sure
what resource it is waiting on, I am not seeing anything on SQL Server's side
(tracing using Profiler) related to this timeout -- so the transaction
attempt does not seem like it has made it that far.
The stack looks like:
System.Data.SqlClient.SqlConnectionOnError
System.Data.SqlClient.SqlInternalConnection.OnError
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning
System.Data.SqlClient.TdsParser.Run
System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction
System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction(iso, transactionName)
System.Data.SqlClient.SqlConnection.BeginTransaction()
<my code>
Any hints as to what the fundamental problem may be?
in the BeginTransaction method. The server is close to I/O bound but SQL
Server is generally responding to queries in less than 4 seconds and is using
25% CPU about 25% of the time and probably averaging closer to 18% on a
dual-dual-core box. According to my log file, the timeout must happen in
less than 16 seconds (20:21:16.890 is time stamp of previously logged trace
line at top of method, 20.21.32.000 is time stamp of timeout exception).
I know I can bump SqlConnection.ConnectionTimeout up from the default of 15
seconds, but I would like to also understand what can cause a loaded yet
healthy server to take so long to set up a transaction on 1 out of 1000 or so
attempts. BeginTransaction must be blocking on something but I am not sure
what resource it is waiting on, I am not seeing anything on SQL Server's side
(tracing using Profiler) related to this timeout -- so the transaction
attempt does not seem like it has made it that far.
The stack looks like:
System.Data.SqlClient.SqlConnectionOnError
System.Data.SqlClient.SqlInternalConnection.OnError
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning
System.Data.SqlClient.TdsParser.Run
System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction
System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction(iso, transactionName)
System.Data.SqlClient.SqlConnection.BeginTransaction()
<my code>
Any hints as to what the fundamental problem may be?