Come generare uno script di statistiche per creare un database solo statistiche in SQL Server

Questo articolo illustra come generare uno script di statistiche usando i metadati del database per la creazione di un database solo statistiche in SQL Server.

Versione originale del prodotto: SQL Server 2014, SQL Server 2012, SQL Server 2008

Numero KB originale: 914288

Introduzione

DBCC CLONEDATABASE è il metodo preferito per generare un clone solo dello schema di un database per analizzare i problemi di prestazioni. Usare la procedura descritta in questo articolo solo quando non si è in grado di usare DBCC CLONEDATABASE.

Query Optimizer in Microsoft SQL Server usa i tipi di informazioni seguenti per determinare un piano di query ottimale:

  • metadati del database
  • ambiente hardware
  • stato sessione database

In genere, è necessario simulare tutti questi stessi tipi di informazioni per riprodurre il comportamento di Query Optimizer in un sistema di test.

Il Servizio Supporto Tecnico Clienti Microsoft potrebbe chiedere di generare uno script dei metadati del database per analizzare un problema di Query Optimizer. Questo articolo descrive i passaggi per generare lo script di statistiche e descrive anche come query Optimizer usa le informazioni.

Nota

Le chiavi salvate all'interno di questi dati potrebbero contenere informazioni personali. Ad esempio, se la tabella contiene una colonna Numero di telefono con una statistica, il valore della chiave elevata di ogni passaggio sarà nello script di statistiche generato.

Creare script per l'intero database

Quando si genera un database clone solo statistiche, potrebbe essere più semplice e affidabile creare script per l'intero database anziché creare script per singoli oggetti. Quando si esegue lo script dell'intero database, si ottengono i vantaggi seguenti:

  • È possibile evitare problemi con oggetti dipendenti mancanti necessari per riprodurre il problema.
  • Sono necessari meno passaggi per selezionare gli oggetti necessari.

Si noti che se si genera uno script per un database e i metadati per il database contengono migliaia di oggetti, il processo di scripting utilizza risorse CPU significative. È consigliabile generare lo script durante le ore non di punta oppure usare la seconda opzione Script Individual Objects per generare lo script per i singoli oggetti.

Per creare script per ogni database a cui fa riferimento la query, seguire questa procedura:

  1. Aprire il SQL Server Management Studio.

  2. Nel Esplora oggetti espandere Database e quindi individuare il database da creare come script.

  3. Fare clic con il pulsante destro del mouse sul database, scegliere Attività e quindi selezionare Genera script.

  4. Nella creazione guidata script verificare che sia selezionato il database corretto. Fare clic per selezionare Script entire database and all database objects (Script intero database e tutti gli oggetti di database) e quindi selezionare Avanti.

  5. Nella finestra di dialogo Scegli opzioni script selezionare il pulsante Avanzate per modificare le impostazioni seguenti dal valore predefinito al valore elencato nella tabella seguente.

    Opzione di scripting Valore da selezionare
    Spaziatura interna ansi Vero
    Continua scripting in caso di errore Vero
    Generare script per oggetti dipendenti Vero
    Includere i nomi dei vincoli di sistema Vero
    Regole di confronto dello script Vero
    Account di accesso degli script Vero
    Autorizzazioni a livello di oggetto script Vero
    Statistiche script Statistiche di script e istogrammi
    Indici script Vero
    Trigger di script Vero

    Nota

    Si noti che l'opzione Script Logins e l'opzione Script Object Level Permissions potrebbero non essere necessarie a meno che lo schema non contenga oggetti di proprietà di account di accesso diversi da dbo.

  6. Selezionare OK per salvare le modifiche e chiudere la pagina Opzioni di scripting avanzate .

  7. Selezionare Salva nel file e selezionare l'opzione File singolo .

  8. Esaminare le selezioni e selezionare Avanti.

  9. Seleziona Fine.

Script di singoli oggetti

