Sybase ASE : procédures stockées de monitoring exploitant les tables MDA

Logo

Introduction

Cette note présente quelques procédures stockées de surveillance d’instances ASE et qui exploitent les tables MDA.

Les tables MDA depuis ASE 12.5.0.3 (2005) permettent d’accéder simplement aux compteurs systèmes internes de l’instance ASE, compteurs jusqu’à présent (et toujours) disponibles avec les outils comme sp_sysmon ou monitor/historical server.

L’accès à ces informations sous forme de table permet un audit temps réel et simplifié. Les tables MDA remplaceront d’ailleurs définitivement à terme Monitor Server et Historical Server, outils dont la fin de support est annoncée pour le 31/12/2012.

Les tables MDA sont automatiquement installées à partir des versions 15.0 ESD#2. Pour les versions 12.5.x, l’installation des tables MDA est décrite sur SQLPAC : SQLPAC - Sybase 12.5.0.3 -Tables dynamiques MDA de monitoring.

Avec les nouvelles versions ou EBF de Sybase Adaptive Server Enterprise, les tables MDA évoluent et s’enrichissent. Rob Verschoor propose sur son site une page qui récapitule les nouvelles colonnes et nouvelles tables MDA avec les versions : Sypron (Rob Verschoor) - Changes and Enhancements to MDA tables since ASE 12.5.0.3

sp_dba_whodoes : qui travaille ?

Usage

sp_dba_whodoes liste les processus actuellement actifs sur l’instance, en complément des procédures stockées sp__who d’Ed Barlow (EdBarlow.com, extended stored procedures library)

Par processus, l’activité technique (CPU/Mémoire/Lectures/Lignes) est extraite mais aussi la date de connexion du processus ainsi que la date de la dernière opération :

/**
Usage : sp_dba_whodoes [SPID] ,
                       [OUTPUTTYPE=0|1|10|11] ,
                       [SHOWUSAGE=0|1]
                       
OUTPUTTYPE : 0 = summary , 1 = detail 
             10 = active proc summary , 11 = active proc detail

SHOWUSAGE  : 0 = hide usage , 1 = show usage
*/

Les tables utilisées, jointes avec la colonne SPID, sont les suivantes:

  • monSysStatement
  • sysprocesses

Remarque : l’historisation est gérée dans tempdb, le contenu de la table est régulièrement purgée au delà d’une certaine limite. La persistence de cette table dans tempdb est gérée en créant la structure dans la base système model.

OUTPUTTYPE=0 : vue compacte des processus actifs

execute sp_dba_whodoes
          -- Usage   : sp_dba_whodoes [SPID] , [OUTPUTTYPE=0|1|10|11] , [SHOWUSAGE=0|1] 
-- Running : sp_dba_whodoes NULL , 10 , 1
 
 spid Cnx      Login_DB_Prog                                         Cpu   R_W    Rws   Err DtEnd   
 ---- -------- ----------------------------------------------------- ----- ------ ----- --- --------
   92 6  05:45 smartco_maint/smartco/RepServer                         421 3/0     2613   0 14:17:01
   52 8  12:00 sa/master/                                            10586 3680/0   158   0 14:16:54
  164 6  07:10 ws_reportread1/sma_report/DaliServices.Winservice.exe    81 1/0      432   0 14:16:29
  254 6  15:25 ws_reportread1/sma_report/DaliServices.Winservice.exe  1824 415/0  28984   0 14:16:29
  203 6  07:10 ws_reportread1/sma_report/DaliServices.Winservice.exe    78 0/0      420   0 14:16:29
  285 6  15:25 ws_reportread1/sma_report/DaliServices.Winservice.exe   602 123/0   7630   0 14:16:29
   84 6  15:25 ws_reportread1/sma_report/DaliServices.Winservice.exe   150 0/0      830   0 14:16:29
   34 6  06:21 ws_reportread1/sma_report/DaliServices.Winservice.exe   941 113/0   8839   0 14:16:29

Le premier paramètre offre la fonctionnalité de suivre l’évolution d’un process particulier en passant son identifiant de connexion (spid).

execute sp_dba_whodoes 92,10,0;
           spid Cnx      Login_DB_Prog                   Cpu R_W Rws  Err DtEnd   
 ---- -------- ------------------------------- --- --- ---- --- --------
   92 6  05:45 smartco_maint/smartco/RepServer 535 3/0 3944   0 14:31:56

OUTPUTTYPE=1 : vue détaillée des processus actifs

