CODEWITHSUNDEEP
c#sqlsql-server
Ruudjah
Ruudjah

Reputation: 857

Can I disable MultipleActiveResultSets in my codebase without consequences?

We found we're qualifiying for the issue described at https://github.com/dotnet/SqlClient/issues/422 : linux machines querying SqlServer using a connectionString where MultipleActiveResultSets=True and having exceptions alike

---> System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 ---> System.ComponentModel.Win32Exception (258): Unknown error 258
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

A bunch of services all have MARS set to True, which I'd like to disable (set to False). It is unclear to me what consequences this has on a current codebase. I have searched around and found that it transparently adds performance wins (in a scenario where the client is windows-based, that is), but was unable to find dependencies of MARS using sql.

Can I disable MARS without consequences to my current application?

Upvotes: 1

Views: 854

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063814

MARS primarily impacts scenarios where you have an open, in progress reader, and you execute a second command on the same connection. This could be for N+1 operations. So: you need to review for any time where you open a reader and then do something (anything) on the connection before the reader is closed/disposed. If you never do that: you can disable MARS.

Assuming you have decent tests and/or a test environment, you can just disable MARS and see what happens!

Enabling MARS does not, AFAIK, add transparent/automatic performance wins - and having the machinery in place to support overlapping operations could actually result in a very minor performance hit when enabled, when it isn't being exploited.

Upvotes: 2

Related Questions