Discussion :

[Christophe Charron]

Bonsoir,

je viens de faire une première lecture de l'article http://sqlpro.developpez.com/sqlserv...isation/intro/.
Il me faut un peu de temps et j'y retournerai demain, après une première digestion.

Mais, en attendant, je demande néanmoins s'il est possible de mesurer l'impact des triggers. Et l'impact des vues. Et si la présence de cast et convert dans ces vues n'ajoute pas à la gourmandise en ressources ?

Je m'explique.
J'ai une base de donnée dessinée en 1996. Autant dire, créée à la truelle, pour ne pas employer une expression plus triviale. Je peux me permettre de le dire car c'était moi le coupable.
Bref, ce descriptif a un peu, mais très peu évolué depuis. Mais d'autres personnes ont eu à intervenir dans cette base et ont eu des besoins plus "actuels".
Donc ils ont fait des vues enrichies sur les tables et ensuite on fait des trigger sur ces vues : instead of insert et instead of update.
Le temps passant, les données s'accumulant ... les temps de réaction deviennent insupportables.

Je ne sais pas si ce sont les triggers, les vues ou autre chose mais "ça" devient de plus en plus lent et donc de moins en moins tolérable.
J'ai déjà créé tous les index proposés ...

Quelles autres pistes à explorer ?

D'avance, merci pour vos lumières.

[StringBuilder]

Bonsoir,