execute sp_dba_whodoes null,11,0
           spid Cnx      Login          DB         Program                     Cpu   Wait MemKb  ReadL ReadP PgMod Rws   Err DtStart  DtEnd   
 ---- -------- -------------- ---------- --------------------------- ----- ---- ------ ----- ----- ----- ----- --- -------- --------
   52 8  12:00 sa             master     NULL                        17136    0 107820  6714     0     0   510   0 13:56:57 14:34:33
   92 6  05:45 smartco_maint  smartco    RepServer                     541   16 542270     3     0     0  3960   0 14:11:31 14:34:04
  285 6  15:25 ws_reportread1 sma_report DaliServices.Winservice.exe   827  200  17462   146     0     0  8304   0 13:59:27 14:33:21
   84 6  15:25 ws_reportread1 sma_report DaliServices.Winservice.exe   345    0  15680    13     0     0  1572   0 14:14:08 14:33:21
   34 6  06:21 ws_reportread1 sma_report DaliServices.Winservice.exe  1199 2900  34324   137     0     0  9583   0 13:10:02 14:33:21
  164 6  07:10 ws_reportread1 sma_report DaliServices.Winservice.exe   246    0  10894    10     0     0  1092   0 14:15:43 14:33:21
  254 6  15:25 ws_reportread1 sma_report DaliServices.Winservice.exe  2070 1266  18622   440     0     0 29649   0 13:33:37 14:33:21
  203 6  07:10 ws_reportread1 sma_report DaliServices.Winservice.exe   240    0  10706     9     0     0  1069   0 14:15:43 14:33:20
  225 8  03:07 ws_reportread1 sma_report w3wp.exe                       72    0   3202     0     0     0   632   0 13:18:57 14:30:22
  245 8  03:03 ws_reportread1 sma_report w3wp.exe                       54    0   1392     6     0     0   139   0 14:30:03 14:30:2

Le mode détaillé est plus complet et la présentation y est retraitée.

Par rapport à la vue compacte par processus, les informations supplémentaires sont : le nombre de lectures physiques et logiques, le dernier code erreur, le nombre de lignes, les dates de connexion, de début et de fin de traitement.

sp_dba_whatdoes : que fait un processus ?

Usage

sp_dba_whatdoes donne les dernières opérations réalisées pour un processus donné. Si aucun SPID n’est fourni en argument, c’est la procédure sp_dba_whodoes qui liste les processus actifs qui est appelée.

Par processus, outre l’activité technique (CPU/Mémoire/Lectures/Rows), le code SQL exécuté est affiché. Les résultats sont historisés à chaque exécution de sp_dba_whatdoes, permettant ainsi d’extraire une séquence d’opérations passées.

/**
# @(#) Usage   : sp_dba_whatdoes [SPID] , [OUTPUTTYPE=0|1] , [ROWS] , [SHOWUSAGE=0|1]
# @(#)           SPID : Process spid
# @(#)           OUTPUTTYPE : 0 = Summary / 1 = SQL text only
# @(#)           ROWS : limits the number of rows returned
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
*/

Les tables utilisées, jointes avec la colonne SPID, sont les suivantes :

  • monSysSQLText
  • monSysStatement

Le nombre de lignes dans le résultat peut être limité avec le troisième paramètre.

Remarque : l’historisation est gérée dans tempdb, le contenu de la table est régulièrement purgée au delà d’une certaine limite. La persistence de cette table dans tempdb est gérée en créant la structure dans la base système model.

OUTPUTTYPE = 0 : vue simple 

execute sp_dba_whatdoes 229
          -- Usage   : sp_dba_whatdoes [SPID] [ , [OUTPUTTYPE=0|1] [ , [ROWS] ] [ , SHOWUSAGE=0|1 ] ] ]
-- Running : sp_dba_whatdoes 229 , 0 , 100 , 1 
 
 text                                                                                                                                                                                                                                                           
 ----------------------------------------------------------------------------------------------------
 update COUNTERS set VALUE=4340761 where COUNTER='EVENT_DETAIL'
 update BENCHMARK set UPDATE_USER=538, UPDATE_DATE='2011-01-04', UPDATE_TIME='13:17:46', 
         VERSION=4, EXCH_RATE_PROVIDER=3431 where BENCHMARK_ID=3676
 commit
 update COUNTERS set VALUE=33974231 where COUNTER='EVENT_REFERENCE'
 commit
 update BENCHMARK set UPDATE_USER=538, UPDATE_DATE='2011-01-04', UPDATE_TIME='13:18:25', 
        VERSION=5, EXCH_RATE_PROVIDER=26466 where BENCHMARK_ID=3676
 commit
 update COUNTERS set VALUE=4369873 where COUNTER='EVENT'
 update COUNTERS set VALUE=33974575 where COUNTER='EVENT_REFERENCE'
 update COUNTERS set VALUE=4341226 where COUNTER='EVENT_DETAIL'
 commit

Les n dernières instructions SQL sont affichées dans leur ordre d’exécution.

OUTPUTTYPE = 1 : vue détaillée 

