Utilizzo di MARS (Multiple Active Result Set)

Articolo
05/23/2023

Si applica a: SQL Server Azure SQL Database Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW)

Scaricare il driver OLE DB

In SQL Server 2005 (9.x) è stato introdotto il supporto per MARS (Multiple Active Result Set) nelle applicazioni che accedono al motore di database. Nelle versioni precedenti di SQL Server le applicazioni di database non erano in grado di gestire più istruzioni attive in una connessione. Quando si usa il set di risultati predefinito di SQL Server, l'applicazione deve elaborare o annullare tutti i set di risultati da un batch prima che possa eseguire qualsiasi altro batch o connessione. SQL Server 2005 (9.x) ha introdotto un nuovo attributo di connessione che consente alle applicazioni di avere più di una richiesta in sospeso per connessione e in particolare, per avere più di un set di risultati predefinito attivo per connessione.

MARS semplifica la progettazione delle applicazioni grazie alle nuove funzionalità seguenti:

Le applicazioni possono avere più set di risultati predefiniti aperti e interfacciarsi per eseguirne la lettura.
Le applicazioni possono eseguire altre istruzioni, ad esempio INSERT, UPDATE, DELETE e chiamate alle stored procedure, mentre sono aperti i set di risultati predefiniti.

Le applicazioni che utilizzano MARS troveranno vantaggiose le linee guida seguenti:

I set di risultati predefiniti devono essere utilizzati per i set di risultati temporanei o i set di risultati brevi generati da singole istruzioni SQL (SELECT, DML con OUTPUT, RECEIVE, READ TEXT e così via).
I cursori server devono essere utilizzati per i set di risultati di lunga durata o di grandi dimensioni generati da singole istruzioni SQL.
Leggere sempre fino alla fine dei risultati per le richieste procedurali indipendentemente dalla restituzione dei risultati e per i batch che restituiscono più risultati.
Quando possibile, usare le chiamate API per modificare le proprietà di connessione e gestire le transazioni anziché le istruzioni Transact-SQL.
In MARS la rappresentazione con ambito sessione non è consentita durante l'esecuzione di batch simultanei.

Nota

Per impostazione predefinita, la funzionalità MARS non è abilitata. Per usare MARS quando ci si connette a SQL Server con OLE DB Driver per SQL Server, è necessario abilitarlo in modo specifico all'interno di una stringa di connessione. Per altre informazioni, vedere le sezioni relative a OLE DB Driver per SQL Server più avanti in questo argomento.

OLE DB Driver per SQL Server non limita il numero di istruzioni attive in una connessione.

Le applicazioni tipiche che non devono eseguire contemporaneamente più stored procedure o più batch costituiti da più istruzioni trarranno vantaggio da MARS senza dovere comprendere il modo in cui MARS viene implementato, mentre le applicazioni con requisiti più complessi dovranno necessariamente comprenderne il funzionamento.

MARS consente l'esecuzione interleaved di più richieste all'interno di una sola connessione. Ovvero, consente di eseguire un batch e contestualmente di eseguire altre richieste. Notare, tuttavia, che MARS viene definito in termini di interleaving e non in termini di esecuzione parallela.

L'infrastruttura di MARS consente l'esecuzione di più batch in modo interleaved, sebbene sia possibile passare da un'esecuzione all'altra solo in specifici punti definiti. Inoltre, la maggior parte delle istruzioni deve essere eseguita automaticamente all'interno di un batch. Le istruzioni che restituiscono righe al client, definite talvolta punti specifici possono eseguire l'interleave dell'esecuzione prima del completamento durante l'invio delle righe al client, ad esempio:

SELECT
FETCH
RECEIVE

Per tutte le altre istruzioni eseguite come parte di una stored procedure o di un batch è necessario attendere il completamento dell'esecuzione prima di potere eseguire le altre richieste MARS.

Il modo esatto in cui viene eseguito l'interleave dei batch è influenzato da una serie di fattori ed è difficile prevedere la sequenza esatta in cui vengono eseguiti i comandi da più batch contenenti specifici punti. Prestare attenzione in modo da evitare gli effetti collaterali indesiderati dovuti all'esecuzione interleaved di tali batch complessi.

Per evitare problemi, usare le chiamate API anziché le istruzioni Transact-SQL per gestire lo stato della connessione (SET, USE) e le transazioni (BEGIN TRAN, COMMIT, ROLLBACK) senza includere queste istruzioni nei batch costituiti da più istruzioni contenenti inoltre specifici punti e serializzando l'esecuzione di tali batch usando o cancellando tutti i risultati.

Nota

Un batch o una stored procedure che avvia una transazione manuale o implicita quando MARS è abilitato deve completare la transazione prima di poter uscire dal batch. In caso contrario, SQL Server esegue il rollback di tutte le modifiche apportate dalla transazione al termine del batch. Tale transazione è gestita da SQL Server come transazione con ambito batch. Si tratta di un nuovo tipo di transazione introdotto in SQL Server 2005 (9.x) per consentire l'uso delle stored procedure esistenti ben progettate quando MARS è abilitato. Per altre informazioni sulle transazioni con ambito batch, vedere Istruzioni Transaction (Transact-SQL).