È possibile creare script solo per i singoli oggetti a cui fa riferimento una particolare query anziché creare script per il database completo. Tuttavia, a meno che non siano stati creati tutti gli oggetti di database usando la WITH SCHEMABINDING clausola , le informazioni sulle dipendenze nella tabella di sys.depends sistema potrebbero non essere sempre accurate. Questa imprecisione potrebbe causare uno dei problemi seguenti:

  • Il processo di scripting non esegue lo script di un oggetto dipendente.

  • Il processo di scripting potrebbe creare script per gli oggetti nell'ordine errato. Per eseguire correttamente lo script, è necessario modificare manualmente lo script generato.

Pertanto, non è consigliabile creare script per singoli oggetti a meno che il database non disponga di molti oggetti e la creazione di script altrimenti richiederebbe troppo tempo. Se è necessario usare singoli oggetti script, seguire questa procedura:

  1. Nel SQL Server Management Studio espandere Database e quindi individuare il database da creare come script.

  2. Fare clic con il pulsante destro del mouse sul database, scegliere Script DatabaseAs, quindi CREATE To e quindi selezionare File.

  3. Immettere un nome file e quindi selezionare Salva.

    Verrà eseguito lo script del contenitore di database di base. Questo contenitore include file, gruppi di file, database e proprietà.

  4. Fare clic con il pulsante destro del mouse sul database, scegliere Attività e quindi selezionare Genera script.

  5. Assicurarsi che sia selezionato il database corretto e quindi selezionare Avanti.

  6. Nella finestra di dialogo Scegli tipi di oggetto scegliere Seleziona oggetti di database specifici e selezionare tutti i tipi di oggetti di database a cui fa riferimento la query problematica.

    Ad esempio, se la query fa riferimento solo alle tabelle, selezionare Tabelle. Se la query fa riferimento a una visualizzazione, selezionare Visualizzazioni e tabelle. Se la query problematica usa una funzione definita dall'utente, selezionare Funzioni.

  7. Dopo aver selezionato tutti i tipi di oggetto a cui fa riferimento la query, selezionare Avanti.

  8. Nella finestra di dialogo Imposta opzioni di scripting selezionare il pulsante Avanzate e modificare le impostazioni seguenti dal valore predefinito al valore elencato nella tabella seguente nella pagina Opzioni avanzate di scripting .

    Opzione di scripting Valore da selezionare
    Spaziatura interna ansi Vero
    Continua scripting in caso di errore Vero
    Includere i nomi dei vincoli di sistema Vero
    Generare script per oggetti dipendenti Vero
    Regole di confronto dello script Vero
    Account di accesso degli script Vero
    Autorizzazioni a livello di oggetto script Vero
    Statistiche script Statistiche di script e istogrammi
    Script USE DATABASE Vero
    Indici script Vero
    Trigger di script Vero

    Nota

    Si noti che le opzioni Account di accesso script e Autorizzazioni a livello di oggetto script potrebbero non essere necessarie a meno che lo schema non contenga oggetti di proprietà di account di accesso diversi da dbo.

  9. Selezionare OK per salvare e chiudere la pagina Opzioni di scripting avanzate .

    Viene visualizzata una finestra di dialogo per ogni tipo di oggetto di database selezionato nel passaggio 7.

  10. In ogni finestra di dialogo selezionare le tabelle, le viste, le funzioni o altri oggetti di database specifici e quindi selezionare Avanti.

  11. Selezionare l'opzione Script in file e quindi specificare lo stesso nome file immesso nel passaggio 3.

  12. Selezionare Fine per avviare lo scripting.

    Al termine dello scripting, inviare il file di script all'supporto tecnico Microsoft Engineer. Il tecnico supporto tecnico Microsoft potrebbe anche richiedere le informazioni seguenti:

    • Configurazione hardware, incluso il numero di processori e la quantità di memoria fisica esistente.

    • Opzioni SET attive durante l'esecuzione della query.

    Si noti che queste informazioni potrebbero essere già state fornite inviando un report SQLDiag o una traccia di SQL Profiler. È anche possibile che sia stato usato un altro metodo per fornire queste informazioni.

Modalità di utilizzo delle informazioni

