Discussion :

[KeepItSimple]

Bonjour,

nous avons un sql server 2017 avec une activité assez importante (milliers d'update par heure).
De manière sporadique (quelques fois par jour), nous avons des transactions qui tombent en timeout à cause de lock refusé. Nous avons analysé l'application mais nous n'avons pas trouvé la raison.

De manière à investiguer, est-il possible en cas de lock refusé d'enregistrer "quelque part" (fichier, table, event viewer, ... n'importe où) les instructions en cours qui font qu'un select/update ne peut être effectué ?
(au niveau appli on a un timeout sur select * from table where id in (<valueList>) ).

Merci.

[Boubou2020]

vous pouvez tracer les deadlock en activant le trace flag

DBCC TRACEON(1204)
DBCC TRACEON(1222)

l'information sera stocké dans l'errorlog

Code :

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

EXEC master.dbo.xp_readerrorlog 0, 1, N'deadlock victim=process', NULL, NULL, NULL, N'desc'

[mikedavem]

Est-ce que tu peux définir un lock refusé ? Est-ce qu'il s'agirait de locks en timeout? De deadlocks ?
Comment tu l'identifies à ton niveau pour le moment ?

Si deadlock comme stipulé par Boubou2020 je ne conseille pas d'activer les trace flags car d'une part leur exploitation est compliquée dans le journal des erreurs SQL et d'autre part ils sont par défaut capturés dans la session d'événement étendue system_health avec SQL Server 2017.

++

[SQLpro]

En sus cette méthode est considérée comme obsolète.... Pour les deadlock utilisez le profiler SQL.

Pour le timemout il faut faire une session d'événement étendu en capturant les erreurs :

• 8645
• 17197
• 17830

A +

[KeepItSimple]

@mikedavem:
"Est-ce que tu peux définir un lock refusé ? Est-ce qu'il s'agirait de locks en timeout? De deadlocks ?
Comment tu l'identifies à ton niveau pour le moment ?"
=> au niveau du serveur applicatif

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

statement:

Code :

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

1
2
3
4
5
6
7
8
SELECT * FROM stubbe.UniqueIdentifier (NOLOCK) WHERE Id IN 
(
'QCBDRa1AnqQQFHx00000042403241n9EAA021012007',
'QCBDRa1AnqqVeub00000042403241n9EAA021012007',
'QCBDRa1AnqQW0i400000042403241n9EAA021012007',
'QCBDRa1AnqqX7WN00000042403241n9EAA021012007',
'QCBDRa1AnQQy1Re00000042403241n9EAA021012007'
)

Pour le profiler sql , ca veut dire que je dois le laisser capturer des trucs pendant des heures et espérer que ça arrive assez vite.
Ma question n'est pas ça: est-il possible via une configuration/un trigger/... de générer une trace quand une instruction sql tel update/select/delete renvoie un timeout, trace comprenant la raison du timeout (e.g. l'id de la seconde session/instruction qui a causé le lock), de manière à avoir de quoi investiguer sans devoir lancer à posteriori un profiler et espérer qu'exactement le même souci se produise endéans un temps raisonnable.

[KeepItSimple]

Est-il possible que sql server fasse des locks au niveau de la page et pas au niveau de la ligne? Cela pourrait expliquer nos soucis sporadiques.

[hmira]

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Bonjour,

Il est important de noter qu’il ne s’agit pas d’une erreur SQL Server à proprement dit, mais plutôt d’une erreur du client, et ce, même si les causes réelles peuvent provenir de processus bloqués SQL Server, mais pas forcément (à ne pas confondre avec les Deadlock ! dont le déclenchement est généralement immédiat !).

La requête (ou plutôt le client) a attendu un certain temps (le temps correspondant au Timeout défini au niveau du client), une fois ce temps d’attente (timeout) dépassé, le client génère une erreur.

A+

[SQLpro]

...
(au niveau appli on a un timeout sur select * from table where id in (<valueList>) ).

Utiliser l'étoile dans le SELECT :

Code :

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

