Vous êtes actuellement hors ligne, en attente de reconnexion à Internet.

Fichier INF: Comment faire pour surveiller le blocage de SQL Server 7.0

IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d’articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d’avoir accès, dans votre propre langue, à l’ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s’exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s’efforce aussi continuellement de faire évoluer son système de traduction automatique.

La version anglaise de cet article est la suivante: 251004
Cet article a été archivé. Il est proposé « en l'état » et ne sera plus mis à jour.
Résumé
Cet article documente l'utilisation et la création d'une procédure stockée que vous pouvez utiliser pour diagnostiquer des problèmes de blocage et de performances. Pour une description de la façon de comprendre et résoudre le blocage, consultez l'article suivant dans la Base de connaissances :
224453 Fichier INF: Description et résolution des problèmes de blocage de SQL Server 7.0
Pour d'informations sur ces mêmes problèmes dans SQL Server 2000, consultez l'article suivant dans la Base de connaissances :
271509 Fichier INF: Comment faire pour surveiller le blocage de SQL Server 2000
Plus d'informations
La procédure sp_blocker_pss70 stockés, décrite dans cette section, consigne les informations suivantes :
  • Le premier temps (en fonction l'ordinateur qui exécute SQL Server) afin que cet échantillonnage de blocage peut être aligné à-temps avec les autres informations de performances, comme un journal Analyseur de performances de Microsoft Windows NT ou un journal du Générateur de profils SQL.

  • Informations sur les connexions à SQL Server, en interrogeant la table système sysprocesses .

  • Informations sur les ressources verrou, en interrogeant la table système syslockinfo .

  • Informations sur les attentes de ressources, en exécutant DBCC SQLPERF(WAITSTATS).

  • L'actuel exécutent lot SQL pour les connexions qui sont bloquées par d'autres personnes ou bloquer d'autres, en exécutant DBCC INPUTBUFFER.

  • L'heure de fin, conformément à l'ordinateur qui exécute SQL Server.
La procédure stockée a été créée avec les optimisations suivantes pour réduire l'effet de performances et de blocage en exécutant cette procédure stockée :
  • Il n'existe aucune sortie générée, sauf si au moins une connexion est en attente sur une ressource.

  • Les tables de système sysprocesses et syslockinfo de la base de données maître sont interrogées directement, pour améliorer les performances et empêcher cette procédure stockée soit bloquée. Par conséquent, cette procédure stockée est spécifique à Microsoft SQL Server 7.0.

  • Création d'un petit worktable sortie par le curseur pour obtenir le DBCC INPUTBUFFER ; ce n'est avoir aucun impact notable sur l'utilisation de la base de données tempdb .

  • Étant donné que le blocage peut changer lors de la collecte les informations, il existe un mode rapide qui accroît les performances en diminuant les résultats vers les lignes pertinentes des tables système sysprocesses et syslockinfo .

  • Si vous voulez suivre les attentes de ressource non verrou, il existe un mode accès rapide qui provoque le verrou de sortie à être omis.
Cette procédure stockée est utile en exécutant il à partir d'un outil de requête. Toutefois, Microsoft suggère que vous procédez comme suit pour effectuer une analyse blocage :
  1. Créer la procédure stockée sp_blocker_pss70 , qui est inclus à la fin de cet article, à partir de n'importe quel outil de requête lorsque vous êtes connecté avec une connexion qui possède des privilèges sysadmin sur le serveur SQL Server ou l'instance de SQL Server que vous souhaitez surveiller.
  2. Créez un fichier de script avec la requête suivante pour exécuter la procédure stockée dans une boucle. Notez que le délai est comprise entre 5 et 60 secondes :
    WHILE 1=1BEGIN   EXEC master.dbo.sp_blocker_pss70   -- Or for fast mode    -- EXEC master.dbo.sp_blocker_pss70 @fast=1   -- Or for latch mode    -- EXEC master.dbo.sp_blocker_pss70 @latch=1   WAITFOR DELAY '00:00:15'ENDGO					
  3. Cette sortie est très utile lorsqu'il est combiné avec un journal de l'Analyseur de performances de Microsoft Windows NT et un journal du Générateur de profils, donc créer deux en même temps est suggérée. Pour les informations concernant les événements du générateur de profils et Analyseur de performances pour capturer et pour savoir comment interpréter les résultats, consultez l'article suivant dans la Base de connaissances Microsoft :
    224453 Fichier INF: Description et résolution des problèmes de blocage de SQL Server 7.0
  4. Exécutez le fichier de script créé à l'étape 2 d'un l'isql.exe ou Osql.exe requête outil à partir d'un command prompt Windows sur l'ordinateur exécute SQL Server vous envisagez de surveiller pour empêcher les problèmes de mise en réseau déconnectent l'outil de requête. Voici une ligne de commande exemple pour démarrer Osql.exe, qui suppose que le client est exécuter à partir l'ordinateur qui exécute SQL Server et que le fichier de script est nommé Checkblk.sql. Veillez à corriger le paramètre S , remplacez « serveur » par le nom de votre serveur SQL serveur, correct le paramètre i et remplacez « checkblk.sql » par le chemin d'accès et le nom au fichier de script créé à l'étape 2.
    osql -E -Sserver -icheckblk.sql -ocheckblk.out -w2000						
    Notez que vous devez utiliser la ligne de commande autre commutateurs pour les raisons suivantes :

    • Pour éviter l'habillage de ligne dans les fichiers de sortie, qui facilite la lecture.
    • Pour envoyer la sortie vers un fichier, spécifié avec- o paramètre, plutôt qu'à l'écran afin que si l'outil de requête a des problèmes, vous toujours avez exporté jusqu'à ce que l'outil de requête échoue.
Voici le script que vous permet de créer la procédure sp_blocker_pss70 stockées :

use masterGOif exists (select * from sysobjects where id = object_id('dbo.sp_blocker_pss70') and sysstat & 0xf = 4)   drop procedure dbo.sp_blocker_pss70GOcreate proc sp_blocker_pss70 (@latch int = 0, @fast int = 1)as --version 13set nocount ondeclare @spid varchar(6)declare @blocked varchar(6)declare @time datetimedeclare @time2 datetimedeclare @time3 datetimedeclare @dbname nvarchar(128)declare @IsDetached tinyintdeclare @IsEmergencyMode tinyintdeclare @IsInLoad tinyintdeclare @IsInRecovery tinyintdeclare @IsNotRecovered tinyintdeclare @IsOffline tinyintdeclare @IsShutDown tinyintdeclare @IsSingleUser tinyintdeclare @IsSuspect tinyintset @time = getdate()select spid, ecid, blocked, waittype, dbid, 'ignore_app' = case   when convert(varchar(128),hostname) = 'PSSDIAG' then 1 else 0 end into #probclients  from master..sysprocesses where blocked!=0 or waittype != 0x0000create unique clustered index pc on #probclients (blocked, spid, ecid)set @time3 = getdate()if exists (select spid from #probclients where ignore_app != 1 or waittype != 0x020B)begin   set @time2 = getdate()   print ''   print '7 Start time: ' + convert(varchar(26), @time, 121) + ' '      + convert(varchar(12), datediff(ms,@time,@time2)) + ' ' + convert(varchar(12), datediff(ms,@time,@time3))   insert #probclients select distinct blocked, 0, 0, 0x0000, 0, 0 from #probclients   where blocked not in (select spid from #probclients) and blocked != 0   if (@fast = 1)   begin      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master..sysprocesses      where blocked!=0 or waittype != 0x0000         or spid in (select blocked from #probclients where blocked != 0)         or spid in (select spid from #probclients where blocked != 0)      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from #probclients where waittype != 0x0000      if exists(select blocked from #probclients where blocked != 0)      begin         print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from #probclients         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)         if @latch = 0         begin            print 'SYSLOCKINFO'            set @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'IS'                                        when 5 then 'SIU'                                        when 6 then 'IS-S'                                        when 7 then 'IX'                                        when 8 then 'SIX'                                        when 9 then 'S'                                        when 10 then 'U'                                        when 11 then 'IIn-Nul'                                        when 12 then 'IS-X'                                        when 13 then 'IU'                                        when 14 then 'IS-U'                                        when 15 then 'X'                                        when 16 then 'BU' end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end            from master.dbo.syslockinfo s,               #probclients p            where p.spid = s.req_spid            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end -- blocking via locks      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)   end  -- fast set   else     begin  -- Fast not set      print ''      print 'SYSPROCESSES ' + ISNULL (@@servername,'(null)') + ' ' + str(@@microsoftversion)      select spid, status, blocked, open_tran, waitresource, waittype,          waittime, cmd, lastwaittype, cpu, physical_io,         memusage,last_batch=convert(varchar(26), last_batch,121),         login_time=convert(varchar(26), login_time,121), net_address,         net_library, dbid, ecid, kpid, hostname, hostprocess,         loginame, program_name, nt_domain, nt_username, uid, sid      from master..sysprocesses      print 'ESP ' + convert(varchar(12), datediff(ms,@time2,getdate()))       print ''      print 'SYSPROC FIRST PASS'      select spid, ecid, waittype from #probclients where waittype != 0x0000      if exists(select blocked from #probclients where blocked != 0)      begin          print 'Blocking via locks at ' + convert(varchar(26), @time, 121)         print ''         print 'SPIDs at the head of blocking chains'         select spid from #probclients         where blocked = 0 and spid in (select blocked from #probclients where spid != 0)         if @latch = 0          begin            print 'SYSLOCKINFO'            set @time2 = getdate()            select spid = convert (smallint, req_spid),               ecid = convert (smallint, req_ecid),               rsc_dbid As dbid,               rsc_objid As ObjId,               rsc_indid As IndId,               Type = case rsc_type when 1 then 'NUL'                                    when 2 then 'DB'                                    when 3 then 'FIL'                                    when 4 then 'IDX'                                    when 5 then 'TAB'                                    when 6 then 'PAG'                                    when 7 then 'KEY'                                    when 8 then 'EXT'                                    when 9 then 'RID' end,               Resource = substring (rsc_text, 1, 16),               Mode = case req_mode + 1 when 1 then NULL                                        when 2 then 'Sch-S'                                        when 3 then 'Sch-M'                                        when 4 then 'IS'                                        when 5 then 'SIU'                                        when 6 then 'IS-S'                                        when 7 then 'IX'                                        when 8 then 'SIX'                                        when 9 then 'S'                                        when 10 then 'U'                                        when 11 then 'IIn-Nul'                                        when 12 then 'IS-X'                                        when 13 then 'IU'                                        when 14 then 'IS-U'                                        when 15 then 'X'                                        when 16 then 'BU' end,               Status = case req_status when 1 then 'GRANT'                                        when 2 then 'CNVT'                                        when 3 then 'WAIT' end            from master.dbo.syslockinfo            print 'ESL ' + convert(varchar(12), datediff(ms,@time2,getdate()))          end -- latch not set      end      else         print 'No blocking via locks at ' + convert(varchar(26), @time, 121)   end -- Fast not set   print ''   print 'DBCC SQLPERF(WAITSTATS)'   dbcc sqlperf(waitstats)   print ''   print ''   Print '*********************************************************************'   Print 'Print out DBCC INPUTBUFFER for all blocked or blocking spids.'   Print '*********************************************************************'   declare ibuffer cursor fast_forward for   select cast (spid as varchar(6)) as spid, cast (blocked as varchar(6)) as blocked   from #probclients   where (spid <> @@spid) and (blocked!=0      or (waittype != 0x0000 and ignore_app = 0)      or spid in (select blocked from #probclients where blocked != 0))   open ibuffer   fetch next from ibuffer into @spid, @blocked   while (@@fetch_status != -1)   begin      print ''      exec ('print ''DBCC INPUTBUFFER FOR SPID ' + @spid + '''')      exec ('dbcc inputbuffer (' + @spid + ')')      fetch next from ibuffer into @spid, @blocked   end   deallocate ibuffer   Print ''   Print '*******************************************************************************'   Print 'Print out DBCC OPENTRAN for active databases for all blocked or blocking spids.'   Print '*******************************************************************************'   declare ibuffer cursor fast_forward for   select distinct cast (dbid as varchar(6)) from #probclients   where dbid != 0   open ibuffer   fetch next from ibuffer into @spid   while (@@fetch_status != -1)   begin      print ''      set @dbname = db_name(@spid)      select @IsDetached = DATABASEPROPERTY(@dbname,'IsDetached')      select @IsEmergencyMode = DATABASEPROPERTY(@dbname,'IsEmergencyMode')      select @IsInLoad = DATABASEPROPERTY(@dbname,'IsInLoad')      select @IsInRecovery = DATABASEPROPERTY(@dbname,'IsInRecovery')      select @IsNotRecovered = DATABASEPROPERTY(@dbname,'IsNotRecovered')      select @IsOffline = DATABASEPROPERTY(@dbname,'IsOffline')      select @IsShutDown = DATABASEPROPERTY(@dbname,'IsShutDown')      select @IsSingleUser = DATABASEPROPERTY(@dbname,'IsSingleUser')      select @IsSuspect = DATABASEPROPERTY(@dbname,'IsSuspect')      print 'DBCC OPENTRAN FOR DBID ' + @spid + ' ['+ @dbname + ']'      if @IsDetached + @IsEmergencyMode + @IsInLoad + @IsInRecovery + @IsNotRecovered + @IsOffline         + @IsShutDown + @IsSingleUser + @IsSuspect = 0         dbcc opentran(@dbname)      else         print 'Skipped: Status=ID=' + str(@IsDetached,1) + ' IEM=' + str(@IsEmergencyMode,1) + ' IIL='            + str(@IsInLoad,1) + ' IIR=' + str(@IsInRecovery,1) + ' INR' + str(@IsNotRecovered,1) + ' IO='            + str(@IsOffline,1) + ' ISD=' + str(@IsShutDown,1) + ' ISU=' + str(@IsSingleUser,1) + ' IS='           + str(@IsSuspect,1)       print ''      if @spid = '2' select @blocked = 'Y'      fetch next from ibuffer into @spid   end   deallocate ibuffer   if @blocked != 'Y'    begin      print ''      print 'DBCC OPENTRAN FOR DBID  2 [tempdb]'      dbcc opentran ('tempdb')   end   print ''   print 'End time: ' + convert(varchar(26), getdate(), 121)end -- Allelse  print '7 No Waittypes: ' + convert(varchar(26), @time, 121) + ' '    + convert(varchar(12), datediff(ms,@time,@time3)) + ' ' + ISNULL (@@servername,'(null)')GO 	
performances

Avertissement : Cet article a été traduit automatiquement.

Propriétés

ID d'article : 251004 - Dernière mise à jour : 12/05/2015 18:15:15 - Révision : 3.2

Microsoft SQL Server 7.0 Standard

  • kbnosurvey kbarchive kbmt kbhowto kbinfo kbsqlserv700 KB251004 KbMtfr
Commentaires