execute sp_dba_whatdoes 59,1,10,0
go
           
 SPID Bat  Seq CPU Wait MemKb Reads_P Reads_L Rws Err Db      Start    Dur text                                              
 ---- ---- --- --- ---- ----- ------- ------- --- --- ------- -------- --- --------------------------------------------------
   59 7375   1   0    0    26       0       6   2   0 smartco 15:21:36   0 select NAME from SECURITY_NAME where SECURITY_ID=1
   59 7376   1   0    0    26       0       8   2   0 smartco 15:21:36   0 select SECURITY_CODE from SECURITY_CODE where SECU
   59 7377   1   3    0   110       0      14   2   0 smartco 15:21:36   3 update COUNTERS set VALUE=34 where COUNTER='#EXP_F
   59 7378   1   3    0    10       0       0   0   0 smartco 15:21:36   3 commit                                            
   59 7379   1   0    0    26       0       6   2   0 smartco 15:22:40   0 select SHORT_NAME from SECURITY_NAME where SECURIT
   59 7380   1   0    0    26       0      66   2   0 smartco 15:22:40   0 select CURRENCY from SECURITY where SECURITY_ID=24
   59 7381   1   0    0    26       0      60   0   0 smartco 15:22:40   0 select QUOTE_CURRENCY from SECURITY_QUOTE where SE
   59 7382   1   0    0    26       0      22   0   0 smartco 15:22:40   0 select QUOTE_LAST from SECURITY_QUOTE where SECURI
   59 7383   1   3    0   110       0      14   2   0 smartco 15:22:40   3 update COUNTERS set VALUE=35 where COUNTER='#EXP_F
   59 7384   1   3    0    10       0       0   0   0 smartco 15:22:40   3 commit

Le texte SQL devient partiel dans cette vue mais cette version trace plus finement l’état d’avancement d’un traitement (lectures, CPU, lignes…). Les informations captées sont :

  • BAT, Seq : BatchID et SequenceID de l’opération.
  • CPU : consommation CPU du traitement.
  • Wait : nombre d’évènements d’attente.
  • MemKb : mémoire consommée par le process.
  • Reads_P : lectures physiques (Physical Reads).
  • Reads_L : lectures logiques (Logical Reads).
  • Rws : lignes (rows : @@rowcount).
  • Err : code erreur.
  • Db : base courante.
  • Start : heure de l’opération.
  • Text : code SQL partiel de l’opération.

sp_dba_cache : les caches de données sont-ils bien dimensionnés et efficaces ?

Usage

sp_dba_cache détaille la structure et les performances des caches de données.

/**
# @(#) Usage   : sp_dba_cache [ , OUTPUTTYPE=0|1|2 ]   [ , SHOWUSAGE=0|1 ]
# @(#)           OUTPUTTYPE : 0 = Summary / 1 = ObjectsStats / 2 = DetailedPerf
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
*/

Les tables utilisées sont les suivantes :

  • monCachePool
  • monDataCache
  • monCachedObject
  • sysconfigures (pour le type de cache)

Les jointures sont réalisées sur les colonnes CacheID ou CacheName

OUTPUTTYPE = 0 : vue résumée de l’efficacité des caches

Il s’agit du mode par défaut.

execute sp_dba_cache
          -- Usage   : sp_dba_cache [ , OUTPUTTYPE=0|1|2 ]  [ , SHOWUSAGE=0|1 ] 
-- Running : sp_dba_cache 0, 1
 
 Cache                Type     SizeMb UsedMb UsedPct Tbl# Idx# DB# MissPct VolPct Reuse
 -------------------- -------- ------ ------ ------- ---- ---- --- ------- ------ -----
 default data cache   Default    7000   1440      20  509  505   9       0      0    13
 smartco log          Log Only    200      0       0    1    0   1       0      0     0
 smf_distribution     Mixed       700    319      45   40   41   1       0      0     0
 smf_distribution log Log Only    200      0       0    1    0   1       0      0     6
 tempdb cache         Mixed      1000     14       1   26   26   1       0      1     0

Ce mode par défaut précise la taille de chaque cache et leur contenu en terme de nombre d’objets

  • Tbl# : nombre de tables.
  • Idx# : nombre d’indexes.
  • DB# : nombre de bases représentées.

Quelques statistiques pertinentes sont calculées dans ce résumé :

  • MissPct : Cache miss % (PhysicalReads / LogicalReads). Excellent 0 <=> 100 Catastrophe.
  • VolPct : Volatiliy % (PhysicalWrites / PhysicalReads). Excellent 0 <=> 100 Catastrophe.
  • Reuse : (PagesRead / PagesTouched). Inutile 0 <=> 2+ Bien.

OUTPUTTYPE = 1 : vue détaillée par base 