Per un esempio dell'uso di MARS da ADO, vedere Uso di ADO con OLE DB Driver per SQL Server.

OLTP in memoria

OLTP in memoria supporta MARS con query e stored procedure compilate in modo nativo. MARS consente la richiesta di dati restituiti da più query senza la necessità di recuperare completamente ogni set di risultati prima di inviare una richiesta di recupero delle righe di un nuovo set di risultati. Per leggere correttamente i dati di più set di risultati aperti, è necessario usare una connessione abilitata per MARS.

Per impostazione predefinita, MARS è disabilitato. È pertanto necessario abilitarlo in modo esplicito aggiungendo MultipleActiveResultSets=True a una stringa di connessione. L'esempio seguente illustra come connettersi a un'istanza di SQL Server e specificare che MARS è abilitato:

Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=True

MARS con OLTP in memoria è essenzialmente uguale a MARS nel resto del motore SQL. Di seguito sono elencate le differenze nell'uso di MARS nelle tabelle ottimizzate per la memoria e nelle stored procedure compilate in modo nativo.

MARS e tabelle ottimizzate per la memoria

Di seguito sono riportate le differenze tra le tabelle basate su disco e quelle ottimizzate per la memoria quando si usa una connessione abilitata per MARS:

Due istruzioni possono modificare i dati nello stesso oggetto di destinazione, ma se entrambe tentano di modificare lo stesso record, la nuova operazione non riuscirà a causa di un conflitto di scrittura-scrittura. Tuttavia, se entrambe le operazioni modificano record diversi, le operazioni riusciranno.
Ogni istruzione viene eseguita in isolamento SNAPSHOT, quindi le nuove operazioni non possono vedere le modifiche apportate dalle istruzioni esistenti. Anche se le istruzioni simultanee vengono eseguite nella stessa transazione, il motore SQL crea transazioni con ambito batch isolate l'una dall'altra per ogni istruzione. Le transazioni con ambito batch rimangono tuttavia associate tra loro. Di conseguenza, il rollback di una transazione con ambito batch influisce sulle altre nello stesso batch.
Le operazioni DDL non sono consentite nelle transazioni utente e avranno esito negativo immediato.

MARS e stored procedure compilate in modo nativo

Le stored procedure compilate in modo nativo possono essere eseguite in connessioni abilitate per MARS e possono cedere il controllo dell'esecuzione a un'altra istruzione solo quando viene rilevato un punto specifico. Per un punto specifico è necessaria un'istruzione SELECT, ovvero l'unica all'interno di una stored procedure compilata in modo nativo che può cedere il controllo dell'esecuzione a un'altra istruzione. Se nella procedura non è presente un'istruzione SELECT, il controllo dell'esecuzione non verrà ceduto e la procedura continuerà fino al completamento prima che vengano avviate altre istruzioni.

MARS e transazioni OLTP in memoria

Le modifiche apportate dalle istruzioni e dai blocchi ATOMIC interleaved sono isolate l'una dall'altra. Se, ad esempio, un'istruzione o un blocco ATOMIC apporta alcune modifiche e quindi cede il controllo dell'esecuzione a un'altra istruzione, la nuova istruzione non vedrà le modifiche apportate dalla prima istruzione. Inoltre, quando la prima istruzione riprende l'esecuzione, non vedrà le modifiche apportate da altre istruzioni. Le istruzioni vedranno solo le modifiche completate e di cui è stato eseguito il commit prima dell'avvio dell'istruzione.

È possibile avviare una nuova transazione utente all'interno della transazione utente corrente usando l'istruzione BEGIN TRANSACTION. Questa operazione è supportata solo in modalità di interoperabilità. BEGIN TRANSACTION può pertanto essere chiamata solo da un'istruzione T-SQL e non dall'interno di una stored procedure compilata in modo nativo. È possibile creare un punto di salvataggio in una transazione usando SAVE TRANSACTION o una chiamata API a transaction.Save(save_point_name) per eseguire il rollback al punto di salvataggio. Anche questa funzionalità è abilitata solo da istruzioni T-SQL e non dall'interno di stored procedure compilate in modo nativo.

MARS e indici columnstore

SQL Server (a partire dalla versione 2016) supporta MARS con indici columnstore. SQL Server 2014 usa MARS per le connessioni di sola lettura alle tabelle con un indice columnstore. Tuttavia, SQL Server 2014 non supporta MARS per le operazioni simultanee di Data Manipulation Language (DML) su una tabella con indice columnstore. Quando questo si verifica, SQL Server termina le connessioni e interrompe le transazioni. SQL Server 2012 include indici columnstore di sola lettura e MARS non è applicabile.

Driver OLE DB per SQL Server

