Le bulletin technique de SQL Server - comment faire pour résoudre un blocage

Bulletin technique de SQL Server

Rubrique a expliqué dans ce numéro : Comment faire pour résoudre un blocage
Pour la documentation mise à jour et les recommandations qui s’applique à cette rubrique dans les versions ultérieures de SQL Server, consultez les sites Web suivants :

Résolution des problèmes de blocage
Détection et fin des blocages

Si vous tentez de résoudre les problèmes de performance d’ordre général, reportez-vous à la documentation suivante :

Amélioration des performances des requêtes SQL

Objectif

Pour identifier, pour résoudre les problèmes et recommander une solution pour résoudre un blocage.

Introduction

Cet article examine une situation de blocage et fournit des étapes pour résoudre le blocage. Chaque blocage peut être différente et peut être provoqué par plusieurs variables d’environnement différent. Les informations fournies dans cet article peuvent vous aider à identifier et résoudre un blocage.

Étude de cas

Dans une étude de cas, nous examinons un système 911 a six opérateurs. Au cours des pics d’activité, qu’ils utilisent l’application frontale de Microsoft Visual Basic rencontre des connexions interrompues. En raison de connexions cassées, les opérateurs doivent ré-entrer des données. Pour un système 911 qui fonctionne 24 heures par jour, sept jours par semaine, ce comportement est inacceptable.

En quoi consiste un blocage ?

Un blocage se produit lorsque deux server ID de processus système (SPID) sont en attente d’une ressource et aucun ne peut le faire progresser parce que l’autre processus empêche d’accéder à la ressource.

Thread du gestionnaire verrouillage vérifie les verrous mortels. Algorithme de détection d’un gestionnaire de verrous détecte un blocage, le Gestionnaire de verrous choisit parmi les SPID comme victime. Le Gestionnaire de verrous lance un message d’erreur 1205 qui est envoyé au client, et le Gestionnaire de verrous arrête le SPID. Tuer le SPID libère les ressources et autorise l’autre SPID continuer. Le SPID qui est victime de blocage de la mise à mort est la cause de l’interruption de la connexion qui rencontre de l’application frontale de Visual Basic.

Dans une application bien conçue, l’application frontale doit intercepter l’erreur 1205 se reconnecter à SQL Server et soumettre de nouveau la transaction.

Bien que les blocages peuvent être minimisées, qu’ils ne peuvent pas toujours être évités. C’est pourquoi l’application frontale doit être conçue pour gérer les verrous mortels.

Comment identifier un blocage

Étape 1

Pour identifier un blocage, vous devez d’abord obtenir les informations du journal. Si vous soupçonnez un blocage, vous devez collecter des informations sur les (SPID) et les ressources qui sont impliqués dans le blocage. Pour ce faire, ajoutez l’option - T1204 et - T3605 paramètres de démarrage de SQL Server. Pour ajouter ces deux paramètres de démarrage, procédez comme suit :
  • Démarrez SQL Server Enterprise Manager.
  • Sélectionnez, puis cliquez sur le serveur.
  • Cliquez sur Propriétés.
  • Cliquez sur paramètres de démarrage.
  • Dans la boîte de dialogue Paramètres de démarrage , tapez -T1204 en texte paramètres puis cliquez sur Ajouter.
  • Dans la zone de texte paramètres , tapez
    -T3605, puis cliquez sur Ajouter.
  • Cliquez sur OK.

Les paramètres de démarrage prendront effet lorsque SQL Server est arrêté et redémarré puis.

-T1204 démarrage paramètre collecte les informations concernant le processus et les ressources lorsque l’algorithme de détection de blocage rencontre un blocage. -T3605 paramètre de démarrage écrit ces informations dans les journaux d’erreurs SQL Server.

-T1205 démarrage paramètre collecte les informations chaque fois que l’algorithme de blocage vérifie à un blocage, pas lorsqu’un blocage est détecté. Vous n’avez pas à utiliser le - paramètre de démarrage T1205.


