FunzionalitÓ di manutenzione di statistiche (autostats) in SQL Server

Traduzione articoli Traduzione articoli
Identificativo articolo: 195565 - Visualizza i prodotti a cui si riferisce l?articolo.
Espandi tutto | Chiudi tutto

In questa pagina

Sommario

La funzionalitÓ introdotte nuove statistiche manutenzione, AutoStat, potrebbe generare indesiderato sovraccarico in un sistema di produzione effettuando una delle seguenti operazioni:
  • Avvio statistici aggiornamenti durante i periodi intenso di produzione.

    - oppure -
  • Avvio un numero troppo alto di processi di UPDATE STATISTICS in un determinato punto nel tempo.
Lo scopo di questo articolo Ŕ descrivere in dettaglio le condizioni in cui Ŕ possibile aspettarsi vedere autostats generato e in esecuzione su tabelle in un database di UPDATE STATISTICS.

Per informazioni su autostats in SQL Server 2000, vedere "Statistiche utilizzate da Query Optimizer in Microsoft SQL Server 2000" nel sito Web MSDN riportato di seguito:
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx


Nota Se si utilizza Microsoft SQL Server 2005, vedere il white paper seguenti di Microsoft per informazioni su come statistiche vengono utilizzate da query optimizer in SQL Server 2005:
http://technet.microsoft.com/en-us/library/cc966419.aspx

Informazioni

Informazioni generali

SQL Server utilizza un ottimizzatore basato sui costi che pu˛ essere estremamente riservato a informazioni statistiche che viene fornite in tabelle e indici. Senza informazioni di statistiche corrette e aggiornate, SQL Server pu˛ essere richieste per determinare il piano di esecuzione migliore per una particolare query.

Statistiche gestite in ogni tabella in SQL Server per facilitare l'ottimizzatore basato sul costo decisionale includono il:
  • Numero di righe nella tabella.
  • Numero di pagine utilizzate dalla tabella.
  • Numero di modifiche apportate alle chiavi della tabella dall'ultimo aggiornamento per le statistiche.
Ulteriori informazioni sono memorizzate per gli indici, inclusi (per ogni indice):
  • Un istogramma equi l'altezza della prima colonna.
  • DensitÓ su tutti i prefissi di colonna.
  • Lunghezza media della chiave.
Ogni volta che viene creato un nuovo indice vengono create automaticamente statistiche sugli indici. Inoltre, Ŕ ora possibile creare e gestire le statistiche di altre colonne nonchÚ.

Per mantenere le informazioni statistiche in modo aggiornato possibile, SQL Server introduce la AutoStat, che, tramite il monitoraggio di SQL Server delle modifiche della tabella, Ŕ in grado di aggiornare automaticamente le statistiche per una tabella quando viene raggiunto una determinata soglia di modifica. Inoltre, server SQL Ŕ stata introdotta automatico-creare-statistiche, il server generare automaticamente tutte le statistiche necessarie per l'ottimizzazione di precisa di una query specifica.

Determinare quando Ŕ imminente AutoStat generazione

Come accennato in precedenza, AutoStat aggiornerÓ automaticamente le statistiche per una particolare tabella quando viene raggiunto "Modifica threshold". La colonna sysindexes.rowmodctr mantiene un'esecuzione totale tutte le modifiche apportate a una tabella, con il passare del tempo, possono influire negativamente sul decisione rendendo processo del processore di query. Questo contatore viene aggiornato ogni volta che si verifica uno degli eventi seguenti casi:
  • Viene eseguito l'inserimento di un singola riga.
  • Viene effettuata un'eliminazione singola riga.
  • Viene effettuato un aggiornamento di una colonna indicizzata.
Nota : TRUNCATE TABLE non aggiorna rowmodctr.

Dopo l'aggiornamento delle statistiche di tabella, il valore rowmodctr viene reimpostato su 0 e Statistiche schema versione della tabella viene aggiornata.

Inoltre, in situazioni in cui viene eseguita piano di esecuzione di una stored procedure dalla cache e piano Ŕ sensibile alle statistiche, la versione dello schema di statistiche verrÓ confrontati con la versione corrente. Se sono disponibili nuove statistiche, Ŕ possibile che il piano per la stored procedure verrÓ ricompilato.

