�ݺ�ߣ

DPC3010 - SQL Server 2012:
Performance Tuning
Methodology
Gianluca
Sartori
MCITP MCTS MCT

Performance Tuning Methodology
• Perché una metodologia?
• Come condurre un Health Check
• Raccolta
• Analisi
• Intervento
• The Performance Cycle
• Replay
• Capture
• Analyze
• Adjust

• SQL Server è un prodotto complesso
• Molte leve su cui agire
• Non c’è il pulsante «turbo» degli anni ‘90 
• Si rischia di perdersi
• Serve un metodo riproducibile
• Linee guida, ognuno troverà il metodo che preferisce
Perché una metodologia?

Raccolta Informazioni
• Windows
– Versione
– Dotazione HW
• CPU
• RAM
• Disco
• SQL Server
– Versione
– Parametri di configurazione
– Numero e dimensione dei database
– Distribuzione di data/log files sullo storage
Dati anagrafici del «paziente»

• Wait stats
• Performance counters
• Dynamic Management Views
• Query stats
Quali dati raccogliere?

• DMV  sys.dm_os_wait_stats
• Traccia il tempo speso dai processi in attesa di risorse
• Tempi di attesa cumulativi (ultimo riavvio istanza)
• Ottimo punto di partenza per il troubleshooting
– Consente di identificare i problemi dai sintomi
– Mostra quali aree richiedono attenzione
• Non tutte le classi di wait sono significative
Wait Statistics

Comuni classi di wait
Risorsa Classe Esempio
Locking LCK_*
LCK_M_S
LCK_M_X
Latching
LATCH_*
PAGELATCH_*
LATCH_EX
PAGELATCH_UP
I/O
PAGEIOLATCH_*
*_COMPLETION
WRITELOG
PAGEIOLATCH_SH
IO_COMPLETION
ASYNC_IO_COMPLETION
CPU
THREADPOOL
SOS_SCHEDULER_YIELD
Network ASYNC_NETWORK_IO
Parallelismo CXPACKET

DEMO
Wait Stats

• CPU
• Memoria
• Disco
• SQL – specific
• …
• Quali contatori?
– In SQL Server 2012 sono 355!
Performance Counters

DEMO
Performance Counters

• sys.dm_os_... OS information, memory, performance
• sys.dm_io_... Disk performance
• sys.dm_exec_... Sessions, requests, connections
• sys.dm_db… Database info
• sys.dm_tran… Transactions, locks
• sys.dm_... Audits, broker, CDC, CLR, FileStream,
FTS, HADR, Repl, Resource Governor,
Extended Events
• Non tutte le DMV riguardano le performance
DMVs

Quali DMV?
In SQL 2012 sono 141!
SELECT name
,type_desc
FROM sys.all_objects
WHERE name LIKE 'dm[_]%'
ORDER BY name

• Glenn Berry’s Diagnostic Information Queries
http://sqlserverperformance.wordpress.com/
• Brent Ozar’s sp_Blitz
http://www.brentozar.com/blitz/
• Davide Mauri’s SYS2 DMVs
http://sys2dmvs.codeplex.com/
Come orientarsi tra 355 performance counters e 141 DMV?

• DMV  sys.dm_exec_query_stats
• Informazioni su tutte le query presenti in plan cache
• Possibile ottenere le query più costose in termini di:
– IO
• Reads
• Writes
– CPU
– Execution count
• Correlare le wait stats con le query stats
Query Statistics

DEMO
Query Stats

• Performance Data Collector
– Popola il Management Datawarehouse
• PAL
– Analizza Performance Counters
– Fornisce un template performance monitor
• SQL Trace / Profiler
– Cattura l’attività dell’istanza
• SQLDiag / PSSDiag
– Raccoglie counters, DMV, Trace, logs…
Strumenti per la raccolta informazioni

Analisi
• Management Datawarehouse
– Contiene informazioni sugli indicatori di performance e l’attività
• PAL
– Produce un report con analisi soglie
• RML Utilities
– ReadTrace produce un database di analisi
– Reporter visualizza e confronta i dati
• SQLNexus
– Analizza i dati raccolti da SQLDiag
– Offre reportistica per l’analisi
Strumenti per l’analisi dei dati raccolti

Analisi
• Best Practices Analyzer
– Controlla la conformità delle Best Practices
• ClearTrace
– «Normalizza» una traccia
– Analizza il consumo di risorse per query
• CPU
• Reads
• Writes
Altri strumenti di analisi

Analisi
• Riduzione dell’utilizzo delle risorse
• Riduzione delle attese
Su quali punti intervenire?