Si vous n’utilisez pas-paramètre de démarrage T1205, Voici un exemple de la sortie qui sera dans le journal des erreurs SQL Server :

2003-05-14 11:46:26.76 spid4     Starting deadlock search 12003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4
2003-05-14 11:46:26.76 spid4 End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 ----------------------------------
2003-05-14 11:46:31.76 spid4 Starting deadlock search 2



Parfois, vous ne peut-être en mesure d’arrêter et de redémarrer SQL Server. Dans ce cas, vous pouvez utiliser l’Analyseur de requêtes pour exécuter la commande suivante pour activer les indicateurs de trace du blocage.

Remarque De cette façon, que vous pouvez rassembler des informations sur les blocages immédiatement. « -1 » indique tous les SPID.

dbcc traceon (1204, 3605, -1)go
dbcc tracestatus(-1)
go


Étape 2

Ensuite, vous devez collecter une trace du Générateur de profils SQL. Si vous activez l’indicateur de trace d’un interblocage, vous obtiendrez la plupart des informations requises, mais pas toujours. Par exemple, dans une étude de cas la sortie d’indicateur de trace identifié que la procédure stockée système sp_cursoropen et une « mise à jour tblQueuedEvents la valeur notifyid = 3, ResynchDate » instruction ont été impliquées dans un blocage. Malheureusement, vous ne connaissez pas la définition de la procédure stockée système sp_cursoropen . Vous n’avez l’instruction de mise à jour complète pas car il a été tronqué.

Générateur de profils SQL peut obtenir les instructions complètes, outre les plans d’exécution des instructions. Une trace du Générateur de profils SQL possède également un événement de verrouillage pour « blocage » et « deadlock chain ». « Blocage » correspond à le T1204 indicateur-, et « deadlock chain » correspond à le T1205 indicateur. Activer les indicateurs de trace du blocage et l’exécution d’une trace du Générateur de profils SQL lors de l’occurrence d’un blocage devraient vous permettre des données que vous devez posséder pour résoudre un blocage. Dans ce cas et dans d’autres, en cours d’exécution du Générateur de profils SQL modifie la durée d’exécution suffisamment pour empêcher le blocage. Par conséquent, vous généralement capture les informations d’interblocage avec les indicateurs de trace, et vous exécutez du Générateur de profils SQL.

Résolution des problèmes liés à un blocage

Après un blocage se produit, vous pouvez rassembler des informations sur le blocage à l’aide de la
sqldiag utilitaire et à l’aide du Générateur de profils SQL. Dans la sortie du fichier SQLDiag.txt, recherchez une entrée « Attente de graphique ». Un « attente-graphique « entrée indique qu’un verrou mortel a été rencontré.

Voici un exemple de la sortie que vous pouvez consulter dans le journal des erreurs SQL Server lorsque vous utilisez-paramètre de démarrage T1205.

2003-05-05 15:11:50.80 spid4    Wait-for graph2003-05-05 15:11:50.80 spid4    Node:1
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Victim Resource Owner:
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: RPC Event: sp_cursoropen;1
2003-05-05 15:11:50.80 spid4 SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1937f2a0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0

2003-05-05 15:11:50.80 spid4 Node:2
2003-05-05 15:11:50.80 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538) Value:0x193
2003-05-05 15:11:50.80 spid4 Requested By:
2003-05-05 15:11:50.80 spid4 Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2, ResynchDate
2003-05-05 15:11:50.80 spid4 SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2003-05-05 15:11:50.80 spid4 Owner:0x1936e420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60 ECID:0
2003-05-05 15:11:50.80 spid4 Grant List 0::
2003-05-05 15:11:50.80 spid4 KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0