execute sp_dba_cache 1,0
           Cache                Type     SizeMb UsedMb UsedPct Tbl# Idx# DB              
 -------------------- -------- ------ ------ ------- ---- ---- ----------------
 default data cache   Default    7000      0       0    5    9 model           
 default data cache   Default    7000      0       0    5    8 appian          
 default data cache   Default    7000      0       0    5    8 dbccdb          
 default data cache   Default    7000      2       0   26   31 master          
 default data cache   Default    7000   1384      19  421  394 smartco         
 default data cache   Default    7000     43       0   14   17 sma_report      
 default data cache   Default    7000      0       0    8   10 sybsystemdb     
 default data cache   Default    7000      0       0    9   10 sybsecurity     
 default data cache   Default    7000     11       0   16   18 sybsystemprocs  
 smartco log          Log Only    200      0       0    1    0 smartco         
 smf_distribution     Mixed       700    326      46   42   41 smf_distribution
 smf_distribution log Log Only    200      0       0    1    0 smf_distribution
 tempdb cache         Mixed      1000     14       1   26   26 tempdb

Le second paramètre 0 supprime l’affichage de l’usage (pour une utilisation dans le cadre d’extractions ou d’écrans de consultation).

Les informations sont détaillées par base par rapport à la vue résumée.

OUTPUTTYPE = 2 : vue détaillée par pool (ou zone)

execute sp_dba_cache 2,0
           CacheName            Pool Type     SizeMb UsedMb UsedPct Reuse Stalls PagesRead MRUPct LRUPct
 -------------------- ---- -------- ------ ------ ------- ----- ------ --------- ------ ------
 default data cache   2K   Default    5800    474       8     0      0    220738     99      0
 default data cache   16K  Default    1000    349      34    43      0   7854152     99      0
 default data cache   4K   Default     200      0       0     5      0        56    100      0
 smartco log          2K   Log Only      1      0       0     0      0         0      0      0
 smartco log          4K   Log Only    199      0       0     0      0         2    100      0
 smf_distribution     2K   Mixed       500    158      31     0      0     38335     97      2
 smf_distribution     16K  Mixed       200    176      88     1      0    134080     77     22
 smf_distribution log 2K   Log Only      1      0       0     0      0         0      0      0
 smf_distribution log 4K   Log Only    199      0       0     6      0         6    100      0
 tempdb cache         16K  Mixed       400      0       0     0      0         0      0      0
 tempdb cache         4K   Mixed       200      0       0     0      0         4    100      0
 tempdb cache         2K   Mixed       400      3       0     1      0      3058    100      0

Les informations déja évoquées dans les vues précédentes (Taille, Usage, Reuse…) sont à présent détaillées par pool.

  • Stalls : "Number of dirty buffer retrievals", autrement dit un temps d’attente I/O (long) sur une mise à disposition d’une page de cache. Lorsque ce paramètre est particulièrement élevé, cela peut révéler un problème majeur d’accès aux disques.
  • PagesRead : comme son nom l’indique, le nombre de pages lues dans la zone.
  • MRU/LRU pct : répartition du type de remplacement des données dans la zone (MRU : Most Recent Used, LRU : Least Recent Used).

sp_dba_getobj : quels objets sont en mémoire ?

Usage

sp_dba_getobj liste les objets en mémoire.

/**
# @(#) Usage   : sp_dba_getobj [TABLE] [ , OUTPUTTYPE=0|1|10|11 ] [ , SHOWUSAGE=0|1 ]
# @(#)           TABLE : Table name, wildcards managed
# @(#)           OUTPUTTYPE : 0 = table only / 1 = include index / +10 = active only
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
*/

Les tables utilisées sont les suivantes :

  • monOpenObjectActivity
  • monCachedObject

Comme pour sp_dba_cache, les jointures sont également réalisées sur les colonnes CacheID ou CacheName.

OUTPUTTYPE = 0 : tables en mémoire

Il s’agit du mode par défaut. Ce mode liste uniquement les tables en mémoires (sans les indexes).

execute sp_dba_getobj
          -- Usage   : sp_dba_getobj [TABLE] [ , OUTPUTTYPE=0|1|10|11 ] [ , SHOWUSAGE=0|1 ] 
-- Running : sp_dba_getobj , 0, 1
 
 Cache              Tbl                            Used# LastUsed     CachedMb SizeMb Pct 
 ------------------ ------------------------------ ----- ------------ -------- ------ ----
 default data cache CLASSIFICATION_VALUE            5152 110308 15:39        0      0   85
 default data cache CLASSIFICATION_VALUE            5152 110308 15:39        0      0  100
 default data cache PORTFOLIO                      19251 110308 15:37        1      1  100
 default data cache AGGREG_CRITERION                 350 110308 15:37        0      0  100
 default data cache MANAGEMENT_TYPE                   92 110308 15:37        0      0  100
 default data cache PRICE_SOURCE                     737 110308 15:36        0      0  100
 ...
 default data cache INSTRUMENT                         0                   533    533  100

Tous les objets de la base courante actuellement en cache sont affichés :

  • LastUsed : colonne LastUsedDate de la table MDA OpenObjectActivity, autrement dit date du dernier accès utilisateur sur l’objet.
  • SizeMb : taille de l’objet.
  • CachedMb : taille de l’objet en cache.
  • Used# : nombre d’accès sur l’objet.

