Profiling procédures stockées

Version imprimable

26/02/2015, 10h04
djchou

Profiling procédures stockées

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
26/02/2015, 10h30
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
26/02/2015, 14h34
djchou

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

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 ? :calim2:

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:

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

26/02/2015, 16h12
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:

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...

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:

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:

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:

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 ...

++

28/02/2015, 18h47
SQLpro

Et pourquoi pas plus simplement le profiler SQL ?

A +
03/03/2015, 07h38
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.

++