Compteurs de performances SQL SERVER

 

Dans le Cadre d’une mission d’optimisation d’une base sql server 2012 pour un client

J’ai mis en place des compteurs de performance avec perfmon outils  windows  afin de récolter les données sur la santé de l’instance et aussi le dashboard,outil également fourni par Microsoft cela permet de récolter les données en temps réel sur l’instance.

Il est important de récolter ces données sur une longue période, cela permettra d’avoir plus d’informations.

Je conseille d’automatiser la collecte sur une semaine au moins :

Ci joint mes compteurs ainsi qu’un peu d’explication :

Les compteurs de performances :

CPU :
– User Mode and Priviled Mode

* Processor (_Total) \% Processor Time
* Processor (_Total) \% Privileged Time
Il correspond au temps qu’ sql met pour effectuer des opérations system (io)
Plus sql passe du temps en mode privilégié moins le cpu sera performant

* Process(sqlsrvr) \% Processor Time

* Process(sqlsrvr) \% Privileged Time

– Eventuellement

* System\Context Switches /sec

* System\Processor Queue Length

– VM Processor(_Total)

* Effective VM Speed in Mhz

* Host processor Speed in Mhz
SQL SERVER : DATABASES
=======================
* Percent log Used:
Il correspond au % d’utilisation du journal de transaction à >60% pb sur la taille
du fichier de journal
Il peut également signifier un problème de sauvegarde

Lorsqu’on est en mode de récupération simple, le journal est vidé à partir de 70% de remplissage: Il s’agit d’un problème de transaction non commited

Si on est en mode complet,cela signifie que la fréquence de sauvegarde des journaux  de transaction n’est pas suffisante

* Log Flush Wait Time :

L’application doit attendre avant d’écrire dans le journal
* Log Flush Waits/Sec
L’application doit attendre avant d’écrire dans le journal

* Log Growths

Nombre de fois qu’on a agrandi le journal de transaction

* Log Shrinks

Nombre de fois qu’on a réduit le journal de transaction
il y a forcement une attente avant l’écriture,la valeur doit être proche de 0

 

SQL SERVER :SQL Statistics

* Batch Requests /Sec
Mesure l’activité globale du service SQL => une carte Gigabit supporte en 30000 !

* SQL Compilations /Sec

Si le nombre est important il y a juste des performances il faut alors utiliser des procédures stockées

* SQL Recompilation /Sec

>10% il y a un problème

SQL Server:Databases \Transactions per second
SQL SERVER : GENERAL Statistics\User connetions
Il faut avoir une connexion stable
SQL SERVER :Transactions

* free space in TempDb(kb)

* version store Size (kb)

SQL SERVER : LOCKS

* Lock Requests/sec
Nombre de demande de verrou à la seconde
ACCES aux Données : SQL SERVER :ACCES Methods
======================
* full Scans/sec
Nombre de parcours complet de table par seconde, il ne faut pas dépasser  1 full scan pour 1000 utilisation d’index

* Index searches/sec :

Nombre de recherche d’index par seconde, si ce nombreux est bas cela signifie qu’il y a un manque d’index

* Forwarded Records/sec :
Nombre de pointeur sur des pages

*(page splits/sec)

Mesure le nombre de pages partagées (dû à un index plein)=> doit être inférieur à 100
* Table lock Escalation/sec

Verrouiller sur la table

* Workfiles Create/sec
Donne l’information sur l’utilisation de la base tempdb

LOCK et LATCHES :
============================

SQL SERVER : Locks

* lock Requests/sec
* Lock wait Time(ms)
* LOCK wait/sec
* LOCK Timeouts/sec
* Number of Deadlocks/sec

SQL Server : LATCHES

il faut avoir des valeurs proches de 0
* Latch wait/sec
* Avg Lacth Wait Time(ms)
* Total Latch Wait Time(ms)
Buffer Manager :

sql server : memory
* memory grants pending : nombre d’attente pour recevoir de la mémoire

Page Life Expectancy
nombre de fois qu’on renouvelle les pages en memoire
seuil : maxdebufferpool(MB)/1024/4)*300) 4800sec
il faut la maintenir a une valeur haute le plus possible

* Checkpoint Page/sec
* Free Pages
les pages libres en mémoires

* Free List Stall/sec
* Lazy Writes/sec:
mesure le nombre de pages « dirty » écrites sur le disque => idéalement, la valeur doit être le plus proche de zéro avec un maximum de 20/25

* Page Reads/sec
* Page Writes/sec

Réseau :

Longueur de la file d’attente de sortie (Ouput Queue Length) => mesure la longueur d’une file en paquet. Une valeur supérieure à 2 indique une saturationotal des octets/s (Bytes Total/sec) => indique de débit (voici la formule de calcul: paquets/sec * 1500 * 8 / 1 000 000)

 

*Wortables Create/sec
table spools,index spools
VM Memory

* memory active in MB
* memory ballooned in MB
on fait croire qu’on a de la mémoire

DISQUE :

Logical DISK

* AVg Disk sec/read
* AVg DISK sec/write < 5ms dans le lantence
*% ldle Time:
mesure l’activité disque

* Avg disk bytes/read : nombre de blocks en lecture
* Avg disk bytes/write :nombre de blocks en ecriture

< .0.005 soit 5 ms = excellent

5 à 10 ms = bon

10 à 15 ms = acceptable

> 15 ms = problème

Logs: 1 à 5 ms

Base OLTP: 5 à 20 ms – 10 recommandé

Base RDW: 25 à 30 ms – 10 recommandé

——————————————————–
——————————————————–

Les DMVs

CLassification
category

sys.dm_exec_% —>>> execution and connections

sys.dm_os_% —>>> SQL os related information

sys.dm_tran_% —>> Transaction Management

sys.dm_io_% —>> I/O related information

sys.dm_db_% —>> Database scoped information

MEMOIRE et CPU

sys.dm_os_scheduler

sys.dm_os_buffer_descriptors

sys.dm_os_performance_counters

sys.dm_os_waiting_tasks voir en temps réel les attentes

 

Grâce aux compteurs de performances et le dashboard on peut déjà avoir des informations très intéressantes, cela va nous donner une orientation sur les  quatre composants :

– CPU

– Mémoire

– Disque Dur

– Réseaux

Avant de s’attaquer à notre base sql server il faut d’abord s’assurer  que celui-ci évolue dans un environnement stable , il ne sert à rien de se mettre à optimiser les requêtes ou les procédures stockées si  l’environnement est déjà mal en point.

Je suis à l’écoute des experts en optimisation, pour ma part, ce sont les compteurs que j’utilise dans le cadre d’une étude de performance.