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

Ancien nº de publication de cet article : F283725
Cet article a été archivé. Il est proposé « en l'état » et ne sera plus mis à jour.
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 asselect 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 asselect 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 asselect 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

ID d'article : 283725 - Dernière mise à jour : 01/16/2015 21:22:30 - Révision : 3.1

  • Microsoft SQL Server 2000 Standard
  • kbnosurvey kbarchive _ik kbinfo KB283725
Commentaires