SELECT *

est la manière la plus simple de plomber les performances d'un SGBDR et d'entretenir des time out....

En effet, le fait de demander systématiquement le retour dans le dataset de toutes les colonnes de la table, conduit a utiliser rarement un index, par le simple fait qu'un index ne contient pas toutes les données de la table.
Or ne pas utiliser l'index revient a balayer toutes les lignes de la table, ce qui prend du temps.
Cela prend d'autant plus de temps, qu'il faut aussi transmettre ces données du serveur SQL à l'application... Rassurez vous du côté du serveur SQL, cela va très vite à envoyer. Il ne s'agit que de lire des octets et les balancer sous forme de trames vers l'application. Mais du côté applicatif c'est beaucoup beaucoup plus lent... Tout simplement parce que l'application doit construire dynamiquement des objets spécifiques à l'affichage ou au traitement des données (l'information est-elle est un nombre ?, un littéral ?, une date ? Dans quelle culture dois-je afficher la date ? Le nombre ?...). Ceci peut d'ailleurs être vérifié en demandant à SQL Server quels sont ses attentes (du temps gaspillé) et vous verrez immédiatement pour ce type de requêtes un nombre incalculable de ASYNC_NETWORK_IO...

Avoir une restriction (filtre) avec un IN :

Code :

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

where id in (<valueList>)

Est le plus sur moyen de ne jamais passer par un index et donc de faire un scan de table. L'opérateur IN est en fait un une série de OR (vous pouvez voir dans le plan de requête que le IN n'existe pas et est systématiquement remplacé par une série de OR...). Malheureusement le OR (où logique) n'est pas "cherchable" (les anglais disent "sargable"), c'est à dire que l'on ne peut pas utiliser un index pour cherches de multiples valeurs. Mais l'optimiseur de SQL Server est assez malin pour contourner la difficulté et récrire votre requête à la volée pour la rendre cherchable.

Prenons un exemple concret... Vous cherchez les valeurs 'A' ou 'B' sans la colonne CODE. Vous écrivez la requête suivante :

Code :

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

SELECT * FROM MaTable WHERE CODE IN ('A', 'B')

Requête non cherchable, qui est traduit en :

Code :

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

1
2
3
SELECT * FROM MaTable WHERE CODE = 'A'
UNION
SELECT * FROM MaTable WHERE CODE = 'B'

Ces deux requêtes avant et après l'opérateur UNION deviennent cherchable (on peut utiliser un index), mais l'union a un coût. Mettons que la recherche avec A ou B par rapport à une recherche unitaire de type recherche A coute deux fois plus cher (logique puisque deux recherches) avec un surcout du même ordre pour l'opération UNION. Nous avons donc un coup de 3 fois supérieur au cout de la requête unitaire.

Que se passe t-il avec :

• 3 valeurs dans le IN ? 3 requêtes et 2 union => cout de 5
• Avec 4 ? cout de 7
• Avec 10 ? cout de 19

Au bout d'un moment, l'optimiseur va trouver, de façon logique et purement mathématique, qu'avec un nombre élevé de valeurs dans le IN, cette stratégie de découpe et d'union, coutera plus cher qu'un simple et unique balayage de la table...

Tout ceci pour vous expliquer que l'un des facteurs les plus important pour obtenir de bonnes performances est d'appliquer à la lettre les bonnes pratiques, parmi lesquelles :

• ne jamais faire de SELECT *, mais mettre après le mot clé SELECT uniquement les colonnes dont on a réellement besoin
• écrire des prédicats cherchables (clauses WHERE, HAVING, JOIN/ON) en évitant le OR, le IN, le LIKE %%...

Au passage transformer votre liste de valeurs en une jointure est une chose aisée en utilisant une jointure qui passe par la création d'une table temporaire indexée... Une jointure sur index étant incommensurablement plus rapide qu'une série en OR (fallait la faire celle là !)

A +

PS, je suis en train d'écrire un article pour faire comprendre la notion de time-out et ses pièges...

[KeepItSimple]

