Discussion :

[Archi89]

Bonjour,

Actuellement je m'exerce sur SQL server avec les tables systèmes. Alors,... Comment dire? Il y en a énormément.
Je suis un peu perdu.
Nous avons une erreur avec une DUPLICATE KEY.
Le problème est assez simple. Lorsque l’exception est levée, nous voulons connaître les Transaction_Id des requêtes qui ont amené à cette exception.

Je pense que ces requêtes de suffisent pas.

Code :

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

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
 
 
// Connaître les dernières transactions.
 
SELECT		QS.creation_time Creation, QS.last_execution_time LastExec,	QS.plan_generation_num NbComp, QS.execution_count NbExec, 
			QS.total_worker_time/QS.execution_count AvgCPUTime, OBJECT_NAME(SQL.objectid) Procedures,
					SUBSTRING
					(
						SQL.text,
						QS.statement_start_offset / 2 + 1,
						(
							CASE
								WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), SQL.text)) * 2 
								ELSE QS.statement_end_offset 
							END - QS.statement_start_offset
						) / 2 + 1
					) AS Instruction
FROM sys.dm_exec_query_stats QS 
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) SQL
Order by Creation desc;
 
// En attente
 
SELECT
    s.host_name, 
    t.text, 
    r.start_time, 
    r.status, 
    r.total_elapsed_time, 
    r.logical_reads, 
    r.granted_query_memory 
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

Le problème est que je n'ai pas vraiment la table dans laquelle le INSERT est fait (sauf si je bricole la colonne Instruction, mais compliqué à faire)...

De plus, on a parfois un soucis de TIMEOUT (j'ai vu qu'avec l'outil SQL_Profiler on pouvait avoir plus de détails).
Mais peut-on affiner cette exception encore une fois avec une requête (savoir si c'est un DEADLOCK par ex).

Désolé, cela semble être un pb de débutant mais j'apprends au fur et à mesure et le monde des tables systèmes est très vaste... Si vous avez de la littérature assez concise dessus, je suis preneur. (J'ai récemment acheté le livre SQL Server 2014, mais manque de temps en ce moment...).

Je vous remercie par avance (encore...).
Julien

[SQLpro]

Actuellement je m'exerce sur SQL server avec les tables systèmes.

Il n'y a aucun accès aux tables systèmes dans SQL Server. Vous ne pouvez accéder qu'à des vues. Soit des vues de métadonnées, soit des DMV (fonction identifiées par sys.dm_...).

Le problème est assez simple. Lorsque l’exception est levée, nous voulons connaître les Transaction_Id des requêtes qui ont amené à cette exception.

Dans ce cas il faut gérer une exception à l'intérieur du code fautif et remonter dans le code à la requête fautive. Mais en dehors du scope de code c'est impossible.
sys.dm_exec_query_stats fournit un échantillon des requêtes mais ne garantie nullement que tout y soit ! Vous ne pouvez pas vous fier à cette fonction pour faire cela.


Le moyen le plus simple est de nommer correctement vos contraintes, car dans le message d'erreur figure le nom de la contrainte, et par ce nom vous pouvez remonter à la table.

En adoptant une norme de nommage vous pourrez retrouver cela facilement.
1) toutes les tables ont un trigramme qui figure en suffixe du nom
2) toute contrainte est identifiée par un nom comportant :
a) le digramme PK, UK, FK, CK pour indiquer le type de contrainte, respectivement Primary Key, Unique Key, Foreign Key, ChecK
b) un blanc souligné puis de trigramme de la table puis un nouveau blanc souligne
c) pour les contraintes FK, le trigramme de la table de référence puis un blanc souligné
d) d'autres informations si nécessaire
Le tout en MAJUSCULE.

À partir de là, n'importe quel message d'erreur faisant référence à une contrainte est facile à identifier (il suffit de retrouver le motif LIKE '[PUFC]K?_[A-Z0-9][A-Z0-9][A-Z0-9]%' ESCAPE '?') extraire le trigramme et remonter au nom de la table.

A +

[Archi89]

Rapide... Merci.

Il n'y a aucun accès aux tables systèmes dans SQL Server. Vous ne pouvez accéder qu'à des vues. Soit des vues de métadonnées, soit des DMV (fonction identifiées par sys.dm_...).

D'accord, c'était un raccourci de ma part un peu bête. J'aime bien vos "recadrages".

À partir de là, n'importe quel message d'erreur faisant référence à une contrainte est facile à identifier (il suffit de retrouver le motif LIKE '[PUFC]K?_[A-Z0-9][A-Z0-9][A-Z0-9]%' ESCAPE '?') extraire le trigramme et remonter au nom de la table.
A +

Donc ça, on l'a bien fait (ouf). Nous avons les bons nommages.
Maintenant peut-on connaître toutes les transactions effectuées sur une table précise. Faut reconnaître que décortiquer text de la vue sys.dm_exec_sql_text n'est pas forcément aisé.

L'idée, je récupère toutes les transaction_id des commandes INSERT dans la table X. Est-ce possible avec sys.dm_exec_requests, sys.dm_exec_query_stats et sys.dm_exec_sql_text (avec un CROSS APPLY)?

Finalement je crois que ce n'est pas si simple que ça.

Merci

[SQLpro]

Maintenant peut on connaître toutes les transactions effectués sur une table précise.

Non... sauf à utiliser le profiler en permanence. sys.dm_exec_query_stats ne collecte qu'un échantillon basé sur le cache. En cas de pression mémoire, les objets les moins utilisés dégagent les premiers. Si c'est la première fois que votre requête est jouée, il y a toutes les chances qu'elle dégage...

Faut reconnaître que décortiquer text de la vue sys.dm_exec_sql_text n'est pas forcément aisé.

Vous pouvez créer une fonction et la placer dans master ou msdb....

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE FUNCTION F_EXTRACT_QUERY(@TXT NVARCHAR(max), @start_offset INT, @end_offset INT)
RETURNS NVARCHAR(max)
AS
BEGIN
   RETURN SUBSTRING(@TXT, 
                    (@start_offset / 2) + 1,   
                    ((CASE @end_offset
                         WHEN -1 THEN DATALENGTH(@TXT)  
                         ELSE @end_offset
                      END - @start_offset) / 2) + 1);
END;

L'idée, je récupère toutes les transaction_id des commandes INSERT dans la table X. Est-ce possible avec sys.dm_exec_requests, sys.dm_exec_query_stats et sys.dm_exec_sql_text (avec un CROSS APPLY)?

Non, car la requête sera déjà passée puisque vous avez levé une exception...

Finalement je crois que ce n'est pas si simple que ça.

oui !

A +

[Michel.Priori]

Une alerte sur le numéro d'erreur permettrait de lancer le script juste à temps non ?

[SQLpro]

Une alerte sur le numéro d'erreur permettrait de lancer le script juste à temps non ?

Les alertes de l'Agent SQL ne sont levées sur les exceptions que dans deux cas :
1) une alerte de niveau 19 ou supérieure, or les alertes sur viol de contraintes sont de niveau inférieur.
2) si une exception est lancée avec journalisation (RAISERROR(...) WITH LOG) ce qui oblige à élever le niveau de privilège de tout utilisateur à sysadmin pour pouvoir journaliser....

Autrement dit c'est irréaliste !

A +