INF : Procédure d'affichage des données de blocage de SQL Server 2000

Traductions disponibles Traductions disponibles
Numéro d'article: 283725 - Voir les produits auxquels s'applique cet article
Ancien nº de publication de cet article : F283725
Agrandir tout | Réduire tout

Résumé

Cet article définit les vues qui peuvent être utilisées pour afficher les données de blocage contenues dans un fichier de trace créé par le travail décrit dans l'article suivant de la Base de connaissances Microsoft :
283696 INF : Contrôle des performances et de l'activité de SQL Server 2000
Avant de créer ces vues, le fichier de trace doit être importé dans une table SQL Server 2000 nommée ActivityTrace. Le script de la procédure stockée nommée trace_gettable, qui importe les données du fichier de trace dans une table SQL Server 2000, est défini dans l'article suivant :
283784 INF : Procédure d'affichage des données d'activité de SQL Server 2000
Utilisez les vues définies ci-dessous pour afficher les données de blocage comme indiqué :
  • La vue v_blocked liste les ID du processus serveur (SPID, System Process ID) qui sont bloquées.
  • La vue v_blocking liste toutes les SPID impliquées dans le blocage.
  • La vue v_blockspids1 est un résumé des SPID bloquant d'autres SPID. La colonne RowNumber dans cette vue correspond au nombre de lignes de la table ActivityTrace et du fichier de trace. Tapez la requête suivante pour afficher la colonne TextData de la table ActivityTrace pour les lignes situées avant la ligne listée dans la vue v_blockspid1.
    select TextData from ActivityTrace where RowNumber > 4080 and RowNumber < 5000
    Remplacez le deuxième nombre (5000) dans la requête ci-dessus par la valeur de RowNumber renvoyée dans v_blockspid1. Changez le premier nombre (4080) pour refléter le nombre de lignes précédentes que vous souhaitez afficher. Par exemple, si v_blockspid1 renvoie RowNumber 52365 et que vous souhaitez afficher les 20 lignes de la table ActivityTrace situées avant cette ligne, tapez l'instruction suivante :
    select TextData from ActivityTrace where RowNumber > 52345 and RowNumber < 52365
  • La vue v_blockspids2 est une liste détaillée des SPID bloquant d'autres SPID.

Plus d'informations

CREATE    view v_blocked as
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) != 0
CREATE   view v_blocking as
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
create view v_blockspids1 as
select top 100 percent
       cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,min(RowNumber) as RowNumber
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)
 group by cast(substring(BinaryData,1,2) as smallint)
 order by cast(substring(BinaryData,1,2) as smallint)
CREATE    view v_blockspids2 as 
select RowNumber,StartTime
      ,TextData
      ,cast(substring(BinaryData,1,2) as smallint) as [spid]
      ,cast(substring(BinaryData,3,2) as smallint) as [kpid]
      ,cast(substring(BinaryData,5,2) as smallint) as [blocked]
      ,cast(substring(BinaryData,7,2) as binary(2)) as [waittype]
      ,cast(substring(BinaryData,9,4) as int) as [waittime]
      ,cast(substring(BinaryData,13,64) as nchar(32)) as [lastwaittype]
      ,cast(substring(BinaryData,77,512) as nchar(256)) as [waitresource]
      ,cast(substring(BinaryData,589,2) as smallint) as [dbid]
      ,cast(substring(BinaryData,591,2) as smallint) as [uid]
      ,cast(substring(BinaryData,593,4) as int) as [cpu]
      ,cast(substring(BinaryData,597,8) as bigint) as [physical_io]
      ,cast(substring(BinaryData,605,4) as int) as [memusage]
      ,cast(substring(BinaryData,609,8) as datetime) as [login_time]
      ,cast(substring(BinaryData,617,8) as datetime) as [last_batch]
      ,cast(substring(BinaryData,625,2) as smallint) as [ecid]
      ,cast(substring(BinaryData,627,2) as smallint) as [open_tran]
      ,cast(substring(BinaryData,629,60) as nchar(30)) as [status]
      ,cast(substring(BinaryData,689,86) as binary(86)) as [sid]
      ,cast(substring(BinaryData,775,256) as nchar(128)) as [hostname]
      ,cast(substring(BinaryData,1031,256) as nchar(128)) as [program_name]
      ,cast(substring(BinaryData,1287,16) as nchar(8)) as [hostprocess]
      ,cast(substring(BinaryData,1303,32) as nchar(16)) as [cmd]
      ,cast(substring(BinaryData,1335,256) as nchar(128)) as [nt_domain]
      ,cast(substring(BinaryData,1591,256) as nchar(128)) as [nt_username]
      ,cast(substring(BinaryData,1847,24) as nchar(12)) as [net_address]
      ,cast(substring(BinaryData,1871,24) as nchar(12)) as [net_library]
      ,cast(substring(BinaryData,1895,256) as nchar(128)) as [loginame]
      ,cast(substring(BinaryData,2151,128) as binary(128)) as [context_info]
  from ActivityTrace
 where substring(TextData,1,8) = N'blocking'
   and cast(substring(BinaryData,5,2) as smallint) = 0
   and cast(substring(BinaryData,1,2) as smallint) in 
     (select cast(substring(BinaryData,5,2) as smallint) as spid
        from ActivityTrace
       where cast(substring(BinaryData,5,2) as smallint) != 0)

Propriétés

Numéro d'article: 283725 - Dernière mise à jour: jeudi 6 novembre 2003 - Version: 3.1
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 2000 Standard
Mots-clés : 
_ik kbinfo KB283725
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com