Discussion :

[dari68]

Bonjour,

Je cherche à récupérer l'historique des deadlocks sur une base.

J'ai essayé:
https://blog.developpez.com/elsuket/p12987/moteur-de-base-de-donnees-sql-server/recuperer-les-graphes-des-deadlocks-retrospectivement-les-requetes-adequates-aux-versions

Il y aurait une vue système à partir de la quelle il y a ces infos ...? j'ai regardé dans ERRORLOG ras...

Merci.

A+

[SQLpro]

Quelle version de SQL Server ?

A +

[SQLpro]

Essaye :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
WITH T AS
(
SELECT path FROM sys.traces WHERE is_default = 1
)
SELECT p.*
FROM   T
       CROSS APPLY sys.fn_trace_gettable(path, NULL) AS p
WHERE  p.EventClass IN (SELECT trace_event_id 
                        FROM   sys.trace_events 
                        WHERE  name LIKE '%deadlock%' COLLATE French_CI_AI)

A +

[dari68]

Merci bcp.

Le serveur a été redémarré cette nuit, plus de trace malheureusement.

Bonne journée.

A+

[Boubou2020]

mais non

avec le redémarrage du serveur tu perd seulement la position du fichier de trace par défaut

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

from sys.traces where id = 1

en totalité il y a 5 fichiers de trace

il faut juste adapter ta requête avec la position de ta fichier de trace

[SQLpro]

Les traces étant enregistrées dans des fichiers, vous pouvez accéder aux données les plus anciennes….
La requête que je vous ais donné ne scrute que le fichier actif.
Pour voir tous les fichiers, vous pouvez utiliser celle-ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
WITH T AS
(
SELECT SUBSTRING(path, 1, LEN(path) - CHARINDEX('_', REVERSE(path)) ) + '.trc' AS path 
FROM   sys.traces 
WHERE  is_default = 1
)
SELECT p.*
FROM   T
       CROSS APPLY sys.fn_trace_gettable(path, NULL) AS p
WHERE  p.EventClass IN (SELECT trace_event_id 
                        FROM   sys.trace_events 
                        WHERE  name LIKE '%deadlock%' COLLATE French_CI_AI);

Plus d'information dans mon bouquin :


A +

[dari68]

Merci bcp,

j'ai rien dans ma trace sur les verrous.

En parlant deadlock, est ce qu'il y a une limite ds SQL ....? je n'ai pas compris pourquoi ma base était figée ...?

Merci.

A+

[SQLpro]

Qu'entendez vous par figée ?

Il y a une limite en rétention effectivement.

Néanmoins, vous pouvez tracer ces deadlocks en utilisant le profiler SQL. C'est très facile. En 3 clics. Notamment recueillir le DEADLOCK graph…

Il faut ensuite le dépouiller.

C'est pourquoi je vous invite à lire l'article que j'ai publié à ce sujet :

https://blog.developpez.com/sqlpro/p...ck-du-profiler

A +

[dari68]

Bonsoir,

Merci, base figée => utilisateurs impossible de se connecter après c'est restart.

A+

[SQLpro]

Bonsoir,

Merci, base figée => utilisateurs impossible de se connecter après c'est restart.

A+

Si vous constatez cela c'est sans doute qu'il y a des blocages. Les blocages sont le fait que les transactions (lectures, écritures…) qui doivent poser des verrous, se gênent les unes les autres, comme le font les feux de circulation, et cela devient gênant s'il y a des embouteillages, ce qu'en terme e BD nous appelons de la contention.

Il y a de multiples facteurs au sujet de la contention. Les deux branches factorielles principales sont :

• un mauvais réglage de l'instance SQL Server
• une mauvaise modélisation de la base de données

Redémarrer une instance SQL Server est la chose à ne jamais faire !...

• Vous videz le cache qui permet d'obtenir de bonnes performances. Les données sont mises en cache, comme les plans d'exécution. En cas de vidage, cela oblige à lire les données du disque (1000 à 10000 fois moins rapide) et recalculer les plans d'exécution des requêtes...
• Vous purgez les statistiques d'exploitation qui permettent de diagnostiquer ce qui s'est passé et d'améliorer la situation. Un peu comme si dans l'ambulance du SAMU, le médecin jetait par la fenêtre toutes les informations qu'il vient de recueillir sur l'état de santé du patient qu'il vient de consulter… !

En cas de blocage, il faut diagnostiquer, débloquer que ce qui est nécessaire de débloquer et corriger certaines choses afin que les blocages ne surviennent plus.

1 - Configuration de l'instance

SQL Server a besoin d'un serveur dédié. Cela signifie qu'aucune autre application ne doit tourner sur ce serveur, pas même un antivirus !

La mémoire utilisée par SQL Server doit être limitée à RAM physique - 2, -3 ou -4 Go selon la quantité de RAM de la machine.

En cas de VM, il est impératif de fixer les cœurs et fixer la RAM. Pas de "balooning" notamment. Pas de cœurs flottants…

Il faut aussi régler le parallélisme à deux niveaux :

• Limiter le nombre de threads en // à n/2 - 1 voir n/4 - 1, suivant la valeur de n (règle 1 pour n jusqu'à 8), n étant le nombre de cœurs.
• Limiter le seuil de déclenchement du //. par défaut fixé à 5, ce seuil est trop bas. Placez le entre 25 et 100.

2 - modélisation de la base

Vérifiez que l'ensemble des formes normales a bien été appliqué.

En particulier :

• Toute table doit avoir une clef primaire
• Toutes les clefs primaires devraient être des auto incréments sur un type entier
• Toutes les clefs primaires de type auto incrément devraient être des index clustered.
• Le nombre de colonnes d'une table ne devrait pas dépasser 20
• Toutes les clefs étrangères devraient avoir une contrainte FOREIGN KEY
• Certaines colonnes (pour ne pas dire la plupart) devraient avoir une contrainte CHECK de domaine
• Il faut poser les index adéquats.

Les règles basiques de la modélisation des bases de données étant les suivantes :

1. toutes les informations doivent être atomiques
2. pas de redondance
3. pas de NULL
4. La modification d'une information ne doit pas entrainer la mise à jour de plus d'une ligne dans une seule table

Pour commencer, pouvez vous renvoyer le résultat de ces trois commandes (bien présentées SVP !) :
SELECT @@VERSION;
EXEC xp_msver;
EXEC sp_configure;

A +

[dari68]

Bonsoir,

Merci bcp de votre réponse, le retour des 2 commandes en pj.

C'est sur une instance 2016 sp1 en édition standard sur OS 2012 standard R2.

MERCI.

A+

[SQLpro]

relevez le seuil du // à 33 et mettez une limite de 2 cœurs au //, activez l'optimisation des requêtes ad hoc

Après voir le la structure de la base….

A +