Utilisation de set statistics time on

Cette commande sert à étudier les ressources consommées en temps (temps passé et temps CPU) lors de l'exécution des requêtes.

Comment s'en sert-on ?

Dans l'analyseur de requêtes, saisir la commande :

    set statistics time on

    go

    select count(*) from Coureur

Cette commande retourne à l'issue de l'exécution d'une requête SQL :

Les premières lignes représentent le temps d'analyse de la requête (parse) et sont donc habituellement négligeables.

Le temps CPU présente son intérêt par sa stabilité, en effet il est relativement indépendant de ce qui se passe pour les autres process.
Le temps passé (écoulé) quand à lui offre une vision plus temps de réponse à l'utilisateur : il peut traduire une contention dans certaines ressources (des attentes d'E/S disque, des attentes de libération de verrous,…)

Exemple 1 :

    select count(*) from Coureur

    where nom like 'V%'

Le temps CPU n'est pas mesurable, la durée de l'exécution est de 57 milli secondes.
Vous aurez bien sûr compris que les toutes petites valeurs ne sont pas significatives.

Si on ré exécute la même requête, les résultats sont encore meilleurs :

On constate sur le deuxième lancement que le cache des données joue aussi sur le temps d'exécution : en effet, il est moins coûteux pour SQLServer d'utiliser des pages qui sont déjà dans le cache que d'en charger de nouvelles depuis les disques.

Pourquoi le temps d'exécution peut-il varier si fortement ?

Cela peut notamment venir des verrouillages comme le montre l'exemple ci-dessous :


    select count(*) from Coureur

    where nom like 'V%'

Cette exécution est très longue (19 secondes 542) et ne consomme pas de CPU.


Explication :
Des verrous exclusifs posés pendant la 1ère requête avaient bloqué la table ; il a fallu attendre que ces verrous soient libérés pour obtenir les données.

Voici la requête concurrente qui posait problème :

    begin tran

    update coureur set nom = nom where nom like 'V%'

A quoi tout-cela peut-il servir ?

On s'attachera à faire la part des choses entre une consommation CPU importante (qui peut être souhaitée par exemple pour les jointures par hachage) et un temps d'exécution important.
Bien sûr des temps importants (dépassant les dizaines de secondes) doivent poser question si les traitements ne manipulent pas des milliers de lignes.
Un symptôme sera également la variabilité du temps d'exécution qui traduira souvent un des problèmes suivants :
• Faiblesse du cache de données (mémoire du serveur)
• Attente de libération de verrous
• Trop grande sollicitation du cache des données

Comment faire des tests du temps d'analyse des requêtes:

Il s'agit de vider le cache des procédures et également le cache des données ; cela doit être fait sur une machine de test dont vous maitrisez bien les exécutions (l'idéal étant que vous soyez seuls).



    DBCC FREEPROCCACHE

    go

    DBCC dropCLEANBUFFERS

    go

    CHECKPOint

    Go

Utilisation de la commande set statistics io on

A quoi cela sert il ?

A étudier les ressources consommées en entrées/sorties (input/output) lors de l'exécution des requêtes.

Comment s'en sert-on ?

Dans l'analyseur de requêtes, saisir la commande


    set statistics io on

    go

    select count(*) from Coureur

    where nom like 'V%'

La fenêtre de sortie, après exécution, vous donnera les informations suivantes :

Explications :

La table COUREUR est accédée une seule fois (Nombre d'analyses =1)
Les 62 pages accédées sont obtenues dans le cache
Il n'y a pas de lecture anticipée, ni de lecture physique (TOUT est dans le cache)



Conclusion :

Il faut faire baisser :

• Le nombre de lectures logiques avant tout : n'oubliez pas que ce sont des pages de 8 koctets !
• Le nombre d'analyse si possible : dans les jointures cela traduit un LOOP join qui sera moins performant qu'un MERGE JOIN

Ne pas trop se focaliser sur le nombre de lectures physiques, sauf s'il est constamment supérieur à 0 : cela veut dire que vous demandez plus de données que possible par rapport à la taille de mémoire gérée par SQLServer.