Dans l’entrée « Attente de graphique », vous avez le nœud 1 et nœud 2. Dans chaque nœud, vous avez une section de subvention et une section de demande. La section de la subvention est la « liste de subvention » et la section de la demande est la « requête par ».
Dans chaque nœud, vous pouvez identifier les éléments suivants :
  • Le SPID.
  • La commande que le SPID était en cours d’exécution.
  • La ressource.
  • Le mode de verrouillage de la ressource.

Par exemple, dans le nœud 1, la liste des autorisations, SPID 55 a eu un verrou de mise à jour, Mode : U, sur une ressource clé : 8:1653632984:2. 8 = DBID, 1653632984 = ObjectID et 2 = Indid. Pour obtenir le numéro d’identification de base de données, exécutez la procédure sp_helpdb stockées. Pour obtenir la table, exécutez le code suivant :

select * from sysobjects where id = 1653632984


Pour obtenir l’index, exécutez le code suivant :
select * from sysindexes where indid = 2 and id = 1653632984

Si IndexId est égal à 2, vous savez que l’index est un index non ordonné en clusters. La commande qui exécutait le SPID 55 a la procédure sp_cursoropen stockées.

Nœud 2, la liste des autorisations, SPID 60 a reçu un verrou exclusif, Mode : X de ressource clé : 8:1653632984:1. 8 = DBID, 1653632984 = ObjectID, 1 = Indid. Il s’agit de la même table mais index 1 est l’index ordonné en clusters. La commande qui exécutait le SPID 60 a :
Update tblQueuedEvents Set NotifyID = 2, ResynchDate

Un IndexId est égale à 1 est un index ordonné en clusters.

Un IndexId est égal à 2 est un index non ordonné en clusters.

Remarque Blocages sont très sensibles.

Ensuite, dans le nœud 1, demander par SPID 55 demande un verrou partagé, Mode : S, sur IndexId = 1. Dans le nœud 2, demander par SPID 60 demande un verrou exclusif, Mode : X, IndexId = 2. Étant donné que ces requêtes de verrouillage se produisent en même temps, le blocage se produit. Chaque SPID d’accordé les verrous empêchent les verrous requis de se poursuivre.

Le tableau suivant présente le tableau de compatibilité de verrouillage. Pour plus d’informations sur la compatibilité de verrouillage, reportez-vous à la rubrique « Compatibilité de verrouillage » dans la documentation en ligne de SQL Server 2000.

Tableau de compatibilité de verrouillage
Mode demandéESTSUIXSIXX
Intent partagé (IS)OuiOuiOuiOuiOuiNon.
Partagé (S)OuiOuiOuiNon.Non.Non.
Mise à jour (U)
OuiOuiNon.Non.Non.Non.
Intent exclusif (IX)OuiNon.Non.OuiNon.Non.
Partagé avec intent exclusif (SIX)
OuiNon.Non.Non.Non.Non.
Exclusif (X)Non.Non.Non.Non.Non.Non.


Ensuite, en regardant la sortie, vous identifiez 1653632984 ObjectId que la table tblQueuedEvents , et vous obtenez une procédure stockée sp_help pour la table de sortie. Il y avait deux index sur la table. Les deux index ont été ix_tblQueuedEvents et PK_tblQueuedEvent. ix_tblQueuedEvents est un index ordonné en clusters sur ResynchDate et PK_tblQueuedEvent est une clé primaire, un index non ordonné en clusters unique sur EventSID.


La trace du Générateur de profils SQL n’a pas pu capturer l’occurrence de blocage. N’oubliez pas, les interblocages sont très dépendant du temps. La surcharge de générateur de profils SQL ajoutés probablement peu de temps pour l’exécution d’un processus et qui a empêché SQL Profiler mise en route dans une situation de blocage. Toutefois, il ne fournissait des informations que vous pouvez utiliser pour résoudre le problème. Vous avez trouvé l’instruction tblQueuedEvents de mise à jour intégrale pour être semblable au suivant :

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16' where eventSID = 73023
Vous avez également trouvé le plan d’exécution. Vous n’avez toujours pas l’instruction de la procédure complète sp_cursoropen stockées, mais vous n’avez pas suffisamment d’informations pour recommander une solution pour résoudre le blocage.

