R: & SQL Q Aumento delle prestazioni SQL

  • Articolo

Ci sono sempre diversi modi per spingere la busta di prestazioni di SQL Server, e spesso non costa denaro per farlo.

Paul S. Randal

La nuova normalità

D. Stiamo per iniziare un progetto di ridisegnare il nostro schema di database e siamo preoccupati per come lontano normalizzare le cose. Avete qualche consiglio?

**R.**Essere cauto nella Spinta normalizzazione troppo lontano. L'obiettivo abituale di un esercizio di normalizzazione è terza forma normale, o 3NF, dove tutti gli attributi non chiave aiutano a definire la chiave. C'è una frase utile che descrive questa regola: "Gli attributi definiscono la chiave, la chiave di tutta e di niente, ma la chiave, così mi aiuti Codd." (E.F. Codd originariamente definite 3NF nel 1971; Vedi questo link per ulteriori informazioni. La frase è basata sul giuramento uno Giura in un'Aula di tribunale.)

Il problema con spingendo per 3NF è che molte persone andare troppo lontano — ciò che io chiamo over-normalizing. Ciò può causare problemi di prestazioni enorme sotto carico quando le query devono unirsi a numerose tabelle a materializzarsi relazioni significative. Una successione di join è quindi l'unico modo per raggiungere le varie entità.

Ad esempio, abbiamo un cliente cui schema è stato scritto 10 anni fa da uno sviluppatore c# che non era particolarmente SQL Server più accorti. Ogni entità possibili vengono archiviati in una propria tabella e ha un ID. Tutti gli attributi di entità vengono memorizzati nelle proprie tabelle, con il proprio ID. Le relazioni con il quale entità appartengono ad altri soggetti, vengono memorizzate nelle proprie tabelle. La storia di come è cambiata ogni attributo per ogni entità viene archiviata in una propria tabella, con il proprio ID.

Così una tabella di particolare entità può avere riferimenti chiave esterna a molte altre tabelle. Questo è uno schema pesantemente dell'eccessiva normalizzazione dei cui fare quasi nulla richiede l'Unione di 10 o più tabelle.

Quel che è peggio è che non ci sono strategie di archiviazione. Ci sono molti anni di dati nel database con conseguente join che devono elaborare enormi quantità di dati. Le cose più devono essere fatto per risolvere il problema:

  • Rimuovere i vecchi dati per ridurre la dimensione dei dati e implementare una strategia di archiviazione automatizzata, mantenendo il dataset attivo piccole.
  • Si consideri utilizzando filtrati gli indici e i predicati di query per ridurre la quantità di operazioni in corso sui dati.
  • Si consideri de-normalizing parti dello schema per consentire alcune tabelle di crollare insieme per ridurre la complessità di aderire.

Ecco la regola d'oro da tenere a mente quando si progetta il nuovo schema: Pensare le operazioni di T-SQL per efficacemente i dati di query e cercare di ridurre al minimo i join eccessivi necessaria o inutilmente operanti su molto grandi set di dati. Si dovrebbe anche prototipo vostro disegno ed eseguire alcune query rappresentante presso un elevato carico di lavoro per vedere se ci sono eventuali colli di bottiglia del disegno evidente.

Una volta che hai messo uno schema in produzione diventa incredibilmente difficile e costoso per apportare modifiche. Questo significa che si può spendere un sacco di tempo di fine-tuning query e l'indicizzazione delle strategie per compensare la hit di prestazioni dello schema dell'eccessiva normalizzazione dei.

Prestazioni puzzle

D. La nostra tabella principale ha un indice cluster su una colonna di identità integer univoco. Le righe della tabella non sono mai aggiornate dopo che hai inseriti e ho mantenuto la dimensione riga ragionevolmente piccolo. Mi sto trovando che, con un più elevato numero di connessioni simultanee, le prestazioni delle query sta rallentando. Avete qualche idea quello che potrebbe essere il problema?

**R.**Voi avete progettato tuo indice cluster con le nostre linee guida best practice, dove la chiave di indice cluster deve essere univoco, statico, stretta e crescente. Questo funziona perfettamente fino ad arrivare in un carico di lavoro di fascia alta.

Per esempio, con diverse migliaia di connessioni simultanee (o forse addirittura centinaia) tutte inserimento nella tabella, non c'è intenzione di bloccare. Se guardate i blocchi mantenuti con la vista a gestione dinamica (DMV) sys.dm_tran_locks, non mostra nulla di male. Tutti i thread sono in possesso di blocchi di pagina IX (su una delle poche pagine) e serrature a X (su record su queste pagine). Le cose sembrano normali, ma c'è sicuramente qualche blocco in corso. Il blocco appena non coinvolga serrature. È possibile trovare ulteriori informazioni sui blocchi e il blocco di SQL Server in Libri Online.

Se si esamina l'os_waiting_tasks DMV, vedrete che la maggior parte dei thread sono in attesa con un tipo di attesa di PAGELATCH_EX su una delle poche pagine. Questo è perché un blocco non è sufficiente per essere in grado di modificare una copia in memoria di una pagina del file di dati. L'os_waiting_tasks è una delle DMV più utili, tra l'altro, perché dimostra che cosa tutti i "thread" in SQL Server sono in attesa di.

Che copia della pagina in memoria è in realtà una struttura di dati, per quanto SQL Server è preoccupata. Impossibile modificare una struttura di dati con appena un blocco relazionale. È necessario proteggere l'integrità della struttura dati da più thread di accedere e di cambiare e allo stesso tempo. Questa è una scienza informatica di base fondamentale. È possibile gestire l'accesso esclusivo a cambiare la struttura dati all'interno di SQL Server utilizzando un meccanismo di sincronizzazione leggero chiamato un chiavistello, che ho discusso qui in precedenza.

Come l'indice cluster su una colonna identity, inserti sarà un modello di sola aggiunta inserto con molte righe su ogni pagina di dati. Questa è una buona cosa. Tuttavia, esso significa che molti thread cercherà di inserimento di righe contemporaneamente sulla stessa pagina file dati. Le serrature richieste non causano blocco perché ciascun thread ha blocchi di pagina compatibile e blocchi di riga su singole righe. Tuttavia, i thread che tutti bisogno di acquisire un'esclusiva fermo sulla pagina. Questo non è possibile contemporaneamente. Mentre aumenta il carico di lavoro, questo può portare a problemi di prestazioni di blocco e query principali.

Ci sono una varietà di soluzioni, tra cui:

  • Utilizzando il partizionamento per diffondere il carico di lavoro di inserimento su diverse partizioni di una tabella (o anche numerose tabelle)
  • La modifica di una chiave cluster composito che sarà naturalmente sparsi gli inserti su diversi punti della tabella (a scapito di causando la frammentazione)

La linea di fondo, però, è che il blocco sintomo non sempre significa che il blocco è il problema.

Riflettendo le prestazioni

D. Stiamo avendo problemi di prestazioni con il nostro Server SQL e i nostri sviluppatori dire che abbiamo bisogno di un server più potente per eseguire il nostro carico di lavoro. Siamo classici "involontari" amministratori di database. Ci può dare qualche consiglio su cosa altro può aiutare, diverso dall'acquisto di hardware più?

**R.**Persone spesso non capiscono che la scarsa performance è un sintomo di un problema fondamentale con il modo in cui che si utilizza SQL Server. Il presupposto è si stanno raggiungendo i limiti di capacità del vostro hardware corrente ed è tempo di investire in un server con CPU più e più veloce. Questo può essere un costoso presupposto perché in un server con più CPU apre più finestre di temporizzazione e può portare al contesa addirittura superiore e meno velocità effettiva del carico di lavoro.

Abbiamo effettivamente visto accadere che un paio di volte a causa della scarsa pratiche di programmazione T-SQL.

Noi abbiamo ripetutamente dimostrato come sarebbe molto più economico e più vantaggioso per risolvere i problemi di prestazioni fondo invece di aggiungere hardware. Prima di prendere il tuffo con nuovo hardware, ci sono molte cose che si possono guardare per vedere se è un problema risolvibile. Ecco alcuni esempi:

  • Cerca nell'os_waiting_tasks DMV per vedere dove il tempo è stato speso in attesa di risorse. Se il tempo è speso in attesa per le applicazioni client elaborare i dati, rafforzando il hardware del server non farà la differenza. Si può vedere questo modello se il numero di righe in DMV output elenco ASYNC_NETWORK_IO come il tipo di attesa.
  • Esaminare le latenze di lettura/scrittura sottosistema I/O utilizzando il sys.dm_io_virtual_file_stats DMV per vedere spinti troppo duro il sottosistema I/O, se. Ciò potrebbe indicare la necessità di una strategia migliore indicizzazione. Tempdb potrebbe anche essere un collo di bottiglia, che potrebbe indicare un uso eccessivo di temp tables.
  • Guarda il manca indice DMV per vedere se gli indici ad alto impatto sono mancanti.
  • Guardate db_index_usage_stats per vedere se stai mantenendo — ma non usando — una tonnellata di indici.

Semplici modifiche spesso possono avere un effetto significativo sulle prestazioni di SQL Server. A volte il volume dei dati semplicemente è cresciuta e avete bisogno di più memoria del server per togliere la pressione e il pool di buffer. A volte il sottosistema I/O davvero bisogno una Spinta. Non è molto spesso, però, che è necessario acquistare un server più grande, più veloce.

Linee parallele

D. Abbiamo alcune query che causano problemi quando corrono in parallelo e vorremmo che limitare a thread singolo. Come possiamo farlo senza fare tutto thread singolo impostando il server, l'opzione MAXDOP su uno?

**R.**Come sfondo, MAXDOP significa "massimo grado di parallelismo". Che è fondamentalmente quanti thread concorrenti parallelo una query può utilizzare durante l'esecuzione.

È bene che non sei "ginocchio-jerking" e tutto ciò che l'impostazione su MAXDOP uno, come parallelismo è uno dei migliori caratteristiche delle prestazioni di SQL Server. Molte persone fare quello, basata soprattutto su poveri consigli, affermando che la prevalenza di attese CXPACKET (dal fare analisi statistiche di attesa) deve essere fissata smettendo di parallelismo.

Uno dei problemi con l'utilizzo dell'opzione sp_configure per disabilitare il parallelismo (a prescindere dal fatto che esso colpisce tutto sul server) è che chiunque, con qualsiasi livello di privilegio possibile ignorare quell'impostazione utilizzando un hint per la query MAXDOP nelle loro query. È possibile specificare sempre MAXDOP un hint per la query su tutte le query che si desidera limitare. Che può essere poco pratico, però, con centinaia o migliaia di query coinvolte.

Due metodi sono molto più efficaci a limitare il parallelismo:

  • Aumentare il livello di server "cost threshold for parallelism." Questo è un numero arbitrario generato durante la compilazione delle query. Esso viene utilizzato per decidere se generare un piano di query che possono eseguire in parallelo o no. Aumentando questo numero, è possibile impedire efficacemente alcune query che non dovrebbero essere eseguite in parallelo dal farlo. Lei non può ignorare questa impostazione utilizzando un hint per la query, neanche. Potete leggere di più su questa impostazione e come cambiarlo in un post sul blog di Jonathan Kehayias qui.
  • È inoltre possibile utilizzare la funzionalità di Resource Governor (in Enterprise Edition solo). Ciò consente di mettere le query in "secchi" (chiamati gruppi del carico di lavoro) e quindi assegnare un MAXDOP a ciascun segmento. È possibile ignorare l'opzione di MAXDOP sp_configure, ma non il Resource Governor. È possibile utilizzare qualsiasi filtro si vogliono decidere quale query andare in cui secchi. Si possono avere più secchi con impostazioni diverse MAX_DOP, inteso come bene. Sempre più persone utilizzano questo metodo per acquisirne il controllo fina parallelismo.

Paul S. Randal

Paul S. Randal è l'amministratore delegato di SQLskills.com, Microsoft regional director e MVP per SQL Server. Ha lavorato del team di Microsoft SQL Server Storage Engine dal 1999 al 2007. Scrisse DBCC CHECKDB/riparazione per SQL Server 2005 ed è stato responsabile per il motore di archiviazione Core durante lo sviluppo di SQL Server 2008. Randal è un esperto di disaster recovery, alta disponibilità e manutenzione del database ed è un presentatore regolarmente a conferenze in tutto il mondo. Ha blog a sqlskills.com /Blogs/Paul, e lo potete trovare su Twitter a Twitter.com /PaulRandal..

Contenuto correlato