Extensions de sp_monitor avec les tables MDA (Sybase 12.5.2)


1- Introduction

La procédure sp_monitor offre de nouvelles fonctionnalités avec la version 12.5.2 de Sybase ASE grâce aux tables de monitoring MDA (nouveauté 12.5.0.3, l'installation des tables de monitoring MDA a déjà fait l'objet d'un article technique).

Les nouvelles procédures stockées sp_monitor% avec Sybase 12.5.2 offrent un confort accru sur le monitoring des exécutions des requêtes, des procédures stockées et des connexions lorsqu'une charge de travail importante se produit sur un serveur.

Le présent article balaye ces nouvelles fonctionnalités de monitoring avec sp_monitor, nouvelles fonctionnalités qui ne sont disponibles que si les tables MDA sont installées.

2-Brefs rappels sur les tables de monitoring MDA

2-1- Installation des tables de monitoring MDA

Rapidement, pour réaliser l'installation des tables de monitoring MDA, trois étapes sont nécessaires

2-2- Nouveautés sur l'installation des tables de monitoring MDA avec Sybase 12.5.2

Lors de l'exécution du script installmontables, des nouvelles procédures sp_monitor% sont implémentées, nouvelles procédures qui se basent sur les tables de monitoring MDA :

3- sp_monitor, nouvelle version 12.5.2

3-1- Nouvelle syntaxe sp_monitor

Dans les versions précédentes d'ASE, sp_monitor ne disposait pas de paramètres. Avec la version 12.5.2, des paramètres nouveaux font leur apparition pour sp_monitor et permettent de monitorer les connexions, les procédures stockées, les requêtes etc...

sp_monitor [ connection, [ cpu | diskio | elapsed time ] ] 
sp_monitor [ event, [ spid ] ]
sp_monitor [ procedure, [ dbname, [ procname, [, summary | detail ] ] ] ] 
sp_monitor [ enable ]
sp_monitor [ disable ]
sp_monitor [ statement, [ cpu | diskio | elapsed time ] ]
sp_monitor [ help ],
sp_monitor [ connection | statement | procedure | event ] ]

3-2- Prérequis

3-3- Usage

La procédure sp_monitor doit être exécutée ponctuellement lorsqu'une charge importante se produit sur un serveur sybase.

Pour monitorer l'exécution des procédures stockées, des requêtes ou des connexions, typiquement la séquence qui suit sera déclenchée :

1 > sp_monitor enable
2 > go
3 > sp_monitor <options>
4 > go
5 > sp_monitor disable
6 > go

Dans le cas où sp_monitor est sollicitée avec des paramètres sans au préalable appeler la commande sp_monitor enable, l'erreur 19261 est levée :

1> sp_monitor statement
2> go
Msg 19261, Level 16, State 1:
Server 'CGC_B1_ASE', Procedure 'sp_monitor_verify_cfgval', Line 105:
Executing option 'sp_monitor' 'statement' requires the 'sp_monitor' 'enable'
option to be executed first.

Il est fortement recommandé d'appeler la commande sp_monitor disable une fois le monitoring terminé.

Par ailleurs, en fonction de la charge sur le serveur, le résultat de la commande sp_monitor peut prendre du temps.

3-4- Principe de fonctionnement de sp_monitor <options> au sein du moteur ASE

