none
What is MARS actual means ? RRS feed

  • Question

  • Hi Experts,

    What is actually meant by Multiple Active Result Sets (MARS) in SQL Server? Can anyone give an example. I always get confused with this term.

    Is below tsql block can be considered as MARS ?

    select * from T1;   --result set 1
    select * from T2;   --result set 2
    select * from T3;   --result set 3


    Thanks,
    Sam
    Saturday, December 7, 2019 10:13 AM

Answers

  • MARS allows client applications to execute multiple queries on the same connection.  The results are interleaved such that the application can retrieve results from multiple SELECT queries on the same connection before results of each query are fully consumed. MARS also allows applications execute data modification queries within the same transaction space.

    Note that MARS queries do not actually execute in parallel on the server side.

    Below is a PowerShell example based on this DDL:

    USE tempdb;
    CREATE TABLE dbo.T1(col1 int);
    CREATE TABLE dbo.T2(col1 int);
    INSERT INTO T1 VALUES(1),(2),(3);
    INSERT INTO T2 VALUES(1),(2),(3),(4),(5),(6);
    GO

    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;MultipleActiveResultSets=true")
    $connection.Open()
    $command1 = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM dbo.T1;", $connection)
    $reader1 = $command1.ExecuteReader()
    $command2 = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM dbo.T2;", $connection)
    # without MARS enabled, this command will fail with Exception calling "ExecuteReader" with "0" argument(s): "There is already an open DataReader associated with this Command which must be closed first."
    $reader2 = $command2.ExecuteReader()
    
    while(!$reader1.IsClosed -or !$reader2.IsClosed) {
    
        if(!$reader1.IsClosed) {
            if($reader1.Read()) {
                Write-Host "query 1 row: $($reader1[0])"
            }
            else {
                Write-Host "no more data for query 1"
                $reader1.Close()
            }
        }
    
        if(!$reader2.IsClosed) {
            if($reader2.Read()) {
                Write-Host "query 2 row: $($reader2[0])"
            }
            else {
                Write-Host "no more data for query 2"
                $reader2.Close()
            }
        }
    
    }
    
    $connection.Close()

    Results:

    query 1 row: 1
    query 2 row: 1
    query 1 row: 2
    query 2 row: 2
    query 1 row: 3
    query 2 row: 3
    no more data for query 1
    query 2 row: 4
    query 2 row: 5
    query 2 row: 6
    no more data for query 2


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, December 7, 2019 3:29 PM
    Moderator

All replies

  • MARS allows client applications to execute multiple queries on the same connection.  The results are interleaved such that the application can retrieve results from multiple SELECT queries on the same connection before results of each query are fully consumed. MARS also allows applications execute data modification queries within the same transaction space.

    Note that MARS queries do not actually execute in parallel on the server side.

    Below is a PowerShell example based on this DDL:

    USE tempdb;
    CREATE TABLE dbo.T1(col1 int);
    CREATE TABLE dbo.T2(col1 int);
    INSERT INTO T1 VALUES(1),(2),(3);
    INSERT INTO T2 VALUES(1),(2),(3),(4),(5),(6);
    GO

    $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;MultipleActiveResultSets=true")
    $connection.Open()
    $command1 = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM dbo.T1;", $connection)
    $reader1 = $command1.ExecuteReader()
    $command2 = New-Object System.Data.SqlClient.SqlCommand("SELECT * FROM dbo.T2;", $connection)
    # without MARS enabled, this command will fail with Exception calling "ExecuteReader" with "0" argument(s): "There is already an open DataReader associated with this Command which must be closed first."
    $reader2 = $command2.ExecuteReader()
    
    while(!$reader1.IsClosed -or !$reader2.IsClosed) {
    
        if(!$reader1.IsClosed) {
            if($reader1.Read()) {
                Write-Host "query 1 row: $($reader1[0])"
            }
            else {
                Write-Host "no more data for query 1"
                $reader1.Close()
            }
        }
    
        if(!$reader2.IsClosed) {
            if($reader2.Read()) {
                Write-Host "query 2 row: $($reader2[0])"
            }
            else {
                Write-Host "no more data for query 2"
                $reader2.Close()
            }
        }
    
    }
    
    $connection.Close()

    Results:

    query 1 row: 1
    query 2 row: 1
    query 1 row: 2
    query 2 row: 2
    query 1 row: 3
    query 2 row: 3
    no more data for query 1
    query 2 row: 4
    query 2 row: 5
    query 2 row: 6
    no more data for query 2


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, December 7, 2019 3:29 PM
    Moderator
  • Thank you very much Dan.
    Sunday, December 8, 2019 2:14 PM