Microsoft SQL Server e Linked Server, come aumentare le prestazioni

Dark

8 anni fa

Come certamente saprete se vi siete imbattuti in questo post, con il termine Linked Server si intende una base dati collegata in modo permanente a una istanza di MS SQL Server alla quale quest'ultima può accedere mediante una connessione di tipo OLE DB. In altre parole, va immaginato come un server collegato al nostro SQL Server e accessibile mediante istruzioni di tipo Transact-SQL come se si trattasse di un database interno. Ovviamente, sia le attività di connessione al Linked Server che quelle di consultazione dei dati contenuti viene effettuata internamente da SQL Server, attività che ha spesso un considerevole impatto in termini di performance.

Per ulteriori informazioni su cosa è un Linked Server, è possibile consultare questa pagina ufficiale Microsoft. Per istruzioni su come configurare uno (o più) Linked Server all'interno di una istanza MS SQL Server, consigliamo di consultare questo ottimo articolo della KB ufficiale, disponibile per tutte le versioni di SQL Server dal 2008 in poi. In questo articolo affronteremo i problemi legati alle prestazioni di un Linked Server esistente, nel tentativo di fornire alcuni utili suggerimenti volti a incrementare le performance del nostro sistema.

Controllare i permessi

La prima cosa da fare per assicurarsi che il Linked Server sia configurato per funzionare in modo ottimale è controllare che l'utente utilizzato per creare la connessione abbia i seguenti permessi:

sysadmin
db_owner
db_ddladmin

In mancanza di questi non sarà infatti possibile utilizzare i comandi STATS, indispensabili per acquisire informazioni relative alla struttura dati collegata e recuperare i dati in modo efficiente. Si tratta senza ombra di dubbio della causa principale del degrado prestazionale dei Linked Server, dovuta al fatto che molti amministratori di sistema, per ragioni di sicurezza, si limitano a fornire il permesso di sola lettura (db_datareader), ignorando che in questo modo creano un vero e proprio collo di bottiglia prestazionale alla connessione.

Utilizzare la modalità pass-through

Come probabilmente saprete già, i dati contenuti in un Linked Server possono essere recuperati sostanzialmente in due modi:

Delegando SQL Server ad effettuare le operazioni di recupero dati.
Eseguendo le query in modalità ''pass-through'', ovvero utilizzando il motore (DB Engine) interno allo stesso Linked Server per l'esecuzione delle funzioni/relazioni e per il recupero dei dati.

Entrambe le soluzioni hanno i loro vantaggi e svantaggi.

Modalità SQL Server

La prima è ideale dal punto di vista della praticità, in quanto consente di utilizzare il "dialetto" Transact-SQL proprio di SQL Server per accedere ai dati: in altre parole, possiamo lavorare sul Linked Server come se fosse un database interno.

Ad esempio, per recuperare le prime 10 righe da una tabella situata su un Linked Server sarà sufficiente eseguire la query seguente:

SELECT TOP 10 ROWS FROM [linkedservername].[databasename].[schemaname].[tablename]

1	SELECT TOP 10 ROWS FROM [linkedservername].[databasename].[schemaname].[tablename]

Lo svantaggio, ovviamente, è che tutte le operazioni di data-filter (WHERE, ON et. al.) e/o data-relation (JOIN, MERGE et. al.) saranno compiute da SQL Server dopo il data-retrieval di tutti i record e i campi interessati: inutile dire che questo si traduce in un forte degrado prestazionale, grossomodo proporzionale alla complessità della query e alla quantità di dati da analizzare.

Modalità Pass-Through

Per questo motivo, in tutti i casi in cui il Linked Server è dotato a sua volta di un DB Engine e si ha interesse ad ottenere le migliori prestazioni possibili, è consigliabile adottare una tecnica detta pass-through. Quest'ultima di fatto consente di inviare al Linked Server una query scritta nel suo linguaggio nativo, il cui risultato sarà fedelmente riportato da SQL Server: in buona sostanza, SQL Server si limiterà a fare da proxy, mettendo in comunicazione i due attori coinvolti nell'operazione di richiesta e recupero dati.

Questo è un esempio della query di cui sopra effettuata in modalità pass-through su un Linked Server in tecnologia MS Access:

SELECT * FROM OPENQUERY('[linkedservername]', 'SELECT TOP 10 ROWS FROM [tablename]' );

1	SELECT * FROM OPENQUERY('[linkedservername]', 'SELECT TOP 10 ROWS FROM [tablename]' );

L'istruzione di cui sopra utilizza il comando OPENQUERY, specifico per effettuare query singole su un Linked Server esistente e opportunamente configurato.

Pass-Through senza Linked Server

E' interessante notare che la modalità pass-through può essere utilizzata anche per effettuare connessioni a Data Source che non siano stati configurati come Linked Server in precedenza: tale tecnica fa uso del comando OPENROWSET, il quale può essere utilizzato come nell'esempio seguente:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    '\\path\to\DBAccessFile.mdb'; 'DBusername'; 'DBpassword', 
    'SELECT TOP 10 ROWS FROM [tablename]' );

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'\\path\to\DBAccessFile.mdb'; 'DBusername'; 'DBpassword',

'SELECT TOP 10 ROWS FROM [tablename]' );

Oppure, nel caso in cui il Data Source a cui interessa collegarsi sia comunque accessibile tramite connection string di tipo SQL Native Client Provider, nel seguente modo:

SELECT * FROM OPENROWSET('SQLNCLI', 
    'Server=servername\instancename;UID=username;PWD=password;', 
    'SELECT TOP 10 ROWS FROM [tablename]' );

SELECT * FROM OPENROWSET('SQLNCLI',

'Server=servername\instancename;UID=username;PWD=password;',

'SELECT TOP 10 ROWS FROM [tablename]' );

Ovviamente, in tutti questi i casi verranno utilizzati gli indici e le relazioni eventualmente presenti sul Data Source di destinazione, con un considerevole incremento di prestazioni rispetto al metodo indicato in precedenza.

Per il momento è tutto: felice recupero dati!

Print Friendly & PDF Download