La collecte des statistiques dans les tables de monitoring ne peut être effective que si certains paramètres de configuration sont correctement positionnés (cf documentation technique sur l'installation des tables de monitoring).

Le tableau ci-dessous regroupe les paramètres de configuration requis pour les options connection | statement | procedure | event de la procédure stockée sp_monitor, paramètres de configuration qui sont tous dynamiques :

Paramètres de configuration indispensables pour sp_monitor Valeur
enable monitoring 1
wait event timing 1
process wait events 1
per object statistics active 1
sql batch capture 1
statement pipe active 1
statement pipe max messages 1-2147483647

Les paramètres de configuration listés dans le tableau ci-dessus ne sont pas forcément déjà appliqués au niveau du serveur : c'est pourquoi la commande sp_monitor 'enable' se charge d'appliquer dynamiquement les valeurs de ces paramètres de configuration. La procédure sp_monitor 'disable' a la responsabilité de réappliquer les anciennes valeurs de ces paramètres de configuration.

3-4-1- Cinématique de la commande sp_monitor 'enable'

La commande sp_monitor 'enable', une fois les contrôles effectués (présence des tables MDA, du rôle mon_role, positionnement correct du paramètre 'max SQL text monitored'), ne fait que provoquer le déclenchement de la procédure stockée sp_monitor_enable.

Durant l'exécution de la procédure stockée sp_monitor_enable, une table multisession mon_config est créée dans la base tempdb par défaut. Dans cette dernière table sont stockés tous les paramètres de configuration (hormis le paramètre 'statement pipe max messages') qui ne sont pas couramment positionnés à la valeur 1 pour l'utilisation des tables de monitoring :

create table tempdb.dbo.mon_config(confignum int, configval int)

insert into tempdb.dbo.mon_config
       select config, value from master.dbo.sysconfigures
              where name in ('enable monitoring', 'SQL batch capture',
               'statement statistics active', 'per object statistics active',
               'statement pipe active', 'wait event timing','process wait events')
              and value = 0

La valeur 1 est alors appliquée pour chacun des paramètres de configuration listés dans la table mon_config pour rendre effectif le monitoring dans les tables MDA :

select @val = config_admin(23, confignum, 1, 0, NULL, NULL)
                           from tempdb.dbo.mon_config

L'étape ultime consiste à vérifier la valeur pour le paramètre de configuration 'statement pipe max messages' et d'appliquer la valeur par défaut 100000 si ce dernier vaut initialement 0. Si la procédure stockée sp_monitor_enable est contrainte de modifier le paramètre de configuration 'statement pipe max messages', cette information est également stockée dans la table mon_config.

3-4-2- Cinématique de la commande sp_monitor 'disable'

Lors de la fin du monitoring déclenchée avec la commande sp_monitor 'disable', la procédure stockée sp_monitor_disable est déclenchée, celle-ci prenant en charge de réappliquer l'ancienne valeur 0 aux paramètres de configuration listés dans la table tempdb..mon_config

select @val = config_admin(23, confignum, 0, 0, NULL, NULL)
       from tempdb.dbo.mon_config

4- Cas pratiques

4-1- Connexions

Syntaxe :

sp_monitor connection, [ cpu |diskio | elapsed time ]

sp_monitor 'connection' fournit les informations sur chaque connexion et se base sur les tables de monitoring :

Le paramètre [ cpu | diskio | elapsed time ] trie la sortie de la procédure sp_monitor 'connection' :

Par défaut, la sortie sp_monitor 'connection' est triée en mode décroissant sur la colonne 'Elapsed Time' (temps CPU + temps d'attente).

sp_monitor 'connection' fournit également sur les verrous acquis par chaque connexion (colonne LocksHeld).

1> sp_monitor connection
2> go
spid LoginName ElapsedTime CPU_Time Physical_Reads LocksHeld SQLText
---- --------- ----------- -------- -------------- --------- ----
26         sa    161359600     6100             62         2 select * from orders
209        sa     81362500      200            188         2 select * from order_line

Les données retournées par la commande sp_monitor 'connection' sont cumulatives pour chaque connexion.

4-2- Procédures stockées

Syntaxe :

sp_monitor procedure, [ dbname, [procname, [, summary | detail ] ] ] ]

sp_monitor 'procedure' fournit les informations sur les procédures stockées (temps CPU, lectures physiques, lectures logiques, nombre d'exécutions etc...). Les informations retournées par sp_monitor 'procedure' reposent sur la table de monitoring :

Comme le montre la syntaxe, il est possible de filtrer sur une base de données (dbname), sur une procédure stockée en particulier (procname).

L'option summary donne les valeurs moyennes sur les exécutions d'une procédure, l'option detail donne les valeurs pour chaque exécution d'une procédure.

sp_monitor "procedure"
go
Average Procedure Statistics

ProcName                   DBName               AvgElapsedTime
AvgCPUTime                 AvgWaitTime          AvgPhysicalReads     AvgLogicalReads
AvgPacketsSent             NumExecs

sp_help                    sybsystemprocs       40
6                          33                   7                    863
6                          1
rowsize                    tpcc                 6
6                          0                    0                    223
0                          1
sp_monitor_getcfgnum       sybsystemprocs       6
3                          0                    0                    18
0                          1
sp_monitor_verify_cfgval   sybsystemprocs       6
0                          0                    0                    0
1                          1

4-3- Requêtes

Syntaxe :

sp_monitor statement , [ cpu |diskio | elapsed time ]

sp_monitor 'statement' fournit les informations sur chaque commande SQL et se base sur les tables de monitoring :

Le paramètre [ cpu | diskio | elapsed time ] trie la sortie de la procédure sp_monitor 'statement' :

Par défaut, la sortie sp_monitor 'statement' est triée en mode décroissant sur la colonne 'Elapsed Time' (temps CPU + temps d'attente).

1> sp_monitor statement
2> go
spid LoginName ElapsedTime CpuTime PhysicalReads SQLText
---- --------- ----------- ------- ------------- -------
26         sa        45063       0             0 select * from orders
209        sa        35210       0           125 select * from order_line

4-4- Evènements d'attente

Syntaxe :

sp_monitor event, [ spid ]

sp_monitor 'event' affiche les informations sur les évènements d'attente et le temps d'attente. Les données sont triées par temps d'attente décroissant (temps d'attente en millisecondes) et reposent sur les tables de monitoring :

Le paramètre optionnel spid permet de filtrer sur un spid en particulier, ce dernier devant être spécifié entre quotes.

1> sp_monitor "event","14"
2> go
WaitTime Description
-------- -----------------------------------
9000     waiting for CTLIB event to complete
600      waiting for disk write to complete
200      waiting for disk write to complete
100      waiting on run queue after yield
100      wait for buffer write to complete

Annexe

Historique

Version Date Commentaires
1.0 09/2004 Version initiale

Liens

Sybase Adaptive Server Enterprise 12.5.2 Books OnLine - New Functionality in Adaptive Server 12.5.2, sp_monitor