Riduzione del carico di ottimizzazione del server di produzione

L'ottimizzazione di un carico di lavoro esteso può creare un overhead significativo sul server in fase di ottimizzazione, L'overhead deriva dalle numerose chiamate eseguite da Ottimizzazione guidata motore di database a Query Optimizer durante il processo di ottimizzazione. È possibile eliminare questo problema utilizzando un server di prova in aggiunta a quello di produzione.

Modalità di utilizzo di un server di prova da parte di Ottimizzazione guidata motore di database

L'utilizzo tradizionale di un server di prova consiste nel copiare tutti i dati dal server di produzione sul server di prova, ottimizzare quest'ultimo e quindi implementare l'indicazione sul server di produzione. Questo processo elimina l'effetto sulle prestazioni del server di produzione, ma non rappresenta comunque la soluzione ideale. Ad esempio, la copia di grandi quantità di dati dal server di produzione sul server di prova può richiedere notevoli quantità di tempo e risorse. Inoltre, l'hardware del server di prova raramente è potente quanto quello utilizzato per i server di produzione. Il processo di ottimizzazione si basa su Query Optimizer e le indicazioni da esso generate dipendono in parte dall'hardware sottostante. Se l'hardware del server di prova e di produzione non sono identici, la qualità delle indicazioni di Ottimizzazione guidata motore di database ne risente.

Per evitare questi problemi, Ottimizzazione guidata motore di database esegue l'ottimizzazione di un database su un server di produzione ripartendo la maggioranza del carico di lavoro di ottimizzazione su un server di prova. Questo avviene utilizzando le informazioni di configurazione hardware del server di produzione e senza copiare effettivamente i dati dal server di produzione sul server di prova. Ottimizzazione guidata motore di database non copia i dati effettivi dal server di produzione sul server di prova, ma solo i metadati e le statistiche necessarie.

Nella procedura seguente viene illustrato il processo per l'ottimizzazione di un database di produzione su un server di prova:

1. Verificare che l'utente che desidera utilizzare il server di prova sia presente su entrambi i server.
   Prima di iniziare, verificare che l'utente che desidera utilizzare il server di prova per l'ottimizzazione del database sul server di produzione sia presente su entrambi i server. Questo richiede la creazione dell'utente e del relativo account di accesso sul server di prova. Per i membri del ruolo predefinito del server sysadmin su entrambi i computer, questo passaggio non è necessario.
2. Ottimizzazione del carico di lavoro sul server di prova.
   Per ottimizzare un carico di lavoro su un server di prova, è necessario utilizzare un file di input XML con l'utilità della riga di comando dta. Nel file di input XML, specificare il nome del server di prova con l'elemento secondario TestServer oltre ai valori per gli altri elementi secondari dell'elemento padre TuningOptions.
   Durante il processo di ottimizzazione, Ottimizzazione guidata motore di database crea uno scheletro di database sul server di prova. Per creare questo scheletro di database e ottimizzarlo, Ottimizzazione guidata motore di database esegue chiamate al server di produzione per gli elementi seguenti:
   1. Ottimizzazione guidata motore di database importa metadati dal database di produzione sullo scheletro di database del server di prova. Questi metadati includono tabelle vuote, indici, viste, stored procedure, trigger e così via. Questo rende possibile l'esecuzione delle query del carico di lavoro sullo scheletro di database del server di prova.
   2. Ottimizzazione guidata motore di database importa le statistiche dal server di produzione in modo che Query Optimizer possa ottimizzare in modo accurato le query sul server di prova.
   3. Ottimizzazione guidata motore di database importa i parametri hardware specificando il numero di processori e la memoria disponibile dal server di produzione per offrire a Query Optimizer le informazioni necessarie per generare un piano della query.
3. Dopo aver terminato l'ottimizzazione dello scheletro di database del server di prova, Ottimizzazione guidata motore di database genera un'indicazione di ottimizzazione.
4. Applicare l'indicazione derivata dall'ottimizzazione del server di prova al server di produzione.

Nella seguente figura viene illustrato lo scenario relativo al server di prova e al server di produzione:

[!NOTA] La funzione di ottimizzazione del server di prova non è supportata nell'interfaccia utente grafica (GUI) di Ottimizzazione guidata motore di database.

Esempio

Innanzitutto verificare che l'utente che desidera eseguire l'ottimizzazione sia presente sul server di prova e di produzione.

Dopo aver copiato le informazioni utente sul server di prova, è possibile definire la sessione di ottimizzazione del server di prova nel file di input XML di Ottimizzazione guidata motore di database. Nell'esempio di file di input XML seguente viene illustrato come specificare un server di prova per ottimizzare un database tramite Ottimizzazione guidata motore di database.

Nell'esempio, il database MyDatabaseName viene ottimizzato su MyServerName. Lo script Transact-SQL, MyWorkloadScript.sql, viene utilizzato come carico di lavoro. Esso include gli eventi eseguiti su MyDatabaseName. La maggioranza delle chiamate di Query Optimizer al database, che si verificano nell'ambito del processo di ottimizzazione, vengono gestite dallo scheletro di database che risiede su MyTestServerName. Lo scheletro di database è costituito da metadati e statistiche. Questo processo determina la ripartizione del carico dell'overhead di ottimizzazione sul server di prova. Quando Ottimizzazione guidata motore di database genera la propria indicazione di ottimizzazione utilizzando questo file di input XML, dovrebbe considerare solo gli indici (<FeatureSet>IDX</FeatureSet>), nessun partizionamento e non dovrebbe essere necessario mantenere nessuna delle strutture di progettazione fisica esistenti in MyDatabaseName.

<?xml version="1.0" encoding="utf-16" ?>
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://schemas.microsoft.com/sqlserver/2004/07/dta">
  <DTAInput>
    <Server>
      <Name>MyServerName</Name>
      <Database>
        <Name>MyDatabaseName</Name>
      </Database>
    </Server>
    <Workload>
      <File>MyWorkloadScript.sql</File>
    </Workload>
    <TuningOptions>
      <TestServer>MyTestServerName</TestServer>
      <FeatureSet>IDX</FeatureSet>
      <Partitioning>NONE</Partitioning>
      <KeepExisting>NONE</KeepExisting>
    </TuningOptions>
  </DTAInput>
</DTAXML>

Vedere anche

Concetti

Considerazioni relative all'utilizzo di server di prova

Altre risorse

Guida di riferimento ai file di input XML (DTA)

Guida in linea e informazioni

Assistenza su SQL Server 2005