Riduzione del carico di ottimizzazione del server di produzione

Ottimizzazione guidata motore di database si basa sull'utilità di ottimizzazione query per analizzare un carico di lavoro e per apportare raccomandazioni 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.

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 test e di produzione non è identico, la qualità di raccomandazione Ottimizzazione guidata motore di database è diminuita.

Per evitare questi problemi, Ottimizzazione guidata motore di database modificare un database in un server di produzione scaricando la maggior parte del carico di ottimizzazione su un server di test. 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 al server di test. 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 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, 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 al database della shell del server di test. 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 statistiche dal server di produzione in modo che query optimizer possa ottimizzare in modo accurato le query nel server di test.

   3. Ottimizzazione guidata motore di database importa parametri hardware che specificano il numero di processori e la memoria disponibile dal server di produzione per fornire alle query optimizer le informazioni necessarie per generare un piano di query.

3. Al termine dell'ottimizzazione del database della shell del server di test, 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 test non è supportata nella Ottimizzazione guidata motore di database interfaccia utente grafica (GUI).

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 nel server di test, è possibile definire la sessione di ottimizzazione del server di test nel file di input XML Ottimizzazione guidata motore di database. Il file di input XML di esempio seguente illustra come specificare un server di test per ottimizzare un database con Ottimizzazione guidata motore di database.

Nell'esempio, il database MyDatabaseName viene ottimizzato su MyServerName. Lo script Transact-SQL, MyWorkloadScript.sql, viene usato 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 raccomandazione di ottimizzazione usando questo file di input XML, deve considerare gli indici solo (<FeatureSet>IDX</FeatureSet>), nessun partizionamento e non deve mantenere alcuna delle strutture di progettazione fisiche 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

Considerazioni sull'uso dei filedi input XML server di test (Ottimizzazione guidata motore di database)