Ca ne répond pas à la question, mais vous pouvez jeter un oeil à l'article que j'ai publié (il doit y avoir un lien sous mon avatar si je ne m'abuse).
Il devrait vous aider à "remodéliser" votre base, notamment les parties les plus tordues, tout en évitant de devoir tout réécrire.

La présence de vues avec triggers me semble prouver que vous avez déjà entrepris plus ou moins le travail. Peut-être qu'en basant vos vues sur un modèle propre, vous pourrez simplifier les requêtes, triggers, et alléger les temps de traitement.

[elsuket]

Bonjour,

J'ai déjà créé tous les index proposés ...

J'espère que vous l'avez fait avec parcimonie, parce que l'optimiseur en suggère, mais ils ne sont pas toujours justifiés, et la définition proposée n'est pas toujours parfaite non plus.
Vous pouvez juger de l'utilité d'un index après sa création à l'aide de la vue de gestion dynamique sys.dm_db_index_usage_stats.
Sachez néanmoins que les statistiques exposées par cette vue sont réinitialisées à chaque démarrage de l'instance (typiquement sur application des patchs Microsoft).

Il me semble que le mieux est que vous réalisiez un audit de performance, soit à l'aide de SQL Trace / SQL Profiler ou des événements étendus. Vous pouvez ensuite analyser les requêtes les plus lentes par rapport à leur fréquence d'exécution, leur durée, le temps CPU qu'elles consomment et le nombre d'IOs qu'elles produisent. Si vous optez pour SQL Profiler, sachez que l'outil ClearTrace, qui est proposé gratuitement, vous permet de calculer les agrégats sur les métriques citées auparavant, et de les stocker dans une petite base de données.

A l'aide des requêtes que vous aurez capturées, vous pouvez étudier le plan de requête, et de là voir ce qui cause le problème de performance.
Sur ce sujet, je vous conseille la lecture de l'ouvrage de Grant Fritchey, lui aussi proposé gratuitement.
Par ailleurs, à la ré-exécution des requêtes capturées, la sortie de l'option de session SET STATISTICS IO, TIME ON est grandement utile.
Le plan contient d'ailleurs celui de la requête elle-même, plus celui de l'exécution du trigger.

Vous pouvez retrouver les fonctionnalités d'audit de performances et bien d'autres fonctionnalités détaillées dans le livre référencé dans ma signature.

Bonne lecture et bon audit. N'hésitez pas à nous faire part de vos trouvailles.

[Christophe LAPORTE]

Bonjour

Code :

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

1
2
SELECT *
FROM sys.dm_exec_trigger_stats

vous donnera déjà une première idée des ressources consommées sur la prod.

Pour les vues, c'est plus compliqué à mesurer ... ou plus simple si l'on part du prnicipe qu'uen vie, sauf à être indexée, ne vous apporte aucun gain de performance. Par contre imbriquer des vues va vous en faire perdre.
Idem pour uen CTE,qui ne reste rien d'autre qu'une sorte de vue temporaire.
Pour le visualiser, exécutez simplement ce buot de code avant et après l'utilisation d'une CTE, vous autre un +1

Code :

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

1
2
3
4
5
DBCC TRACEON(3604)
 
SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = N'view reference';

cdlt

[SQLpro]

SELECT *
FROM sys.dm_exec_trigger_stats

Attention, valable à partir de la version 2014....

A +

[mikedavem]

Si beaucoup de triggers, je commencerais comme Christophe simplement avec la DMV sys.dm_exec_trigger_stats pour commencer mon analyse.
Sauf erreur de ma part cette DMV est disponible depuis 2008 R2.

Après effectivement un peu de profiler ou éventuellement d'événements étendues pour aller plus loin sera nécessaire.
J'aime beaucoup les XE (version 2012 ou +) dans ce cas car on arrive facilement à récupérer le chemin d'exécution des différents batchs / procédures dans un trigger et leurs statistiques

++

[Christophe LAPORTE]

Attention, valable à partir de la version 2014....

A +

Nope, pésente au moins depuis la 2008R2 RTM.

[SQLpro]

Nope, pésente au moins depuis la 2008R2 RTM.

Ha oui, je confond avec sys.dm_exec_function_stats qui est dispo à partir de 2016....

A +

[elsuket]

Merci Christophe, je ne connaissais pas cette vue.
Par contre je peux l'interroger sur une instance 2008 R1 SP4. Aurait-elle été ajoutée par l'un des Service Pack ?

@++

[Christophe LAPORTE]

J'ai souvenir de l'avoir utilisée sur un 2005 (SP4). Il faudrait vérifier sur une RTM.
Donc OK pour 2008.

[SQLpro]

...

Code :

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

1
2
3
4
5
DBCC TRACEON(3604)
 
SELECT *
FROM sys.dm_exec_query_optimizer_info
WHERE counter = N'view reference';

cdlt

À quoi te sert le DBCC TRACEON ????

A +

[elsuket]

Je viens d'interroger une instance SQL Server 2005 SP4 (9.00.5000.00) et j'obtiens le message suivant :

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_exec_trigger_stats'.

Ce n'est pas grave, c'est une version qui n'est plus supportée

@++

[Christophe LAPORTE]

@Nicolas : Pour le support sur SQL 2005, tu parlais de trigger_stats ou bien de optimizer_info ? J'avais opté pour la seconde dans ma réponse.
@Fred : Oups, oublié de supprimer cette commande de la démo dans laquelle j'ai retrouvé la RQ. Apologize.

[elsuket]

Ha je suis parti sur sys.dm_exec_trigger_stats, je n'ai pas essayé sys.dm_exec_query_optimizer_info.
J'essaie et je te dis

@++

[elsuket]

Effectivement

sys.dm_exec_query_optimizer_info

existe sous SQL Server 2005 SP4

@++

[janlouk]

Code :

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

1
2
SELECT *
FROM sys.dm_exec_trigger_stats

Bonjour Christophe,

Y aurait-il un moyen d'avoir en plus le nom du trigger et ou il se trouve ?

Je l'ai exécuté sur une instance et j'obtiens 39 lignes. Je vois l'ID de la DB et l'object_id, mais comment pourrais-je avoir le nom de la DB, la table où il se trouve et idéalement son nom ?

Merci

[Boubou2020]

tu peut récupérer le nom du database a travers sys.databases ainsi que le non du trigger a travers sys.triggers

Code :

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

1
2
3
4
5
6
7
select TRG.name as trigger_name,SD.name as database_name, *
from sys.dm_exec_trigger_stats AS ETS
     INNER JOIN
     sys.triggers AS TRG
         ON ETS.object_id = TRG.object_id
		 inner join sys.databases SD
		 on ETS.database_id=SD.database_id

[janlouk]

Parfait, merci. Avec les 2 ça me permet de savoir combien il y a de trigger sur toute l'instance.

[elsuket]

Bonjour,

La fonction OBJECT_NAME() pouvant prendre en paramètre le database_id en plus de l'object_id, on peut écrire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT	DB_NAME(database_id) AS database_name
	, OBJECT_NAME(object_id, database_id) AS object_name
	, type_desc
	, cached_time
	, last_execution_time
	, execution_count
	, (total_elapsed_time / execution_count) / 1000 AS avg_execution_duration_ms
	, (total_worker_time / execution_count) / 1000 AS avg_cpu_time_ms
	, (total_logical_reads / execution_count) AS avg_logical_reads
	, (total_physical_reads / execution_count) AS avg_physical_reads
	, (total_logical_writes / execution_count) AS avg_logical_writes
FROM	sys.dm_exec_trigger_stats

Ceci donne les statistiques d'exécution seulement pour les déclencheurs s'étant exécuté au moins une fois depuis le dernier démarrage du service SQL Server.
En effet, ces statistiques, comme celles des exécutions de requêtes (sys.dm_exec_query_stats et sys.dm_exec_procedure_stats) sont réinitialisées à chaque démarrage du service.
D'où encore fois le besoin d'un audit servant de base à une baseline, avec une capture régulière de ces statistiques, stockées de façon centrale

@++