Désolé Mr sqlpro, je ne suis pas d'accord et je peux le prouver.

1. SELECT * ou select col1, col2, ... (toutes les colonnes) sont équivalents.
"En effet, le fait de demander systématiquement le retour dans le dataset de toutes les colonnes de la table, conduit a utiliser rarement un index"
=> Faux... et heureusement, il faudrait systématiquement indexer toutes les colonnes, même celles sur lesquelles on ne cherche pas: ce serait catastrophique!
Seules les colonnes du where doivent être indexées.

2. where id in (<valueList>) => Est le plus sur moyen de ne jamais passer par un index

Ce que je peux dire c'est que si je fais sur une table énorme (plusieurs Gb):

SELECT * FROM Table WHERE col in (... 50 valeurs ...)

ou
SELECT * FROM Table WHERE col = ...
UNION ALL
SELECT * FROM Table WHERE col = ...
...
ou

SELECT * FROM Table WHERE COL =...
GO
SELECT * FROM Table WHERE COL =...
...

Ca fonctionne très vite si col est indexé, dans tous les cas.
Si j'enlève l'index par contre c'est lent pour tous les queries.
Si l'index n'était pas utilisé pour le IN ou pour le OR chacun des query avec IN ou OR prendrait plusieurs minutes, et ce n'est pas le cas.

De plus, cette requête avec des IN est exécutée plus d'une fois par minute, de 8h00 à 18h00... et ne plante que trois fois par jour. Si les indexes ne fonctionnaient pas l'application fonctionnerait 3 fois par jour et serait plantée 99.99% du temps. Ici c'est l'inverse, l'appli ne plante "que" 3 fois par jour avec des dizaines d'updates à la seconde.

Le souci que nous avons n'est pas un souci de performances d'index, mais selon moi de deux sessions qui essaient de mettre à jour les mêmes blocs (mais selon les développeurs pas les mêmes lignes... mais ils peuvent se tromper).

[hmira]

Désolé Mr sqlpro, je ne suis pas d'accord et je peux le prouver.
SELECT * ou select col1, col2, ... (toutes les colonnes) sont équivalents.
"En effet, le fait de demander systématiquement le retour dans le dataset de toutes les colonnes de la table, conduit a utiliser rarement un index"
=> Faux... et heureusement, il faudrait systématiquement indexer toutes les colonnes, même celles sur lesquelles on ne cherche pas: ce serait catastrophique!
Seules les colonnes du where doivent être indexées.

Bonjour KeepItSimple,

Sans vouloir vous offusquer, je pense que vous n’avez pas bien compris la remarque et la réponse de SQLPro concernant le SELECT * et l'utilisation ou non des index, et j’ai le regret de vous dire que votre réplique est à côté de la plaque !

Le SELECT * est fortement déconseillé par le fait que d’une part généralement on n’a pas besoin de récupérer toutes les colonnes d’une table, d’autre part, l’index, à moins qu’il soit organisé en Cluster, ne couvre pas toutes les colonnes de la table, aussi le coût nécessaire pour récupérer toutes les autres colonnes (Opération Key Lookups) de la table peut devenir tellement prohibitif et finir par inciter l’optimiseur à ne pas utiliser l’index !

A+

[KeepItSimple]

@ hmira:
Select id, col1, col2 from table where id=5


Utilise l'indexe (sur id=5) pour trouver la ligne puis va chercher la ligne dans le bloc données sans lire toute la table.
Il ne faut pas un index sur (id, col1, col2) pour que ça fonctionne. Et si la table ne comporte que (id, col1, col2) c'est équivalent à un select *.
Si l'appli a besoin de toutes les colonnes, le select * est justifié. Le fait de rapatrier une, deux ou 3 colonnes ne change rien à partir du moment ou au moins une des colonnes est dans la table et pas dans l'index.

[hmira]

Select id, col1, col2 from table where id=5

L’exemple ci-dessus que tu donnes est un exemple simple, trivial où l’index de la clé primaire (organisé en cluster) sera utilisé.