Piano di Intervento
• Elenco dettagliato degli interventi
• Suddividere per area
– Configuration
– Recovery
– Performance
– Concurrency
– Security
– Management
– HA and Replication
• Dare una priorità
• Stimare i tempi di intervento
L’analisi produce un «Action Plan»

Piano di Intervento
• Gli interventi devono produrre dei miglioramenti
– Rispetto a cosa?
• Il miglioramento deve essere misurabile
• Non devo introdurre peggioramenti
Il piano deve avere un obiettivo
BASELINE

Baselining
• Non esiste una baseline uguale all’altra!
• Descrive le prestazioni del sistema in condizioni «normali»
– Servirà come termine di confronto
– Monitoring
– Troubleshooting
• Fotografia statica degli indicatori di performance
– Performance Counters
– DMVs
– WAIT_STATS
Che cos’è una Baseline?

Baselining
• Breve termine
– Servono i dati di dettaglio per il troubleshooting
– ES: valore dei contatori raccolto ogni minuto
retention = 1 mese
• Lungo termine
– Dati aggregati per andamento storico
– ES: valori aggregati (MIN-MAX-AVG) per ogni ora
retention = ∞
• Organizzare in un DWH
• Posizionare in un’istanza non di produzione!
Monitoring Baseline

Baselining
E’ sufficiente una sola Baseline?

Performance Cycle
• Quale strumento?
– SQL Trace vs. Profiler
• Utilizziamo una SQL Trace
– Quali eventi catturare?
– Quali colonne catturare?
• RML Utilities
– Trace definition script
• Backup database
– Prima effettuare il backup, poi avviare la traccia
– Prima avviare la traccia, poi effettuare il backup
• Filtrare la traccia
Capture in produzione

Performance Cycle
• L’ambiente di test deve essere un clone completo
– Restore User Databases
• Attenzione agli object_id!
– Logins
• Attenzione agli utenti orfani!
– Job
– Package SSIS / DTS
– Database Mail
• L’ambiente di test deve essere isolato dalla produzione!
Preparazione dell’ambiente di test

Testare le modifiche
• Profiler
• RML Utilities - Ostress
• Distributed Replay
– Novità di SQL Server 2012
– Può eseguire un workload da più client
– Stesso query rate della traccia originale
Workload Replay - quale strumento?

• E’ composto da tre elementi:
– Administration tool
– Distributed Replay Controller
– Distributed Replay Client
Distributed Replay

Workload Replay - quale strumento?
Profiler Ostress Distributed Replay
Multithreading SI SI SI
Debugging SI NO NO
Synchronization mode NO SI SI
Stress mode SI SI SI
Distributed mode NO NO SI
Input format Trace
Trace
RML
SQL
Trace

• Resettare i performance counters
– DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
– DBCC FREEPROCCACHE();
– DBCC FREESYSTEMCACHE('ALL');
– DBCC DROPCLEANBUFFERS();
• Effettuare uno snapshot di ogni database
• Avviare gli strumenti di raccolta prestazioni
– Performance Monitor Collection Set
– SQLTrace – RML Template
Raccolta della Baseline

• Revert snapshot
• Applicare le modifiche
• Avviare gli strumenti di raccolta
• Avviare il replay
• Analizzare i dati raccolti (ReadTrace, PAL)
• Confrontare i dati raccolti con la Baseline
Ripetere per ogni modifica significativa
Raccolta del benchmark

DEMO
Distributed Replay

Deploy in produzione
• Mantenere le modifiche sotto source control
• SQL Server Data Tools (SSDT)
– Database Projects
– Modello dichiarativo del database
– Database compare
– Change script
• Tenere anche i change script sotto source control
• Monitorare le prestazioni in produzione
– Monitoring Baseline
Deploy

Risorse
• PAL
http://pal.codeplex.com/
• RML Utilities
http://support.microsoft.com/kb/944837/en-us
• ClearTrace
http://www.scalesql.com/cleartrace/default.aspx
• SQLNexus
http://sqlnexus.codeplex.com/
Strumenti

Risorse
• Troubleshooting SQL Server
Jonathan Kehayias
Ted Krueger
http://www.simple-talk.com/books/sql-books/troubleshooting-
sql-server-a-guide-for-the-accidental-dba/
Free Ebook

Con il contributo di
Con il patrocinio di

�ݺ�ߣ

A performance tuning methodology

Recommended

More Related Content

Viewers also liked (18)

Similar to A performance tuning methodology (20)

More from Gianluca Sartori (8)

A performance tuning methodology