L'algoritmo di base per auto update statistics Ŕ:
  • Se la cardinalitÓ di una tabella Ŕ inferiore a sei e si trova la tabella nel database tempdb, automatica aggiornamento con ogni sei modifiche alla tabella.
  • Se la cardinalitÓ di una tabella Ŕ maggiore di 6, ma minore o uguale a 500, Aggiorna stato ogni 500 modifiche.
  • Se la cardinalitÓ di una tabella Ŕ maggiore di 500, Aggiorna le statistiche quando (500 + 20 % della tabella) sono state apportate modifiche.
  • Per le variabili di tabella, le modifiche di cardinalitÓ non viene attivato auto update statistics.
Nota : in questo senso strictest, SQL Server conta cardinalitÓ come il numero di righe della tabella.

Nota : oltre a cardinalitÓ, la selettivitÓ del predicato interessa anche AutoStats generazione. Ci˛ significa che le statistiche potrebbero non essere aggiornato afer ogni 500 modifiche se cardinalitÓ < 500 o per ogni 20 % delle modifiche se cardinalitÓ sono stati > 500. Una scala di fattore (valore varia da 1 a 4, 1 e 4 inclusi) viene generata in base la selettivitÓ e un prodotto di questo fattore e il numero di modifiche, ottenute dall'algoritmo sarebbe il numero effettivo di modifiche necessarie per la generazione di AutoStats.

L'algoritmo precedente pu˛ essere summarised sotto forma di una tabella:
_________________________________________________________________________________
 Table Type | Empty Condition | Threshold When Empty |Threshold When Not Empty 
_________________________________________________________________________________
 Permanent  | < 500 rows      | # of Changes >= 500  | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
 Temporary  | < 6 rows        | # of Changes >= 6    | # of Changes >= 500 + (20% of Cardinality)
___________________________________________________________________________
Table
Variables   | Change in cardinality does not affect AutoStats generation.
___________________________________________________________________________
di seguito Ŕ riportati due esempi per illustrare questo concetto:

Esempio 1

Si consideri la tabella authors nel database pubs, che contiene 23 righe e presenta due indici. L'indice cluster univoco UPKCL_auidind, viene indicizzato in una colonna, ovvero au_id, e un indice non cluster composto aunmind, sia stato creato per le colonne au_lname e au_fname. PoichÚ questa tabella contiene meno di 500 righe, AutoStat inizierÓ dopo sono state apportate 500 modifiche ai dati della tabella. Le modifiche possibile uno o pi¨ di 500 o pi¨ inserisce, Elimina, si passa a una colonna indicizzata, ad esempio au_lname o qualsiasi combinazione di questi.

╚ pertanto possibile prevedere quando saranno iniziate UPDATE STATISTICS monitorando il valore di sysindexes.rowmodctr, che viene incrementato dopo ogni aggiornamento. Quando raggiunge o supera le 500 unitÓ, Ŕ possibile prevedere UPDATE STATISTICS per l'avvio.

Esempio 2

Si consideri una seconda tabella, t2, che ha una cardinalitÓ di 1.000. Per le tabelle con maggiore di 500 righe, SQL Server verrÓ UPDATE STATISTICS quando (500 + 20 %) sono state apportate modifiche. Eseguendo i calcoli, 20 % delle 1.000 Ŕ 200, pertanto Ŕ possibile aspettarsi vedere AutoStat avviare dopo che sono state apportate modifiche circa 700 alla tabella.

Determinazione di automazione Autostats

Per automatizzare la determinazione di quando verrÓ eseguito AutoStat, Ŕ possibile eseguire il polling della tabella sysindexes e identificare quando le modifiche apportate alla tabella sono di raggiungere il punto di partenza. Di seguito Ŕ riportato un algoritmo di base per questa operazione:
   if (sysindexes.rows > 500)
      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production
      hours) //500 change leeway
         begin
            disable autostats
            log autostats disable
         end
      else
         begin
            stats ok
         end
   else
      if (sysindexes.rowmodctr >= 425) //75 change leeway
         begin
            disable autostats
            log autostats disable
         end
				

