journées sql server 2012 attentes et performances
DESCRIPTION
Slidedeck de notre présentation aux JSS 2012TRANSCRIPT
Edition 2012 – 10 et 11 décembre
Rejoignez la Communauté
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
MONITORING SQL SERVERAttentes & Performances
Edition 2012 – 10 et 11 décembre
Merci à nos SponsorsRencontrez les dans l’espace partenaires
Sponsors Platinum
Sponsors Gold
Sponsors Silver
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
SPEAKERS
Benjamin VESAN – [email protected]
David BAFFALEUF - [email protected]
http://www.capdata.fr
http://blog.capdata.fr
@capdata_blog / @dbaffaleuf
Edition 2012 – 10 et 11 décembre
AGENDA
1. Introduction.
2. Qu’est-ce qu’une attente.
3. Les différents postes d’attente.
4. Démos
5. Des outils pour mesurer.
6. Notion de baseline.
7. Démos
Edition 2012 – 10 et 11 décembre
INTRODUCTION
• Lorsque le problème est défini par un ‘sentiment’ de
l’utilisateur:• « C’est plus lent que la semaine dernière. »
• « Le problème vient de la base de données, c’est sûr. »
• « On n’a rien changé dans l’application ! »
• Pas d’indication ou de direction précise.
• Par où commencer ?
• Le matériel ? CPU, mémoire, disques ?
• Les requêtes ?
• L’applicatif ?
Edition 2012 – 10 et 11 décembre
CONSOMMATION DE RESSOURCES ?
• SQL Server utilise 90% de la mémoire disponible ?
• SQL Server utilise 80% de la CPU disponible ?
• SQL Server utilise 90% de temps d’occupation disque ?
C’EST NORMAL !
• SQL Server est conçu pour occuper le terrain (CPU, mémoire,
disques).
• Comment distinguer l’utilisation optimale de la saturation ?
LES ATTENTES !
Edition 2012 – 10 et 11 décembre
QU’EST-CE QU’UNE ATTENTE ?
Edition 2012 – 10 et 11 décembre
ATTENTES ET PERFORMANCES
• Le temps d'exécution d'une requête est la somme des
temps d'attente.
• La somme des attentes sur une même ressource permet
d'identifier une contention.
• Si le top des attentes indique une contention sur les
entrées/sorties, pas la peine de regarder les compteurs
CPU.
• Permet de ne pas perdre de temps à chercher le
problème où il n’est pas.
SYMPTÔME != PROBLEME
Edition 2012 – 10 et 11 décembre
QUELQUES RAPPELS
• SQLOS = planifie les tâches à exécuter, gère les files
d’attente, l’allocation mémoire, la surveillance (resource
monitor, deadlocks). Modèle coopératif (vs préemptif)
=> sys.dm_os_schedulers
• Tâche = unité de travail à exécuter (batch).
=> sys.dm_os_tasks
• Worker = objet supportant l’exécution d’une tâche.
=> sys.dm_os_workers
Edition 2012 – 10 et 11 décembre
EXECUTION D’UNE TACHE
Runnable
Queue(signal_wait
_time)
IO Waiter Timer Abort
Worker pool
SQLOS
Host
SELECT ...
task C
task B
task Y task X
task S
task T
task C
Yield
task A
signal
Lock ? OK !
Resource
Queues(resource_wait
_time)
Edition 2012 – 10 et 11 décembre
VU D’EN HAUT
oCas simple mais …
oParallélisme ?
oNUMA ? + + Lazy writer
Edition 2012 – 10 et 11 décembre
LES DIFFERENTS ETATS D’UNE TACHE
RUNNABLE
RUNNING
SUSPENDED
Edition 2012 – 10 et 11 décembre
ATTENTES SIGNAL / RESOURCESignal_wait _time + Resource_wait_time = Total Attentes
Total Attentes + CPU Time = Temps de réponse
• Si rapport Signal / (Signal + Resource) élevé, alors
contention CPU.
• Si rapport Resource / (Signal + Resource) élevé, alors
contention sur les ressources.
• Vérifier le type de ressource sur lequel porte la contention
avec sys.dm_os_wait_stats.
Contention
CPU !!
Contention
I/O, locks,
CXPACKET,
latches...
S/(S+R) R/(S+R)
Edition 2012 – 10 et 11 décembre
DÉJÀ VU ?
PAGEIOLATCH_EX
WRITELOG PAGELATCH_SH
CXPACKETRESOURCE_SEMAPHORE
IO_COMPLETION
LCK_M_Sch
Edition 2012 – 10 et 11 décembre
DIFFERENTS POSTES D’ATTENTE
• Verrous: LCK_%
• Page / nonpage latches: PAGELATCH_%, LATCH_%
• Mémoire: RESOURCE_SEMAPHORE, THREADPOOL…
• I/Os: PAGEIOLATCH_%, ASYNC_IO_COMPLETION,
IO_COMPLETION
• Journal de transactions: WRITELOG, LOGBUFFER…
• CPU / Parallélisme: SOS_SCHEDULER_YIELD, CXPACKET
• Postes divers: PREEMPTIVE_%, ASYNC_NETWORK_IO,
OLEDB, MSSEARCH…
• Fausses attentes: LAZYWRITER_SLEEP, WAITFOR …
Edition 2012 – 10 et 11 décembre
LCK_%
• Attente sur un verrou.
• Attention à la portée du verrou suivant le mode
d’isolation.
• Détection avec les Blocked Process Reports (SQL Trace /
Xevents)
EXECUTE SP_CONFIGURE 'Blocked Process Threshold',5;
GO
RECONFIGURE;
GO
Edition 2012 – 10 et 11 décembre
PAGELATCH_%, LATCH_%
• Mécanisme de protection de structures mémoire.
• LATCH != LOCK
• Type PAGE Latch: gestion de la concurrence d’accès
physique des pages de données / d’indexes dans le Buffer
Pool.
• Mais aussi problème de concurrence d’accès
PFS/GAM/SGAM dans tempdb (cf session VHP Christophe
/ Frédéric).
• Type NonPage Latch: protection des structures internes
de SQL Server (files d’attente, File Control Block,
metadonnées, etc…)
Edition 2012 – 10 et 11 décembre
PAGEIOLATCH_%
• Lorsqu’une tâche demande des pages qui ne sont pas
dans le Buffer Pool, une demande de lecture depuis le
disque est émise.
• La tâche ne bloque pas la CPU le temps que la lecture
se termine.
• Elle se met en attente sur PAGEIOLATCH.
• De nombreuses attentes sur PAGEIOLATCH indiquent une
contention au niveau du sous-système disque.
Edition 2012 – 10 et 11 décembre
RESOURCE_SEMAPHORE
• Quelques opérateurs physiques nécessitent une allocation mémoire (Hash Join, Hash Aggregate, Sort, Exchange).
• S’il n’y a plus de mémoire disponible pour effectuer ces actions, la tâche est mise en attente.
• Tuning de requêtes !
• Parfois mélange de requêtes à faible coût (OLTP) et requêtes à fort coût (DWH). Les requêtes à fort coût sont pénalisées car moins prioritaires sur l’acquisition de mémoire.
• Utilisation du pool de ressource par défaut de Resource Governor pour gérer les allocations / timeouts.
• DBCC MEMORYSTATUS / sys.dm_exec_query_memory_grants
Edition 2012 – 10 et 11 décembre
IO_COMPLETION
• Lorsque la mémoire nécessaire pour un tri n’est pas
disponible, le tri sera réalisé partiellement sur disque
(tempdb) en utilisant des I/Os synchrones ( …) et
un algorithme Merge Sort.
• (re) Création d’index
Edition 2012 – 10 et 11 décembre
WRITELOG
COMMIT = écriture physique
• Temps de réponse du disque pour écrire une transaction
trop élevé.
• Sous système disque pas assez performant.
• Séparation fichiers DATA / LOG.
Edition 2012 – 10 et 11 décembre
ASYNC_IO_COMPLETION
• Attentes sur des I/Os asynchrones hors pages de données.
• Exemple: création de fichiers de bases de données, zéro-
initialisation des fichiers (journaux de transactions
toujours + données potentiellement).
Edition 2012 – 10 et 11 décembre
CXPACKET
• Indique simplement l’utilisation de parallélisme.
• Si pourcentage très élevé et paramétrage DOP par défaut, alors voir pour réduire DOP (resource governor, use plan, hints ).
• Max DOP 8 ou max NUMA core per node cfhttp://support.microsoft. com/kb/2023536.
• Sinon peut couvrir une autre cause racine (IOs). Rechercher dans sys.dm_os_waiting_tasks la ligne qui n’attend pas sur CXPACKET.
FIN
ISHCXPACKET
Edition 2012 – 10 et 11 décembre
POSTES D’ATTENTE A IGNORER
• …WHERE wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_
SEMAPHORE', … et un peu plus à chaque release … :)
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
Démos
Edition 2012 – 10 et 11 décembre
DES OUTILS POUR MESURER
• DMO: sys.dm_os_wait_stats, sys.dm_os_waiting_tasks.
• Réinitialisables avec :
dbcc sqlperf(‘sys.dm_os_wait_stats’,clear)
• Xevents: trace system_health par défaut (ring buffer +
fichier depuis SQL Server 2012). Possibilité de tracer les
attentes d’une session en ‘Live’.
• Activity Monitor disponible avec SSMS.
• SQL Server Performance Dashboard
• Management Dataware House (MDW).
Disponibles en Standard et Enterprise Edition !
Edition 2012 – 10 et 11 décembre
SYS.DM_OS_WAIT_STATS
• wait_type: nom de l’évènement d’attente.
• waiting_tasks_count: nombre d’attentes sur cet
évènement depuis le dernier redémarrage.
• wait_time_ms: temps total d’attente sur cet évènement.
• max_wait_time_ms: temps maximum d’attente sur cet
évènement.
• signal_wait_time_ms: temps passé en runnable queue.
• A calculer en plus:• Avg wait (ms) = wait_time_ms / waiting_tasks_count
• Avg Signal time (ms) = signal_wait_time / waiting_tasks_count
• Avg Resource time (ms) = (wait_time_ms – signal_wait_time) / waiting_tasks_count
Edition 2012 – 10 et 11 décembre
SYS.DM_OS_WAITING_TASKS
• 1 ligne par tâche en attente (attente en cours).
• Joindre avec sys.dm_exec_sessions et is_user_process=1 pour n’avoir que les sessions utilisateur.
• session_id / exec_context_id: identifiant du worker en attente (même session_id si parallélisme).
• blocking_session_id / blocking_exec_context _id: identifiant du worker source du blocage (origine des attentes CXPACKET si parallélisme).
• resource_description: identifiant de la ressource (exchangeEventid, lock + associatedObjectid, dbid:fileid:pageid, etc…)
Edition 2012 – 10 et 11 décembre
EXTENDED EVENTS
• system_health: attentes de plus de 15 secondes sur un
latch, 30 secondes sur un lock, et plus de 5 secondes sur
une ressource externe (serveur lié, XP stored procedure,
unsafe CLR, COM, etc…).
• Depuis SQL Server 2012 persistée dans un fichier sur
disque.
• Possibilité de tracer les attentes pour une session ‘live’:
Edition 2012 – 10 et 11 décembre
ACTIVITY MONITOR
Edition 2012 – 10 et 11 décembre
PERFORMANCE DASHBOARD 2012
• http://www.microsoft.com/en-
us/download/details.aspx?id=29063
Edition 2012 – 10 et 11 décembre
MANAGEMENT DATAWAREHOUSE
Edition 2012 – 10 et 11 décembre
BASELINE ET ATTENTES 1/2
• Suivre l’évolution des postes d’attente dans le temps:
• Permet de reconnaître les signes de « bonne santé » de
l’application.
• Permet de détecter une anomalie (IOs ? Parallélisme ?
Mémoire ?) qui n’était pas perçue auparavant.
• Quoi mesurer:
• La répartition des postes d’attente:
oPar classe (IO, CPU, mémoire, verrous…)
oPar type (encore plus précis)
Edition 2012 – 10 et 11 décembre
BASELINE ET ATTENTES 2/2
• Comment mesurer:
• Informations contenues dans MDW, mais pas de
possibilité de comparaison native.
• Outils du marché:
oQuest Foglight Performance Analysis.
oPrecise for SQL Server.
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DémosEncore…
Edition 2012 – 10 et 11 décembre
REFERENCES
• Tom Davidson (MS – SQLCAT) Waits and Queues• http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/sql-server-2005-
waits-and-queues.aspx
• Bob Ward (MS – CSS) Wait Type Repository• http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-
repository.aspx
• Santeri Voutilainen (MS – SQL Server Dev Lead)• Practical Troubleshooting the Database Engine (K. HENDERSON, Addison
Wesley), Chapt. 1 Waiting and Blocking
• Paul Randal (SQLSkills - Regional Director) http://www.sqlskills.com/blogs/paul/post/wait-statistics-or-please-tell-me-
where-it-hurts.aspx
Edition 2012 – 10 et 11 décembre
Continuez l’expérience online
Rejoignez la Communauté