Les scénarios dont on vous parle, sont tout autre… aux travers desquels nous essayons de distiller quelques règles de bonnes pratiques pour celles et ceux qui veulent bien les suivre .

Plus généralement seule une analyse du plan d’exécution de la requête vous donnera des informations précises sur les index réellement utilisés.

Concernant l’erreur évoquée initialement, objet principal de votre post, comme je l’ai déjà mentionné ci-haut, celle-ci n’est pas émise directement par le Moteur SQL Server, mais, émises par le client.
Néanmoins, si vous soupçonnez que ces erreurs sont les conséquences de blocages de processus SQL Server lui-même, ce qui est tout à fait plausible, vous pouvez toutefois spécifier le seuil, en secondes, à partir duquel des rapports de processus bloqués seront désormais générés par le moteur SQL Server, puis analyser ces rapports de bocages, analyser les contextes transactionnels des blocages etc.

A+

[SQLpro]

Désolé Mr sqlpro, je ne suis pas d'accord et je peux le prouver.

1. SELECT * ou select col1, col2, ... (toutes les colonnes) sont équivalents.

Vous n'avez visiblement pas lu ce que j'ai écrit. Relisez moi SVP avant d'écrire des âneries...

C'est bien la peine d'ecrire un article qui m'a demandé 1 heure pour donner une perle à un cochon !

"En effet, le fait de demander systématiquement le retour dans le dataset de toutes les colonnes de la table, conduit a utiliser rarement un index"
=> Faux... et heureusement,

Et là en plus vous dite n'importe quoi !

il faudrait systématiquement indexer toutes les colonnes, même celles sur lesquelles on ne cherche pas: ce serait catastrophique!
Seules les colonnes du where doivent être indexées.

Une ânerie d'une haute stupidité de plus... L'indexation commence par la jointure qyui n'a rien à voir avec la clause WHERE qui fait une restriction. Sans doute avez vous séché les cours ? De plus les clauses GROUP BY et ORDER BY ont besoin d'un index !
https://sqlpro.developpez.com/cours/quoi-indexer/
Ayez au moins la pudeur de vous renseigner pour ne pas rester dans votre ignorance suffisante !



Il existe quand même une solution pour indexer TOUTES les colonnes d'une table avec un seul index. Je ne vous la donnerait pas, car c'est donner des perles à un cochon !

2. where id in (<valueList>) => Est le plus sur moyen de ne jamais passer par un index

Ce que je peux dire c'est que si je fais sur une table énorme (plusieurs Gb):

SELECT * FROM Table WHERE col in (... 50 valeurs ...)

ou
SELECT * FROM Table WHERE col = ...
UNION ALL
SELECT * FROM Table WHERE col = ...
...
ou

SELECT * FROM Table WHERE COL =...
GO
SELECT * FROM Table WHERE COL =...
...

Ca fonctionne très vite si col est indexé, dans tous les cas.

Faites de tests au lieu de dire n'importe quoi !

Si j'enlève l'index par contre c'est lent pour tous les queries.
Si l'index n'était pas utilisé pour le IN ou pour le OR chacun des query avec IN ou OR prendrait plusieurs minutes, et ce n'est pas le cas.

De plus, cette requête avec des IN est exécutée plus d'une fois par minute, de 8h00 à 18h00... et ne plante que trois fois par jour. Si les indexes ne fonctionnaient pas l'application fonctionnerait 3 fois par jour et serait plantée 99.99% du temps. Ici c'est l'inverse, l'appli ne plante "que" 3 fois par jour avec des dizaines d'updates à la seconde.

Le souci que nous avons n'est pas un souci de performances d'index, mais selon moi de deux sessions qui essaient de mettre à jour les mêmes blocs (mais selon les développeurs pas les mêmes lignes... mais ils peuvent se tromper).

J'arrête de vous aider car je suis aux limites du supportable. Je n'ai pas envi de perdre mon temps avec quelqu'un qui se réfugie sciemment dans l'ignardise !

A jamais !