Le résultat de l’exemple peut paraître surprenant, un objet est en effet entièrement en cache (table INSTRUMENT) sans pourtant montrer d’accès direct (ni date, ni UsedCount).

2 causes sont possibles :

  • des données sont insérées dans une table mais pas lues.
  • la présence d’un index de type clustered, index non affiché dans cette vue qui ne référence que les tables. Ce cas de figure est évoqué dans le paragraphe qui suit.

OUTPUTTYPE = 1 : tables et indexes en mémoire

Pour illustrer l’option d’affichage des indexes en cache, dans l’exemple ci-dessous, un filtre est appliqué sur le nom de la table (INSTRUMENT). 

execute sp_dba_getobj INSTRUMENT, 1,0
           Cache              Object                             Used#    LastUsed     CachedMb SizeMb Pct 
 ------------------ ---------------------------------- -------- ------------ -------- ------ ----
 default data cache INSTRUMENT                                0                   533    533  100
 default data cache INSTRUMENT.TRANSCO_2                 278786 110308 15:39       20     20  100
 default data cache INSTRUMENT.IDX_INST_ECON_LNK_SEC     209261 110308 15:42        3     14   22
 default data cache INSTRUMENT.PK_INSTRUMENT           47366068 110308 15:42        2    521    0
 default data cache INSTRUMENT.IDX_INST_INST_TYP           2395 110308 15:33        2     20   10
 default data cache INSTRUMENT.IDX_INST_DALI_CODE        420211 110308 15:36        2     18   10
 default data cache INSTRUMENT.TRANSCO_1                 506230 110308 15:39        0     21    1
 default data cache INSTRUMENT.IDX_INST_DECALOG_CODE         75 110308 14:51        0     15    0
 NULL               INSTRUMENT.IDX_INST_UNDY_PTF        3700178 110308 06:56     NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_REF_ENT       732 110308 06:03     NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_ISSUE_DT        0                  NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_NAME            0                  NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_ISSUER          0                  NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_CRE_DTE         3 110308 06:03     NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_ISIN       262173 110308 10:38     NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INSTRUMENT_MOD_DTE         2 110308 04:58     NULL   NULL NULL
 NULL               INSTRUMENT.IDX_ADMIN_LNK_SECURITY         0                  NULL   NULL NULL
 NULL               INSTRUMENT.IDX_INST_SEDOL_CODE        94939 110308 11:11     NULL   NULL NULL
 NULL               INSTRUMENT.COUNTRY                        0                  NULL   NULL NULL

La démonstration de la provenance des accès sur la table INSTRUMENT est faite avec l’affichage des indexes en cache : les pages de données sont en cache mais accédées par les indexes

Le troisième paramètre 0 supprime l’affichage de l’usage (pour une utilisation dans le cadre d’extractions ou d’écrans de consultation).

sp_dba_getwe (get Wait Events) : quels sont les évènements d’attente ?

Usage

sp_dba_getwe liste les évènements d’attente globaux ou associés à un processus. 

/**
# @(#) Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]
# @(#)           SPID : Process ID
# @(#)           OUTPUTTYPE : 0 = Event detail / 1 = Event summary / 2 = Class summary
# @(#)           CLEAR : 0 = maintain history / 1 = clear history
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = showusage
*/

Un historique est géré, les données étant stockées dans tempdb. Le troisième paramètre permet de le vider.

monProcessWaits est La table MDA source utilisée.

Remarque : l’historisation est gérée dans tempdb, le contenu de la table est régulièrement purgée au delà d’une certaine limite. La persistence de cette table dans tempdb est gérée en créant la structure dans la base système model.

OUTPUTTYPE = 0 : détail par évènement

Il s’agit du mode par défaut. Ce mode liste les évènements d’attentes mesurés sur l’instance.

execute sp_dba_getwe
           
-- Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]
-- Running : sp_dba_getwe NULL , 0 , 0 , 1 
 
 Cl_Evt Description                                                  T_Waits T_Seconds dtFrom   dtTo     Waits  Seconds sPct
 ------ ------------------------------------------------------------ ------- --------- -------- -------- ------ ------- ----
 1/214  to be scheduled/on run queue after yield                     3227085     21015 08:34:23 15:56:37 526863    3246    0
 2/197  a disk read to complete/read to complete in parallel dbcc          3         0 08:34:23 15:56:37      0       0    0
 2/200  a disk read to complete/page reads in parallel dbcc          1184134      4068 08:34:23 15:56:37      0       0    0
 2/203  a disk read to complete/on MASS_READING bit in parallel dbcc       3         0 08:34:23 15:56:37      0       0    0
 2/29   a disk read to complete/regular buffer read to complete      3624460      5614 08:34:23 15:56:37 805537    1180    0
 ....

Pour un processus particulier : 