In un secondo momento Ŕ Impossibile pianificare un processo per eseguire le operazioni seguenti:
  • Eseguire UPDATE STATISTICS su tutte le tabelle per cui Ŕ stato necessario disattivarli durante il giorno.

    - e -
  • Riattivare AutoStat, perchÚ il contatore di modifica di ogni tabella verrÓ sono state reimpostate 0 quando Ŕ stato esegue UPDATE STATISTICS.

Controllare se UPDATE STATISTICS vengono eseguite da una tabella

La soluzione pi¨ ovvia per questa domanda, quando AutoStat Ŕ dimostrata Problematic, consiste nel disattivare generazione statistiche automatica, lasciando cosý gli amministratori di database disponibili gratuitamente per pianificare UPDATE STATISTICS orari meno intrusivo. ╚ possibile effettuare questa operazione utilizzando l'istruzione UPDATE STATISTICS o la routine evento sp_autostats memorizzati. La sintassi per l'istruzione UPDATE STATISTICS Ŕ:
   UPDATE STATISTICS <table>...with NORECOMPUTE
				

La sintassi per la procedura sp_autostats memorizzato Ŕ:
sp_autostats <table_name>, <stats_flag>, <index_name>
dove <stats_flag> Ŕ "on" o "off".

╚ inoltre possibile utilizzare sp_dboption per disattivare l'occorrenza automatica di UPDATE STATISTICS oppure CREATE STATISTICS a livello di singolo database:
sp_dboption <dbname>, 'auto update statistics' < su | disattivare >

- oppure -

sp_dboption <dbname>, 'auto create statistics', < su | disattivare >

Controllare il numero di processi simultanei di UPDATE STATISTICS

Attualmente, di disattivazione AutoStat per tabelle specifiche, non Ŕ possibile configurare il numero di istruzioni UPDATE STATISTICS automatiche siano in esecuzione contemporaneamente (DCR 51539 sono stati archiviati per questo). Il server, tuttavia, limitare il numero di processi simultanei di UPDATE STATISTICS per quattro per ogni processore.

Determinare quando sono in esecuzione Autostats

╚ possibile utilizzare il flag di traccia 205 per segnalare quando una stored procedure basate le statistiche viene viene ricompilata come risultato di AutoStat. Questo flag di traccia verrÓ scrivere il log degli errori i seguenti messaggi:
1998-10-15 11:10:51.98 spid9 emesso ricompilazione: nomeroutine: sp_helpindex
LineNo: 75 StmtNo: 29
Quando il flag di traccia 205 Ŕ attivato, il seguente messaggio anche parentesi quadra il messaggio AutoStat 8721 quando vengono aggiornate le statistiche. Il messaggio di apertura della parentesi quadra possibile distinguere dal valore RowModCnt, che sarÓ maggiore di 0. La parentesi di chiusura dopo UPDATE STATISTICS, avrÓ un RowModCnt valore pari a 0:
Modifica dello schema spid8 di 11:38:43.68 1998-10-15: tabella Dbid: ObjID 7:
RowModCnt 133575514: RowModLimit 60500: 60499
Per il messaggio "RowModCnt" Ŕ il numero totale di modifiche alla tabella. "RowModLimit" Ŕ la soglia che, se superato, comporta un'esecuzione dell'istruzione UPDATE STATISTICS per la tabella.

╚ inoltre possibile attivare il flag di traccia 8721, che verrÓ dump informazioni nel log degli errori quando AutoStat Ŕ stato eseguito. Di seguito Ŕ un esempio del tipo di messaggio che Ŕ possibile prevedere per visualizzare:
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED tabella: [autori]
23 Modifiche righe:: associazione 501: 500 Durata: 47ms UpdCount: 2
Per il messaggio "Modifiche" sono il numero totale di modifiche alla tabella. "Associato" Ŕ la soglia di modifica, "Durata" Ŕ la quantitÓ di tempo l'istruzione UPDATE STATISTICS Ŕ necessario per completare e "UpdCount" indica il numero di statistiche aggiornate.

