Riduzione del carico di ottimizzazione del server di produzione
Ottimizzazione guidata Motore di database si basa su Query Optimizer 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.
È possibile inoltre ridurre l'impatto di Ottimizzazione guidata Motore di database sul carico del server tramite SQL Server 2008. In versioni precedenti di Ottimizzazione guidata Motore di database può essere provocata la ricompilazione di piani che fanno riferimento a tabelle ottimizzate da parte di Query Optimizer. La ricompilazione viene eseguita quando Ottimizzazione guidata Motore di database invalida piani nella cache delle procedure durante una sessione di ottimizzazione solo dopo la prima esecuzione della query dopo una sessione di ottimizzazione. Ottimizzazione guidata Motore di database di SQL Server 2008 non invalida alcun piano nella cache delle procedure, eliminando in questo modo tali ricompilazioni aggiuntive.
Le informazioni seguenti descrivono il modo in cui utilizzare un server di prova con Ottimizzazione guidata Motore di database.
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 in un server di produzione ripartendo la maggioranza del carico di ottimizzazione in 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:
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.
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:
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.
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.
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 di query.
Dopo avere terminato l'ottimizzazione dello scheletro di database del server di prova, Ottimizzazione guidata Motore di database genera un'indicazione di ottimizzazione.
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:
.gif "Utilizzo del server di prova con Ottimizzazione guidata motore di database")
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 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="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