Le tabelle seguenti illustrano come Query Optimizer usa queste informazioni per selezionare un piano di query.

Metadati

Opzione Spiegazione
Vincoli Query Optimizer usa spesso i vincoli per rilevare le contraddizioni tra la query e lo schema sottostante. Ad esempio, se la query contiene la WHERE col = 5 clausola e nella tabella sottostante esiste un CHECK (col < 5) vincolo, Query Optimizer sa che nessuna riga corrisponderà. Query Optimizer effettua tipi simili di deduzioni sulla nullability. Ad esempio, la WHERE col IS NULL clausola è nota per essere true o false a seconda della nullability della colonna e se la colonna proviene dalla tabella esterna di un outer join. La presenza di vincoli FOREIGN KEY è utile per determinare la cardinalità e l'ordine di join appropriato. Query Optimizer può usare le informazioni sui vincoli per eliminare i join o semplificare i predicati. Queste modifiche potrebbero rimuovere il requisito di accesso alle tabelle di base.
Statistiche Le informazioni statistiche contengono densità e un istogramma che mostra la distribuzione della colonna iniziale della chiave di indice e statistiche. A seconda della natura del predicato, Query Optimizer può usare densità, istogramma o entrambi per stimare la cardinalità di un predicato. Le statistiche aggiornate sono necessarie per stime accurate della cardinalità. Le stime di cardinalità vengono usate come input per stimare il costo di un operatore. È pertanto necessario disporre di stime di cardinalità valide per ottenere piani di query ottimali.
Dimensioni della tabella (numero di righe e pagine) Query Optimizer usa gli istogrammi e la densità per calcolare la probabilità che un predicato specificato sia true o false. La stima della cardinalità finale viene calcolata moltiplicando la probabilità per il numero di righe restituite dall'operatore figlio. Il numero di pagine nella tabella o nell'indice è un fattore di stima del costo di I/O. Le dimensioni della tabella vengono usate per calcolare il costo di un'analisi ed è utile quando si stima il numero di pagine a cui si accede durante una ricerca di indice.
Opzioni del database Diverse opzioni di database possono influire sull'ottimizzazione. Le AUTO_CREATE_STATISTICS opzioni e AUTO_UPDATE_STATISTICS influiscono sul fatto che Query Optimizer creerà nuove statistiche o aggiornerà statistiche non aggiornate. Il livello di parametrizzazione influisce sulla modalità di parametrizzazione della query di input prima che la query di input venga passata a Query Optimizer. La parametrizzazione può influire sulla stima della cardinalità e può anche impedire la corrispondenza con viste indicizzate e altri tipi di ottimizzazioni. L'impostazione DATE_CORRELATION_OPTIMIZATION fa sì che l'utilità di ottimizzazione cerchi le correlazioni tra le colonne. Questa impostazione influisce sulla cardinalità e sulla stima dei costi.

Ambiente

Opzione Spiegazione
Opzioni set di sessioni L'impostazione ANSI_NULLS influisce sul fatto che l'espressione NULL = NULL restituisca true. La stima della cardinalità per gli outer join potrebbe cambiare a seconda dell'impostazione corrente. Inoltre, anche le espressioni ambigue potrebbero cambiare. Ad esempio, l'espressione col = NULL valuta in modo diverso in base all'impostazione. Tuttavia, l'espressione col IS NULL valuta sempre lo stesso modo.
Risorse hardware Il costo per gli operatori di ordinamento e hash dipende dalla quantità relativa di memoria disponibile per SQL Server. Ad esempio, se le dimensioni dei dati sono maggiori della cache, Query Optimizer sa che i dati devono essere sempre spooling su disco. Tuttavia, se le dimensioni dei dati sono molto più piccole rispetto alla cache, è probabile che l'operazione venga eseguita in memoria. SQL Server considera anche ottimizzazioni diverse se il server dispone di più processori e se il parallelismo non è stato disabilitato usando un MAXDOP hint o l'opzione di configurazione max degree of parallelism.

Vedere anche