╚ inoltre possibile utilizzare SQL Server Profiler per identificare quando sono in esecuzione le istruzioni UPDATE STATISTICS. Per effettuare questa operazione, procedere come segue:
  1. Menu Profiler , fare clic su Strumenti , quindi Opzioni .
  2. Nella scheda Generale , Vai a eventi e quindi selezionare All Event Classes .
  3. Definire una nuova traccia e nella casella eventi selezionare varie , selezionare sub-event l'Aggiornamento automatico statistiche .
Nota: Se sono l'aggiornamento statistiche AutoStat, un grande numero di messaggi pu˛ essere scritti nel log degli errori. Sperimentare con attenzione questi flag di traccia prima di utilizzarli in qualsiasi produzione o di un server critico in caso contrario.

Blocchi di schema

SQL Server utilizza due tipi di blocchi di schema, che vengono eseguite quando aggiorna le statistiche per una tabella:
   Sch-S: Schema Stability Lock
   ----------------------------
   This lock ensures that a schema element, such as a table or index, will
   not be dropped while any session holds a schema stability lock on the
   schema element.

   Sch-M-UPD-STATS: Schema Modification Lock
   -----------------------------------------
   This is a non-blocking lock that is used by the system to ensure that
   only one automatic UPDATE STATISTICS process is run against a table at
   any given point in time. The sp_lock stored procedure will report this
   lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M.
				
Ŕ possibile visualizzare tali blocchi eseguendo sp_lock o selezionando dalla tabella syslockinfo.

ProprietÓ

Identificativo articolo: 195565 - Ultima modifica: venerdý 2 novembre 2007 - Revisione: 7.6
Le informazioni in questo articolo si applicano a:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
Chiavi:á
kbmt kbinfo KB195565 KbMtit
Traduzione automatica articoli
Il presente articolo Ŕ stato tradotto tramite il software di traduzione automatica di Microsoft e non da una persona. Microsoft offre sia articoli tradotti da persone fisiche sia articoli tradotti automaticamente da un software, in modo da rendere disponibili tutti gli articoli presenti nella nostra Knowledge Base nella lingua madre dell?utente. Tuttavia, un articolo tradotto in modo automatico non Ŕ sempre perfetto. Potrebbe contenere errori di sintassi, di grammatica o di utilizzo dei vocaboli, pi¨ o meno allo stesso modo di come una persona straniera potrebbe commettere degli errori parlando una lingua che non Ŕ la sua. Microsoft non Ŕ responsabile di alcuna imprecisione, errore o danno cagionato da qualsiasi traduzione non corretta dei contenuti o dell?utilizzo degli stessi fatto dai propri clienti. Microsoft, inoltre, aggiorna frequentemente il software di traduzione automatica.
Clicca qui per visualizzare la versione originale in inglese dell?articolo: 195565
LE INFORMAZIONI CONTENUTE NELLA MICROSOFT KNOWLEDGE BASE SONO FORNITE SENZA GARANZIA DI ALCUN TIPO, IMPLICITA OD ESPLICITA, COMPRESA QUELLA RIGUARDO ALLA COMMERCIALIZZAZIONE E/O COMPATIBILITA' IN IMPIEGHI PARTICOLARI. L'UTENTE SI ASSUME L'INTERA RESPONSABILITA' PER L'UTILIZZO DI QUESTE INFORMAZIONI. IN NESSUN CASO MICROSOFT CORPORATION E I SUOI FORNITORI SI RENDONO RESPONSABILI PER DANNI DIRETTI, INDIRETTI O ACCIDENTALI CHE POSSANO PROVOCARE PERDITA DI DENARO O DI DATI, ANCHE SE MICROSOFT O I SUOI FORNITORI FOSSERO STATI AVVISATI. IL DOCUMENTO PUO' ESSERE COPIATO E DISTRIBUITO ALLE SEGUENTI CONDIZIONI: 1) IL TESTO DEVE ESSERE COPIATO INTEGRALMENTE E TUTTE LE PAGINE DEVONO ESSERE INCLUSE. 2) I PROGRAMMI SE PRESENTI, DEVONO ESSERE COPIATI SENZA MODIFICHE, 3) IL DOCUMENTO DEVE ESSERE DISTRIBUITO INTERAMENTE IN OGNI SUA PARTE. 4) IL DOCUMENTO NON PUO' ESSERE DISTRIBUITO A SCOPO DI LUCRO.

Invia suggerimenti

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com