Ridurre il carico di ottimizzazione del server di produzione

Si applica a:SQL ServerDatabase SQL di Azure

Consulente per l'ottimizzazione del motore di database si basa sull'ottimizzatore di interrogazioni per analizzare un carico di lavoro e fornire indicazioni di ottimizzazione. L'esecuzione di questa analisi sul server di produzione aumenta il carico del server e può ridurre le prestazioni del server durante la sessione di ottimizzazione. È possibile diminuire l'impatto sul carico del server durante una sessione di ottimizzazione utilizzando un server di prova oltre al server di produzione.

Come il Database Engine Tuning Advisor utilizza un server di prova

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 del Consulente di ottimizzazione del motore di database diminuisce.

Per evitare questi problemi, il Database Engine Tuning Advisor ottimizza un database su un server di produzione, trasferendo la maggior parte del carico di ottimizzazione a 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. Database Engine Tuning Advisor non copia i dati effettivi dal server di produzione al server di prova. Copia 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. È quindi necessario creare l'utente e il relativo account di accesso nel server di test. 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 usare 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, il Database Engine Tuning Advisor crea un database di shell sul server di prova. Per creare questo scheletro di database e ottimizzarlo, l’Advisor di Ottimizzazione del Motore di Database esegue chiamate al server di produzione per gli elementi seguenti:

   1. Database Engine Tuning Advisor importa i metadati dal database di produzione nel database shell 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. Database Engine Tuning Advisor (Strumento di Ottimizzazione del Motore di Database) importa le statistiche dal server di produzione in modo che il Query Optimizer possa ottimizzare accuratamente le query sul server di prova.

   3. Consulente per l'ottimizzazione del motore di database importa i parametri hardware specificando il numero di processori e la memoria disponibile dal server di produzione per fornire all'ottimizzatore delle query le informazioni necessarie per generare un piano di query.

3. Dopo aver ottimizzato il database di base del server di prova, l'ottimizzazione guidata motore di database genera una raccomandazione 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 funzionalità di ottimizzazione del server di prova non è supportata nell'interfaccia utente grafica (GUI) del Consulente per l'ottimizzazione del 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 test, puoi definire la sessione di ottimizzazione del server di test nel file di input XML dell'advisor di ottimizzazione motore di database. Nell'esempio di file di input XML seguente viene illustrato come specificare un server di prova per ottimizzare un database con Database Engine Tuning Advisor.

Nell'esempio, il database MyDatabaseName viene ottimizzato su MyServerName. Lo script Transact-SQL MyWorkloadScript.sql viene utilizzato come carico di lavoro. Questo carico di lavoro contiene eventi che vengono 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 lo scarico dell'overhead di regolazione sul server di prova. Quando il Motore di Database Ottimizzazione Guidata genera la propria raccomandazione di ottimizzazione utilizzando questo file di input XML, dovrebbe prendere in considerazione solo gli indici (<FeatureSet>IDX</FeatureSet>), senza partizionamento, e non è necessario mantenere alcuna 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="http://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>  

Vedi anche

Considerazioni relative all'uso di server di prova
Guida di riferimento ai file di input XML (Ottimizzazione guidata motore di database)