execute sp_dba_getwe 149
           
-- Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]
-- Running : sp_dba_getwe 149 , 0 , 0 , 1 
 
 SPID Cl_Evt Description                                                  T_Waits T_Seconds DtFrom   DtTo     Waits Seconds sPct
 ---- ------ ------------------------------------------------------------ ------- --------- -------- -------- ----- ------- ----
  149 1_214  to be scheduled/on run queue after yield                        8353         4 15:45:57 16:01:50   355       0    0
  149 2_29   a disk read to complete/regular buffer read to complete       865176      1182 15:45:57 16:01:50 30622     139   14
  149 3_31   a disk write to complete/buf write to complete before writin     403         1 15:45:57 16:01:50    39       0    0
  149 3_51   a disk write to complete/last i/o on MASS to complete            609         1 15:45:57 16:01:50    36       0    0
  149 3_52   a disk write to complete/i/o on MASS initated by another tas      45         0 15:45:57 16:01:50     1       0    0
  149 3_54   a disk write to complete/write of the last log page to compl       2         0 15:45:57 16:01:50     0       0    0
  149 3_55   a disk write to complete/i/o to finish after writing last lo    3642         6 15:45:57 16:01:50   343       0    0
  149 5_150  to take a lock/a lock                                           1561         3 15:45:57 16:01:50   176       1    0
  149 6_36   memory or a buffer/MASS to finish writing before changing        116         0 15:45:57 16:01:50     9       0    0
  149 6_46   memory or a buffer/buf write to finish getting buf from LRU      156         3 15:45:57 16:01:50     0       0    0
  149 6_157  memory or a buffer/object to be returned to pool                9120         2 15:45:57 16:01:50     0       0    0
  149 6_280  memory or a buffer/access to a memory manager semaphore            2         0 15:45:57 16:01:50     0       0    0
  149 7_250  input from the network/incoming network data                    4780     15636 15:45:57 16:01:50   358     635   68
  149 8_251  to output to the network/network send to complete             374763       828 15:45:57 16:01:50 20534      42    4
  149 9_41   internal system event/to acquire latch                            66         0 15:45:57 16:01:50     7       0    0
  149 9_70   internal system event/device semaphore                            61         0 15:45:57 16:01:50     2       0    0
  149 9_124  internal system event/mass read to finish when getting page   323153       746 15:45:57 16:01:50 37167     114   12
  149 9_169  internal system event/message                                     78         0 15:45:57 16:01:50    11       0    0
  149 9_209  internal system event/a pipe buffer to read                      111         0 15:45:57 16:01:50     5       0    0
  149 9_266  internal system event/message in worker thread mailbox          1042         0 15:45:57 16:01:50   111       0    0

 ....
  • T_Waits : nombre d’attentes (Total Waits).
  • T_Seconds : total du temps d’attente (en sec).
  • sPct : pourcentage du temps d’attente.

OUTPUTTYPE = 1 : résumé par évènement (event)

Cette option résume (somme) toutes les attentes par évènement et par classe.

execute sp_dba_getwe null,1;
           
-- Usage   : sp_dba_getwe [SPID] [ , [OUTPUTTYPE=0|1|2] [ , [CLEAR=0|1] ] , [SHOWUSAGE=0|1] ]
-- Running : sp_dba_getwe NULL , 1 , 0 , 1 
 
 Cl_Evt Description                                                  T_Waits T_Seconds dtFrom   dtTo     Waits  Seconds sPct
 ------ ------------------------------------------------------------ ------- --------- -------- -------- ------ ------- ----
 1/214  to be scheduled/on run queue after yield                     3225796     21044 08:34:23 16:09:26 525614    3273    0
 2/197  a disk read to complete/read to complete in parallel dbcc          3         0 08:34:23 16:09:26      0       0    0
 2/200  a disk read to complete/page reads in parallel dbcc          1184134      4068 08:34:23 16:09:26      0       0    0
 2/203  a disk read to complete/on MASS_READING bit in parallel dbcc       3         0 08:34:23 16:09:26      0       0    0
 2/29   a disk read to complete/regular buffer read to complete      3313682      4982 08:34:23 16:09:26 499839     561    0
 ...
  • T_Waits : nombre d’attentes (Total Waits).
  • T_Seconds : total du temps d’attente (en sec).
  • sPct : pourcentage du temps d’attente.

OUTPUTTYPE = 2 : résumé par classe d’évènements (class)

Cette option résume (somme) toutes les attentes uniquement par classe d’évènements.

