Temps d'exécution moyen de requêtes

[Keitamax]

Bonjour à tous,
je voudrais pouvoir obtenir le temps moyen d'exécution des requêtes qu'une bdd SQL Server a reçues sur une certaine intervalle.
Quelqu'un saurait comment s'y prendre?

[iberserk]

Faites une trace avec le SQL profiler que vous programmez par exemple de 8h à 10 heures.

La trace est ensuite exportable en fichier de trace ou dans une table...

Pour accéder au SQL PROFILER allez dans les outils de SQL SERVER MANAGEMENT STUDIO...

Vous pouvez automatiser ces trace (tous les jours par exemple...)

[aieeeuuuuu]

Bonjour

Cette requête fournit tout un tas de statistiques sur les requêtes exécutées...

Je vous laisse faire le tri des informations qui vous intéressent :

Code SQL :

1
2
3
4
5
SELECT *
FROM sys.dm_exec_query_stats
OUTER apply sys.dm_exec_sql_text(sql_handle) AS  s2 
LEFT OUTER JOIN sys.objects s3  ON ( s2.objectid = s3.object_id ) 
LEFT OUTER JOIN sys.schemas sch ON(s3.schema_id = sch.schema_id)

[iberserk]

Attention toutefois, cette requête ne retourne que les informations liées aux données en cache...

[Keitamax]

Merci beaucoup pour votre aide. Je suis arrivé à obtenir ce que je voulais avec un Trace; reste plus qu'à l'automatiser.
Je vais également me pencher sur la requête de aieuu.
Mon but est en fait de trouver d'éventuelles sources de mauvaises performances d'une application qui interagit avec SQL Server, si vous avez en tête des paramètres particuliers qui pourraient être intéressants dans ce cadre ils sont bien sur bienvenus!

[aieeeuuuuu]

Envoyé par iberserk

Attention toutefois, cette requête ne retourne que les informations liées aux données en cache...

En effet, il n'est pas inutile de le préciser

Envoyé par Keitamax

Mon but est en fait de trouver d'éventuelles sources de mauvaises performances d'une application qui interagit avec SQL Server, si vous avez en tête des paramètres particuliers qui pourraient être intéressants dans ce cadre ils sont bien sur bienvenus!

Dans ce cas en effet, les temps moyens d’exécution des requêtes ne seront pas forcément les plus utiles. Vous pouvez par exemple avoir une "grosse requête" bien optimisée qui s’exécute en 30 secondes, un fois par jour, et une "petite requête" mal optimisée qui s’exécute en 100 ms 1000 fois par jour, mais qui pourrait être optimisée pour tourner en 10 ms...

Les I/O ainsi que de nombreux autres paramètres sont également à prendre en compte...

mais si je comprend bien le but de votre démarche, je pense que vous pouvez dans un premier temps vérifier si votre BDD dispose des indexes nécessaires. Je vous conseille de lire cet article d'ElSuket pour vérifier les indexes manquants.

Rencontrez vous des lenteurs actuellement, où est-ce que votre but actuel est plutôt de ne pas en rencontrer dans le futur ?

[Keitamax]

Merci pour cet article.
Actuellement je suis en phase de tests sur un SQL Server qui ne rencontre pas de problèmes particuliers, mais le but par la suite sera de surveiller différents serveurs afin à la fois d'éviter l'apparition de problèmes de performances et de pouvoir disposer d'assez d'informations pertinentes pour y remédier si ceux-ci surviennent malgré tout.

[elsuket]

Bonjour,

Envoyé par Keitamax

Je suis arrivé à obtenir ce que je voulais avec un Trace; reste plus qu'à l'automatiser.

Pour l'automatisation de traces côté serveur, vous pouvez regarder le tutoriel que j'ai écrit à ce sujet ici

Vous pouvez également, si vous êtes sous SQL Server 2008, créer une session d'événements étendus comme suit :

Code :

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
CREATE EVENT SESSION requetes_couteuses ON SERVER
ADD	EVENT sqlserver.sql_statement_completed
	(
		ACTION	(sqlserver.sql_text)
		WHERE	(
				reads >= 100000 -- nombres de pages lues par la requête
				OR cpu >= 500 -- temps CPU consommé par la requête, en millisecondes
				OR duration >= 5000000 -- temps total d'exécution de la requête, exprimé en microsecondes
			)
		AND	sqlserver.database_id = n -- n = database_id donné par sys.databases
	)
ADD	TARGET package0.asynchronous_file_target
	(
		SET FILENAME = 'C:\Temp\requetes_couteuses.xel' -- data file path
		, METADATAFILE = 'C:\Temp\requetes_couteuses.xem' -- metadata file path
	)
WITH	(
		EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
		, MAX_DISPATCH_LATENCY = 1 seconds
		, STARTUP_STATE = ON
		, MAX_EVENT_SIZE = 16MB
	)
GO	
 
ALTER EVENT SESSION requetes_couteuses ON SERVER
STATE = START
GO

Cela enregistre toutes les requêtes qui correspondent aux critères que vous avez définis, et les événements étendus sont très légers.
C'est quelque chose que j'utilise en production chez tous les clients de mon entreprise, et personne ne s'en plaint.
Je l'ai automatisé pour produire une session par jour (nécessite du T-SQL dynamique).

Pour supprimer la session (et donc arrêter l'audit), il suffit d'exécuter :

Code :

DROP EVENT SESSION requetes_couteuses ON SERVER

Notez que cela ne supprime pas les fichiers .xel et .xem (qui décrit le .xel), ce qui vous permet de les dépouiller par la suite :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
;WITH 
	CTE (event_data_xml) AS
	(
		SELECT	CAST(event_data AS xml) AS DATA
		FROM	sys.fn_xe_file_target_read_file
			(
				'C:\Temp\requetes_couteuses*.xel'
				, 'C:\Temp\requetes_couteuses*.xem'
				, NULL
				, NULL
			)
	)
SELECT	DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS time_stampe
	, event_data_xml.value('(/event/action/value)[1]', 'nvarchar(max)') AS sql_text
	, event_data_xml.value('(/event/data/value)[4]', 'bigint') AS CPU
	, event_data_xml.value('(/event/data/value)[6]', 'bigint') AS reads
	, event_data_xml.value('(/event/data/value)[7]', 'bigint') AS writes
	, event_data_xml.value('(/event/data/value)[5]', 'bigint') AS duration
FROM	CTE

Bien sûr ceci constitue une solution d'audit à long terme

@++