Discussion :

[djchou]

Bonjour tout le monde,

j'aurais voulu savoir si quelqu'un connait un outil pour connaitre facilement si les procédures stockées d'une bdd sont utilisées ou non
(afin de supprimer les procédures qui ne sont plus utilisées)

Merci d'avance

[julien94320]

Bonjour,

la solution la plus simple serait de se tourner vers l'Audit SQL Server https://msdn.microsoft.com/fr-fr/library/cc280386.aspx.

Un sujet similaire avait été évoqué en 2012 :

http://www.developpez.net/forums/d12...-statistiques/

Bon courage

[djchou]

Super ! Merci c'est exactement ce qu'il me faut

Mais j'ai une ptite question tout de même par rapport aux server audit , la solution proposée par mikedavem :

Je suis sur SQL Server 2012 édition standard, je peux créer le serveur audit mais pas extraire ses data !

J'ai le message suivant :
L'audit granulaire n'est pas disponible dans cette édition de SQL Server. Pour plus d'informations sur la prise en charge des fonctionnalités dans les différentes éditions de SQL Server, consultez la documentation en ligne de SQL Server.

Est ce normal de pouvoir créer un serveur audit et ne pas pouvoir en extraire les données? y a t il une autre solution pour extraire les données ?

[julien94320]

Effectivement la version Standard est bridé c'était évoqué dans mon deuxième liens :

cependant il y a peut etre une parade (extrait du deuxième liens) :

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
WITH stored_procedure_used
AS
(
SELECT 
 af.schema_name + '.' + af.object_name AS [stored_procedure],
COUNT(*) AS nb_executions
FROM sys.fn_get_audit_file('D:\Audit\*', DEFAULT, DEFAULT) AS af
INNER JOIN test.sys.objects AS o
 ON o.object_id = af.object_id
INNER JOIN sys.schemas AS s
 ON s.name = af.schema_name
WHERE af.action_id = 'EX'
 AND o.type_desc = 'SQL_STORED_PROCEDURE'
GROUP BY af.schema_name + '.' + af.object_name
)
SELECT 
 s.name + ' ' + o.name AS [stored_procedure],
 0 AS nb_executions
FROM test.sys.objects AS o
INNER JOIN sys.schemas AS s
 ON o.schema_id = s.schema_id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
 AND NOT EXISTS (SELECT * 
                 FROM stored_procedure_used AS su
				 WHERE su.stored_procedure = s.name + '.' + o.name)
UNION ALL 
SELECT * 
FROM stored_procedure_used

[djchou]

Oui j'ai bien vu le lien justement... mais cette extraction ne peut pas marcher dans la mesure ou tu ne peux pas associer ton audit sur la base de ton choix avec

Code :

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

1
2
3
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-StoredProcedureUsed] FOR SERVER AUDIT [Audit-File]
ADD (EXECUTE ON DATABASE::[maBase] BY [public])
GO

Donc l'extraction ne donne rien...

[mikedavem]

Hello,

Avec 2012 tu peux utiliser les événements étendus pour ce besoin. L'idée est de compter le nombre d'occurrences d'apparition des modules de type P (exécution d'une procédure stockée) dans une cible de type histogramm.

Par exemple:

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
CREATE EVENT SESSION [sp_tracking] 
ON SERVER 
ADD EVENT sqlserver.module_end
(
    ACTION(sqlserver.database_name)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'<mabase>') 
		   AND [object_type]='P ')
) 
ADD TARGET package0.histogram
(
	SET filtering_event_name=N'sqlserver.module_end',
	slots=(10000),
	source=N'object_name',
	source_type=(0)
)
WITH 
(
	MAX_MEMORY = 4096 KB,
	EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY = 30 SECONDS,
	MAX_EVENT_SIZE = 0 KB,
	MEMORY_PARTITION_MODE = NONE,
	TRACK_CAUSALITY = OFF,
	STARTUP_STATE = OFF
)
GO

Il suffit de remplacer ma <mabase> par ta base concernée.

Pour démarrer la trace il suffit d'exécuter la requête suivante:

Code :

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

1
2
3
ALTER EVENT SESSION [sp_tracking] ON SERVER 
WITH STATE = START;
GO

ou utiliser éventuellement l'interface graphique Management > Extended Events > Sessions > Ta session > Clic droit - Start Session

Pour récupérer le résultat:

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
WITH sp_traking_xe
AS
(
	SELECT 
		CAST(t.target_data AS XML) AS target_data
	FROM sys.dm_xe_sessions AS se
	JOIN sys.dm_xe_session_targets AS t
		ON se.address = t.event_session_address
	WHERE se.name = 'sp_tracking'
)
SELECT 
	T.x.value('(./value/text())[1]', 'SYSNAME') AS proc_name,
	T.x.value('(.//@count)[1]', 'BIGINT') AS nb_occurences
FROM sp_traking_xe
CROSS APPLY target_data.nodes('/HistogramTarget/Slot') AS T(x)

ou utilisation de l'interface graphique au choix ...

++

[SQLpro]

Et pourquoi pas plus simplement le profiler SQL ?

A +

[mikedavem]

Possible en effet mais l'impact sur les performances sera plus important que les événements étendues dans ce contexte. Ce que j'aime bien avec les événements étendues ici, c'est la possibilité d'utiliser une cible qui ne fait que compter au lieu d'avoir à stocker probablement une grande quantité de données via le profiler et agréger par la suite. On peut également jouer avec les options de sessions pour augmenter les buffers allouées à l'enregistrement des événements si jamais l'activité de la base est importante pour minimiser l'impact sur les performances, chose que l'on ne peut pas faire avec SQL Profiler. Je pense notamment aux options MAX_MEMORY et MEMORY_PARTITION_MODE.

++