Il driver OLE DB per SQL Server supporta MARS tramite l'aggiunta della proprietà di inizializzazione dell'origine dati SSPROP_INIT_MARSCONNECTION, implementata nel set di proprietà DBPROPSET_SQLSERVERDBINIT. È stata aggiunta anche una nuova parola chiave, MarsConn, per la stringa di connessione. Accetta valori true o false; false è l'impostazione predefinita.

Il valore predefinito della proprietà dell'origine dati DBPROP_MULTIPLECONNECTIONS è VARIANT_TRUE. Ciò significa che il provider distribuirà più connessioni in modo da supportare più oggetti comando e set di righe simultanei. Quando MARS è abilitato, il driver OLE DB per SQL Server può supportare più oggetti comando e set di righe in una singola connessione, pertanto MULTIPLE_CONNECTIONS è impostato su VARIANT_FALSE per impostazione predefinita.

Per altre informazioni sui miglioramenti apportati al set di proprietà DBPROPSET_SQLSERVERDBINIT, vedere Proprietà di inizializzazione e di autorizzazione.

Esempio del driver OLE DB per SQL Server

In questo esempio viene creato un oggetto origine dati usando il driver OLE DB per SQL Server e viene abilitato MARS usando il set di proprietà DBPROPSET_SQLSERVERDBINIT prima che venga creato l'oggetto della sessione.

#include <msoledbsql.h>  
  
IDBInitialize *pIDBInitialize = NULL;  
IDBCreateSession *pIDBCreateSession = NULL;  
IDBProperties *pIDBProperties = NULL;  
  
// Create the data source object.  
hr = CoCreateInstance(CLSID_MSOLEDBSQL, NULL,  
   CLSCTX_INPROC_SERVER,  
   IID_IDBInitialize,   
    (void**)&pIDBInitialize);  
  
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void**)&pIDBProperties);  
  
// Set the MARS property.  
DBPROP rgPropMARS;  
  
// The following is necessary since MARS is off by default.  
rgPropMARS.dwPropertyID = SSPROP_INIT_MARSCONNECTION;  
rgPropMARS.dwOptions = DBPROPOPTIONS_REQUIRED;  
rgPropMARS.dwStatus = DBPROPSTATUS_OK;  
rgPropMARS.colid = DB_NULLID;  
V_VT(&(rgPropMARS.vValue)) = VT_BOOL;  
V_BOOL(&(rgPropMARS.vValue)) = VARIANT_TRUE;  
  
// Create the structure containing the properties.  
DBPROPSET PropSet;  
PropSet.rgProperties = &rgPropMARS;  
PropSet.cProperties = 1;  
PropSet.guidPropertySet = DBPROPSET_SQLSERVERDBINIT;  
  
// Get an IDBProperties pointer and set the initialization properties.  
pIDBProperties->SetProperties(1, &PropSet);  
pIDBProperties->Release();  
  
// Initialize the data source object.  
hr = pIDBInitialize->Initialize();  
  
//Create a session object from a data source object.  
IOpenRowset * pIOpenRowset = NULL;  
hr = IDBInitialize->QueryInterface(IID_IDBCreateSession, (void**)&pIDBCreateSession));  
hr = pIDBCreateSession->CreateSession(  
   NULL,             // pUnkOuter  
   IID_IOpenRowset,  // riid  
  &pIOpenRowset ));  // ppSession  
  
// Create a rowset with a firehose mode cursor.  
IRowset *pIRowset = NULL;  
DBPROP rgRowsetProperties[2];  
  
// To get a firehose mode cursor request a   
// forward only read only rowset.  
rgRowsetProperties[0].dwPropertyID = DBPROP_IRowsetLocate;  
rgRowsetProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[0].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[0].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[0].vValue));  
rgRowsetProperties[0].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[0].vValue.boolVal = VARIANT_FALSE;  
  
rgRowsetProperties[1].dwPropertyID = DBPROP_IRowsetChange;  
rgRowsetProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;  
rgRowsetProperties[1].dwStatus = DBPROPSTATUS_OK;  
rgRowsetProperties[1].colid = DB_NULLID;  
VariantInit(&(rgRowsetProperties[1].vValue));  
rgRowsetProperties[1].vValue.vt = VARIANT_BOOL;  
rgRowsetProperties[1].vValue.boolVal = VARIANT_FALSE;  
  
DBPROPSET rgRowsetPropSet[1];  
rgRowsetPropSet[0].rgProperties = rgRowsetProperties  
rgRowsetPropSet[0].cProperties = 2  
rgRowsetPropSet[0].guidPropertySet = DBPROPSET_ROWSET;  
  
hr = pIOpenRowset->OpenRowset (NULL,  
   &TableID,  
   NULL,  
   IID_IRowset,  
   1,  
   rgRowsetPropSet  
   (IUnknown**)&pIRowset);

Vedere anche

Driver OLE DB per funzionalità di SQL Server