Voici le plan d’exécution.

Remarque Ce plan d’exécution particulier est lue de droite à gauche et de bas en haut.

StmtText                                                                                                                                 


--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Update tblQueuedEvents Set NotifyID = 2, ResynchDate = '5/7/2003 10:44:16'
where eventSID = 73023


|--Clustered Index
Update(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[ix_tblQueuedEvents ]),
SET:([tblQueuedEvents].[NotifyID]=[@1],
[tblQueuedEvents].[ResynchDate]=[Expr1004]))
|--Top(1)



|--Compute Scalar(DEFINE:([Expr1004]=Convert([@2])))



|--Index
Seek(OBJECT:([SOTS].[dbo].[tblQueuedEvents].[PK_tblQueuedEvents]),
SEEK:([tblQueuedEvents].[EventSID]=[@3])

Recommander une solution pour résoudre le blocage

Notez que l’instruction de mise à jour effectue un « index ordonné en clusters mise à jour » dans l’index ordonné en clusters. Par conséquent, l’index non ordonné en clusters et l’index ordonné en clusters doivent tous deux être mis à jour. L’index ordonné en clusters est ix_tblQueuedEvents et que l’index non ordonné en clusters est PK_tblQueuedEvents. Pour effectuer les mises à jour, l’instruction UPDATE doit obtenir des verrous exclusifs sur les deux index. Ces deux index est les index qui sont impliqués dans le blocage. À partir de la révision des traces du Générateur de profils SQL, n’était pas visible des requêtes permettant la ResynchDate dans la clause WHERE. Toutes les instructions étaient très spécifiques et le EventSID utilisés dans la clause WHERE. Un meilleur choix d’un index ordonné en clusters serait EventSID. Avec ces informations et une discussion avec le client, nous avons constaté que l’index ResynchDate était ancien et qu’il n’était pas nécessaire. Il est recommandé que le client déposer l’index ix_tblQueuedEvents sur ResynchDate et que PK_tblQueuedEvent un index ordonné en clusters. La situation d’interblocage est résolu.

Il s’agit d’un seul exemple d’un cas de blocage qui implique des verrous. Des blocages peuvent également impliquent le parallélisme et impliquent des threads. Ils peuvent impliquer une, deux, trois, ou plus SPID et ressources. Tout cas de blocage, vous devez obtenir – T1204 résultat des paramètres de démarrage et la trace du Générateur de profils SQL pour résoudre le blocage à identifier et à résoudre les problèmes. Votre situation de blocage dispose de ressources et des processus différents. Par conséquent, les solutions varient d’un cas à l’autre. Méthodes typiques que vous pouvez utiliser pour résoudre les interblocages sont les suivantes :
  • Ajout et suppression d’index.
  • Ajout d’indicateurs d’index.
  • Modification de l’application d’accéder aux ressources dans un modèle similaire.
  • Suppression de l’activité de la transaction comme déclencheurs. Par défaut, les déclencheurs sont transactionnelles.
  • Conserver les transactions les plus courtes possible.
Propriétés

ID d'article : 832524 - Dernière mise à jour : 27 janv. 2017 - Révision : 1

Microsoft SQL Server 2000 Standard, Windows Server 2008 Datacenter, Windows Server 2008 Enterprise, Windows Server 2008 Standard, Windows Server 2008 R2 Enterprise, Windows Server 2008 R2 Datacenter, Windows Server 2008 R2 Standard, Windows Server 2008 R2 Foundation, Windows Server 2012 Datacenter, Windows Server 2012 Foundation, Windows Server 2012 Standard, Windows Server 2012 R2 Datacenter, Windows Server 2012 R2 Essentials, Windows Server 2012 R2 Standard, Windows Server 2012 R2 Foundation

Commentaires