Journées SQL Server 2012 Attentes et Performances

Edition 2012 – 10 et 11 décembre

Rejoignez la Communauté


Sponsors Platinum

MONITORING SQL SERVERAttentes & Performances


Merci à nos SponsorsRencontrez les dans l’espace partenaires

Sponsors Platinum

Sponsors Gold

Sponsors Silver


Sponsors Platinum

SPEAKERS

Benjamin VESAN – [email protected]

David BAFFALEUF - [email protected]

http://www.capdata.fr

http://blog.capdata.fr

@capdata_blog / @dbaffaleuf

mailto:[email protected]

mailto:[email protected]

http://www.capdata.fr/

http://blog.capdata.fr/


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


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 ?


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 !


QU’EST-CE QU’UNE ATTENTE ?


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


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


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)


VU D’EN HAUT

oCas simple mais …

oParallélisme ?

oNUMA ? + + Lazy writer


LES DIFFERENTS ETATS D’UNE TACHE

RUNNABLE

RUNNING

SUSPENDED


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)


DÉJÀ VU ?

PAGEIOLATCH_EX

WRITELOG PAGELATCH_SH

CXPACKETRESOURCE_SEMAPHORE

IO_COMPLETION

LCK_M_Sch


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 …


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


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…)


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.


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


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


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.


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).


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

http://support.microsoft.com/kb/2023536


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 … :)


Sponsors Platinum

Démos


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 !


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


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…)


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’:


ACTIVITY MONITOR


PERFORMANCE DASHBOARD 2012

• http://www.microsoft.com/en-

us/download/details.aspx?id=29063

http://www.microsoft.com/en-us/download/details.aspx?id=29063


MANAGEMENT DATAWAREHOUSE


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)


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.


Sponsors Platinum

DémosEncore…


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

http://sqlcat.com/sqlcat/b/whitepapers/archive/2007/11/19/sql-server-2005-waits-and-queues.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://www.sqlskills.com/blogs/paul/post/wait-statistics-or-please-tell-me-where-it-hurts.aspx


Continuez l’expérience online

Rejoignez la Communauté

Technology

Journées SQL Server 2012 Attentes et Performances