execute sp_dba_getwe null,2,0,0;
           Cls Description              T_Waits T_Seconds dtFrom   dtTo     Waits  Seconds sPct
 --- ------------------------ ------- --------- -------- -------- ------ ------- ----
   1 to be scheduled          3226864     21056 08:34:23 16:11:20 526682    3282    0
   2 a disk read to complete  4513240      9086 08:34:23 16:11:20 515569     585    0
   3 a disk write to complete 2172483      4685 08:34:23 16:11:20 198502     540    0
   5 to take a lock              4249       208 08:34:23 16:11:20    496       7    0
   6 memory or a buffer         86105       124 08:34:23 16:11:20   1467       2    0
   7 input from the network   1311008   1828269 08:34:23 16:11:20 258017  362745   64
   8 to output to the network  751986      1154 08:34:23 16:11:20 148456     213    0
   9 internal system event     839458   2738963 08:34:23 16:11:20  81654  191543   34
  • T_Waits : nombre d’attentes (Total Waits).
  • T_Seconds : total du temps d’attente (en sec).
  • sPct : pourcentage du temps d’attente.

sp_dba_io : comment sont réparties les E/S ou entrées/sorties (I/O Input/output) ?

Usage

sp_dba_io liste les I/Os répartis par device, devices qui peuvent être filtrés dans l’affichage.

/**
# @(#) Usage   : sp_dba_io [ device ]  [ , [REPORTTYPE=0|1] ] [ , SHOWUSAGE=0|1 ]
# @(#)           DEVICE : device name ,wildcard managed
# @(#)           OUTPUTTYPE : 0 = summary, 1 = detailed
# @(#)           SHOWUSAGE : 0 = hide usage / 1 = show usage
*/

Les tables MDA source sont :

  • monIOQueue
  • monDeviceIO
  • sysusages et sysdevices

OUTPUTTYPE = 0 : résumé par device (support)

Il s’agit du mode par défaut. Ce mode liste, par device, les I/Os dans l’instance.

execute sp_dba_io
           -- Usage : sp_dba_io [ device ]  [ , [REPORTTYPE=0|1] ] [ , SHOWUSAGE=0|1 ]
-- Currently running : sp_dba_io  , 0 , 1 
 
 Device                 Type IO      IOpc IOT        IOTpc ms_IO
 ---------------------- ---- ------- ---- ---------- ----- -----
 log_dbccdb_1           LOG  2667805    5    4587400     0     1
 data_investment_01     DATA 2145960    4   16919600     0     7
 data_investment_21     DATA 2130452    4   26778500     0    12
 log_investment_01      LOG  2052050    4    4128400     0     2
 data_investment_23     DATA 1870013    3   16152400     0     8
 data_investment_04     DATA 1592711    3    6599800     0     4

 ...
  • IO : nombre d’I/Os.
  • IOT : temps I/O.
  • IOpc : pourcentage d’I/Os sur le device.
  • ms_IO : durée moyenne (en ms) des I/Os.

OUTPUTTYPE = 1 : détail par device

execute sp_dba_io 'log%',1,0
           Device                Type IO      IOpc ms_IO Reads  Rpc ReadsAPF Writes  Wpct R_Wpc
 --------------------- ---- ------- ---- ----- ------ --- -------- ------- ---- -----
 log_dbccdb_1          LOG  2667805    5     1 113956   0    30043 2559916    9 4/95 
 log_investment_01     LOG  2055642    3     2 415042   1       55 2082723    7 16/83
 log_FDB_01            LOG  1251549    2    25 308255   0   261525  995356    3 23/76
 log_Invest_Staging_01 LOG   797711    1    98 200328   0      158  811450    2 19/80
 log_idee_idb_01       LOG   259748    0     3 217133   0    12076  257622    0 45/54
 log_ISLF_01           LOG        3    0    66    665   0        0       1    0 99/0 


 ...
  • IO : nombre d’I/Os.
  • IOpc : pourcentage d’I/Os sur le device.
  • ms_IO : durée moyenne (en ms) des I/Os.
  • Reads : nombre de lectures.
  • Rpc : pourcentage de lectures sur le device.
  • ReadsAPF : nombre de "Prefetch" (pré lectures anticipées).
  • Writes : nombre d’écritures.
  • Wpct: pourcentage d’écritures sur le device.
  • R_Wpc : répartition lecture/ecriture en pourcentage (Read/Write).

sp_dba_spid : quelle est l’activité d’un processus au cours du temps ?

Usage

sp_dba_spid liste l’activité globale d’un processus. La méthode affiche un historique de l’activité mais aussi un différentiel de mesures. 

/**
# @(#) Usage   : sp_dba_spid SPID [ , [REPORTTYPE=0|1|2] , [SHOWUSAGE=0|1]
# @(#)           REPORTTYPE : 0 = physical / 1 = logical / 2 = Transactional
# @(#)           SHOWUSAGE  : 0 = hide usage / 1 = show usage
*/

monProcessActivity est la table source MDA utilisée ici.

Remarque : l’historisation est gérée dans tempdb, le contenu de la table est régulièrement purgée au delà d’une certaine limite. La persistence de cette table dans tempdb est gérée en créant la structure dans la base système model.

OUTPUTTYPE = 0 : activité physique

