SQL Server adore les verrous.
SQL Server utilise un verrouillage « pessimiste » par défaut- vos requêtes sont naturellement défensives. Cela peut entraîner des problèmes de blocage qui vous empêchent de faire évoluer SQL Server.
Cours de formation vidéo gratuit – avant de dépanner les blocages et les impasses, commencez avec Comment penser comme le moteur. Il s’agit d’une classe de 90 minutes au rythme rapide qui explique le fonctionnement des internes d’index clusterisés et non clusterisés, ce qui est essentiel pour comprendre comment l’indexation affecte le blocage. Il est gratuit, car nous pensons que chaque professionnel de SQL Server devrait commencer sa formation ici. C’est vraiment aussi important – de bonnes choses.
Outils pour diagnostiquer la contestation de verrou
« Ai-je des attentes de verrou en ce moment ? ». Utilisez sp_BlitzFirst® pour diagnostiquer si les attentes de verrouillage ont actuellement un impact sur votre serveur SQL.
« Quelles tables ont les pires problèmes de blocage ? ». Exécutez sp_BlitzIndex® et recherchez « Aggressive Index Disorder » pour identifier les tables qui ont eu de grandes quantités d’attentes de verrouillage (pour les tables et les index actuellement en mémoire).
« Comment donner du sens au rapport sur les processus bloqués ? » Lorsque vous capturez le rapport sur les processus bloqués de SQL Server (scripts ci-dessous), le Blocked Process Report Viewer de Michael J Swart est un outil gratuit qui vous aide à interpréter les résultats.
« Que faire si mon application n’attrape pas les erreurs de blocage ? » Amit Banerjee, de l’équipe CSS de Microsoft, vous aide à interroger la session System Health des événements étendus de SQL Server pour obtenir des détails (SQL Server 2008+).
Compteurs de performance liés aux blocages
Ces compteurs de performance peuvent être très utiles pour configurer une alerte de l’agent SQL Server basée sur les compteurs de perf afin que SQL Server puisse vous avertir lorsque le blocage dépasse les seuils que vous avez définis.
- Ces compteurs sont listés d’abord OBJECT, puis COUNTER
- SQL Server : Statistiques générales – Processus bloqués
- Serveur SQL : Locks – Temps d’attente des verrous (ms)
- SQL Server : Locks – Number of Deadlocks/sec
Découvrez d’autres compteurs de perforation dans SQL Server ici.
Lisez des articles sur le verrouillage, les niveaux d’isolement et les impasses
Lisez une introduction au verrouillage à deux phases et aux impasses de Jeremiah.
Apprenez comment vous pouvez tester les changements de code pour savoir quels verrous sont nécessaires de Kendra.
Jeremiah vous enseigne comment trouver et prévenir les deadlocks.
Apprenez comment mettre en œuvre le verrouillage optimiste dans SQL Server avec Kendra.
Lisez plus sur les niveaux d’isolation dans SQL Server – consultez notre page de liens et de références complètes.
Scripts pour utiliser le rapport de processus bloqué
Le « rapport de processus bloqué » existe depuis longtemps dans SQL Server- et c’est toujours un outil utile. Par défaut dans SQL Server, le détecteur de blocages se réveille toutes les 5 secondes pour vérifier si vos requêtes sont dans une « étreinte mortelle ». Vous pouvez activer une option qui demande à SQL Server d’utiliser cette ressource pour vérifier le blocage à long terme, et émettre un rapport.
Attention : Vous ne devez configurer le rapport sur les processus bloqués que pour des valeurs de cinq secondes ou plus. Microsoft prévient que si vous définissez cette valeur de 1 à 4, vous risquez de faire fonctionner le détecteur de blocage en continu et de tuer vos performances.
Pour utiliser le rapport de processus bloqué, vous devez configurer une trace qui recherche l’événement « Rapport de processus bloqué » sous « Erreurs et avertissements ». Nous recommandons d’utiliser une trace côté serveur – et bien sûr de ne l’exécuter que lorsque vous en avez besoin.
Vous voulez voir une démo de ceci ? Regardez-en une ici.
Les scripts dans cette section sont des exemples de façons courantes d’effectuer ces tâches- vous devez tester et réviser en conséquence pour votre environnement, et exécuter à vos propres risques.
Etape 1 : Activez le rapport de processus bloqué. Cela recherchera tout blocage prenant 20 secondes ou plus.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
–Vérifiez que vous n’avez pas de modifications en attente
SELECT *
FROM sys.configurations
WHERE value <> value_in_use ;
GO
exec sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE
GO
exec sp_configure ‘blocked process threshold (s)’, 20 ;
GO
RECONFIGURE
GO
|
Etape 2 : Configurez une trace pour capturer le rapport du processus bloqué. Exécutez-le comme une trace côté serveur.
- Il doit contenir un événement : Erreurs et avertissements -> Rapport de processus bloqué.
- Il a besoin de seulement deux colonnes : Texte, spid
Cet exemple de trace côté serveur s’exécutera pendant cinq minutes et s’arrêtera automatiquement. Vous devez modifier le chemin d’accès à un répertoire valide dans lequel SQL Server a la permission d’écrire.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
— Créé par : SQL Server 2012 Profiler
— Créer une file d’attente
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
———Ajouté une fonction ici :
set @DateTime = DATEADD(mi,5,getdate()) ; /* Exécution pendant cinq minutes */
set @maxfilesize = 5
— Veuillez remplacer le texte InsertFileNameHere, par un
— nom de fichier approprié préfixé par un chemin, par ex.g., c:\MonDossier\MonTrace. L’extension .trc
— sera ajoutée au nom de fichier automatiquement. Si vous écrivez depuis
— un serveur distant vers un lecteur local, veuillez utiliser le chemin UNC et assurez-vous que le serveur a
— un accès en écriture à votre partage réseau
———–Set my filename here :
exec @rc = sp_trace_create @TraceID output, 0, N’S:\Traces\BlockedProcessReportDemo’, @maxfilesize, @Datetime
if (@rc != 0) goto error
— Client side File and Table cannot be scripted
— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
— Définir les filtres
declare @intfilter int
declare @bigintfilter bigint
— Définir le statut de la trace pour commencer
exec sp_trace_setstatus @TraceID, 1
— afficher l’id de la trace pour des références futures
select TraceID=@TraceID
goto finish
erreur :
select ErrorCode=@rc
finish :
go
|
Étape 3 : regarder les traces en cours et obtenir l’id de la trace :
|
1
2
|
SELECT * from sys.traces ;
GO
|
Arrêter une trace, si nécessaire
|
1
2
3
4
5
|
–Plongez dans le traceid correct de la requête ci-dessus
EXEC sp_trace_setstatus @traceid =2, @status = 0 ; — arrêter la trace (en supposant que c’est l’ID de trace 2)
GO
EXEC sp_trace_setstatus @traceid =2, @status = 2 ; — fermer la trace (en supposant que c’est l’ID de trace 2)
GO
|
Étape 4 : Lire les données du rapport sur les processus bloqués.
Dans cet exemple, nous utilisons le visualisateur gratuit de rapports de processus bloqués de Michael J Swart.
|
1
2
3
|
exec dbo.sp_blocked_process_report_viewer
@Source=’S:\Traces\BlockedProcessReportDemo.trc’ ;
GO
|
Etape 5 : Nettoyer !
N’oubliez pas cette étape.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
–Vérifiez que votre trace a disparu
SELECT * from sys.traces ;
GO
–Désactivez le rapport de processus bloqué lorsque vous ne l’utilisez pas.
–Assurez-vous que vous n’avez pas de changements en attente
SELECT *
FROM sys.configurations
WHERE value <> value_in_use ;
GO
exec sp_configure ‘blocked process threshold (s)’, 0 ;
GO
RECONFIGURER
exec sp_configure ‘blocked process threshold (s)’ ;
GO
|
.
