Extensions de sp_monitor avec les tables MDA (Sybase 12.5.2)

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

Brefs rappels sur les tables de monitoring MDA

Installation des tables de monitoring MDA

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

• créer un serveur CIS loopback : (facultatif depuis la version 15.0)

  declare @servernetname varchar(30)
  select @servernetname=srvnetname
  from sysservers
  where srvname=@@servername
  exec sp_addserver loopback, NULL, @servernetname

• créer le rôle mon_role si ce dernier n’existe pas et implémenter le rôle mon_role dans le rôle sa_role :

  create role mon_role
  grant role mon_role to sa_role

• exécuter le script installmontables localisé dans le répertoire $SYBASE/$SYBASE_ASE/scripts :

  $SYBASE/$SYBASE_OCS/bin/isql -Usa -P<sapassword> -S<servername> -iinstallmontables

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 créées, nouvelles procédures qui se basent sur les tables de monitoring MDA :

• sp_monitor_getcfgval
• sp_monitor_getcfgnum
• sp_monitor_verify_setup
• sp_monitor_verify_cfgval
• sp_monitor_enable
• sp_monitor_disable
• sp_monitor_connection
• sp_monitor_event
• sp_monitor_procedure
• sp_monitor_statement

sp_monitor, nouvelle version 12.5.2

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 pour monitorer les connexions, les procédures stockées, les requêtes, etc.

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

Prérequis

• Les tables de monitoring MDA doivent être installés, dans le cas contraire l’erreur 19122 est levée :

  exec sp_monitor 'enable'
  go
  Msg 19122, Level 16, State 1:
  Server 'CGC_T1_ASE', Procedure 'sp_monitor', Line 60:
  MDA is not installed. Please run installmontables script with appropriate role mon_role!

• Le paramètre 'max SQL text monitored' doit être appliqué lors du monitoring impliquant des résultats sur les textes SQL (sp_monitor statement , sp_monitor procedure, etc.), dans le cas contraire l’erreur 19262 est levée. Le paramètre 'max SQL text monitored' est statique et impose encore avec la version 12.5.2 un redémarrage du serveur.

  exec sp_monitor 'enable'
  go
  Msg 19262, Level 16, State 1:
  Server 'CGC_T1_ASE', Procedure 'sp_monitor_verify_setup', Line 60:
  sp_monitor' 'enable' requires the parameter 'max SQL text monitored' to be set
  to at least '1024' bytes. Set the configuration parameter to '1024' bytes and
  restart Adaptive Server

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 :

exec sp_monitor enable
go
exec sp_monitor <options>
go
exec sp_monitor disable
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 :

exec sp_monitor statement
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.

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 :

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.

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.

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 exécutée, celle-ci prenant en charge de réappliquer l’ancienne valeur des 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

Cas pratiques

Connexions

Syntaxe :

exec sp_monitor connection, [ cpu |diskio | elapsed time ]

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

• monProcessSQLText
• monProcessActivity

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

• cpu : tri des connexions par temps cpu décroissant.
• diskio : tri des connexions sur les lectures physiques décroissantes.
• elapsed time : tri des connexions par temps CPU + temps d’attente décroissant.

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' affiche également les verrous acquis par chaque connexion (colonne LocksHeld).

exec sp_monitor "connection"
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

Procédures stockées

Syntaxe :

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

sp_monitor 'procedure' donne 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' s’appuient sur la table de monitoring monSysStatement.

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, alors que l’option detail donne les valeurs pour chaque exécution d’une procédure.

exec 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

Requêtes

Syntaxe :

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

• monProcessSQLText
• monProcessStatement

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

• cpu : tri des commandes SQL par temps cpu décroissant.
• diskio : tri des commandes SQL sur les lectures physiques décroissantes.
• elapsed time : tri des commandes SQL par temps CPU + temps d’attente décroissant.

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

exec sp_monitor "statement"
go

spid LoginName ElapsedTime CpuTime PhysicalReads SQLText
---- --------- ----------- ------- ------------- -------
26         sa        45063       0             0 select * from orders
209        sa        35210       0           125 select * from order_line

Evènements d’attente

Syntaxe :

exec sp_monitor event, [ spid ]

sp_monitor 'event' révèle 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 :

• monProcessWaits
• monWaitEventInfo

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

exec sp_monitor "event","14"
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