Il s’agit du mode par défaut. Ce mode liste l’activité physique classique d’un processus au cours du temps (temps CPU, temps d’attente, mémoire, lectures, écritures…).

execute sp_dba_spid 41
          -- Usage   : sp_dba_spid [SPID] , [OUTPUTTYPE=0|1|2] , [SHOWUSAGE=0|1] 
-- Running : sp_dba_spid 41 , 0 , 1
 
 Date       CPUTime WaitTime MemKB ReadsP ReadsL PagesR WritesP PagesW
 ---------- ------- -------- ----- ------ ------ ------ ------- ------
   16:53:01     100  3870800     4   1291  37667   1354     211    446
   16:53:03     100  3873300     4   1291  37667   1354     211    446
   16:53:05     100  3874900     4   1291  37667   1354     211    446
   16:53:07     100  3876900     4   1291  37667   1354     211    446
   16:53:21     100  3891400     4   1291  37667   1354     211    446
   16:53:24     100  3893800     4   1291  37667   1354     211    446
   17:02:15     100  4424800     4   1291  37667   1354     211    446
   17:06:09     100  4659000     4   1291  37667   1354     211    446
   17:06:14     100  4663900     4   1291  37667   1354     211    446
 < 16:53:01       0   793100     0      0      0      0       0      0
 > 17:06:09       0     4900     0      0      0      0       0      0

 ...
  • CPUTime : usage CPU.
  • WaitTime : temps d’attente total depuis la connexion.
  • MemKb : mémoire utilisée.
  • ReadsP : lectures physiques ( disque ).
  • ReadsL : lectures logiques ( cache ).
  • PagesR : pages lues.
  • WriteP : écritures physiques ( disque ).
  • PagesW : pages écrites.

Les deux dernières lignes mesurent les différences

  • "<" : entre la mesure courante et la première prise.
  • ">" : entre la mesure courante et l’avant-dernière prise.

OUTPUTTYPE = 1 : activité logique

Ce mode se focalise sur l’activité logique d’un processus dans le temps : verrous, accès aux tables et indexes, tables temporaires créées, nombre de transactions. 

execute sp_dba_spid 372,1,0
           Date       Locks TblAccess IdxAccess TmpObj WorkTables Trans
 ---------- ----- --------- --------- ------ ---------- -----
   16:53:01     0      3994      2707      6         13    34
   16:53:03     0      3994      2707      6         13    34
   16:53:05     0      3994      2707      6         13    34
   16:53:07     0      3994      2707      6         13    34
   16:53:21     0      3994      2707      6         13    34
   16:53:24     0      3994      2707      6         13    34
   17:02:15     0      3994      2707      6         13    34
   17:06:09     0      3994      2707      6         13    34
   17:06:14     0      3994      2707      6         13    34
   17:10:29     0      3994      2707      6         13    34
   17:10:34     0      3994      2707      6         13    34
 < 16:53:01     0         0         0      0          0     0
 > 17:10:29     0         0         0      0          0     0
 ...
  • Locks : verrous posés.
  • TblAccess : nombre d’accès à des tables.
  • IdxAccess : indexes utilisés.
  • TmpObj: tables temporaires utilisées.
  • Worktable : nombre de tables de travail temporaires pour des opérations internes comme les tris, les regroupements (order by, group by…).
  • Trans : nombre de transactions

OUTPUTTYPE = 2 : activité transactionnelle

execute sp_dba_spid 372,2,0
           Date       Trans Commits Rollbacks ULCBytesWritten ULCFlushes ULCMaxUsage ULCCurrentUsage
 ---------- ----- ------- --------- --------------- ---------- ----------- ---------------
   16:53:01    34      34         0          632416        183       10216               0
   16:53:03    34      34         0          632416        183       10216               0
   16:53:05    34      34         0          632416        183       10216               0
   16:53:07    34      34         0          632416        183       10216               0
   16:53:21    34      34         0          632416        183       10216               0
   16:53:24    34      34         0          632416        183       10216               0
   17:02:15    34      34         0          632416        183       10216               0
   17:06:09    34      34         0          632416        183       10216               0
   17:06:14    34      34         0          632416        183       10216               0
   17:10:29    34      34         0          632416        183       10216               0
   17:10:34    34      34         0          632416        183       10216               0
   17:13:25    34      34         0          632416        183       10216               0
 < 16:53:01     0       0         0               0          0           0               0
 > 17:10:34     0       0         0               0          0           0               0
 ...
  • Trans : nombre de transactions.
  • Commits : nombre de transactions validées.
  • Rollbacks : nombre de transactions annulées.
  • ULCBytesWritten : nombre d’écritures dans le cache ULC (User Log Cache).
  • ULCFlush : nombre d’écritures du cache ULC vers le journal.
  • ULCMaxUsage : taille maximum du cache ULC.
  • ULCCurrentUsage : taille actuelle du cache ULC.