MARS - Multiple Active Result Sets
Multiple Active Result Sets - MARS
In a nutshell, it is the ability to have more than one pending request under a given SQL Server connection. For most cases this will directly translate to the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session.
It is probably as important to delimit what MARS is not:
Parallel execution: Though MARS enables more than one request to be submitted under the same connection, this does not imply that they will be executed in parallel inside the server. MARS will multiplex execution threads between outstanding requests in the connection, interleaving at well defined points.
Cursor replacement: As described earlier, there are some scenarios where cursors represented a suitable workaround for a lack of MARS; it may be valid to migrate those scenarios to use MARS. However, this does not imply that all current usages of cursors should be moved to MARS.
By default, all of the code snippets included in the previous section "just work" when using MARS-enabled client drivers against a SQL Server 2005 server. Also, the deadlock scenario described below where the application would hang now succeeds under MARS-enabled connection.
MARS-enabled client drivers are the following:
The SQLODBC driver included in the SQL Native Client.
The SQLOLEDB driver included in the SQL Native Client.
The SqlClient .NET Data Provider included in the Microsoft .NET Framework, Version 2.0.
By default, these drivers will establish MARS-enabled connections. If for some reason it is desired to establish connections that expose behavior of down-level drivers, each API provides an option to request non-MARS connections.
SqlClient provides the MultipleActiveResultSets connection string option. If set to false, MARS is not enabled for the session. If set to true or omitted, MARS is enabled.
Similarly, ODBC provides a SQL_COPT_SS_MARS_ENABLED connection option, while OLEDB provides a SSPROP_INIT_MARSCONNECTION option. Again, these options may only be needed to disable MARS, since it is enabled by default.
Note MARS is only available with SQL Native Client versions of ODBC and OLEDB providers. Older versions of the providers have not been enhanced to support MARS. Needless to say, new drivers cannot support MARS when connected to SQL Server 2000 or earlier servers.
In a nutshell, it is the ability to have more than one pending request under a given SQL Server connection. For most cases this will directly translate to the ability to have more than one default result set (firehose cursor) outstanding while other operations can execute within the same session.
It is probably as important to delimit what MARS is not:
Parallel execution: Though MARS enables more than one request to be submitted under the same connection, this does not imply that they will be executed in parallel inside the server. MARS will multiplex execution threads between outstanding requests in the connection, interleaving at well defined points.
Cursor replacement: As described earlier, there are some scenarios where cursors represented a suitable workaround for a lack of MARS; it may be valid to migrate those scenarios to use MARS. However, this does not imply that all current usages of cursors should be moved to MARS.
By default, all of the code snippets included in the previous section "just work" when using MARS-enabled client drivers against a SQL Server 2005 server. Also, the deadlock scenario described below where the application would hang now succeeds under MARS-enabled connection.
MARS-enabled client drivers are the following:
The SQLODBC driver included in the SQL Native Client.
The SQLOLEDB driver included in the SQL Native Client.
The SqlClient .NET Data Provider included in the Microsoft .NET Framework, Version 2.0.
By default, these drivers will establish MARS-enabled connections. If for some reason it is desired to establish connections that expose behavior of down-level drivers, each API provides an option to request non-MARS connections.
SqlClient provides the MultipleActiveResultSets connection string option. If set to false, MARS is not enabled for the session. If set to true or omitted, MARS is enabled.
Similarly, ODBC provides a SQL_COPT_SS_MARS_ENABLED connection option, while OLEDB provides a SSPROP_INIT_MARSCONNECTION option. Again, these options may only be needed to disable MARS, since it is enabled by default.
Note MARS is only available with SQL Native Client versions of ODBC and OLEDB providers. Older versions of the providers have not been enhanced to support MARS. Needless to say, new drivers cannot support MARS when connected to SQL Server 2000 or earlier servers.
Comments
Post a Comment