Discussion :

[Donpi]

Bonjour,

J'ai 900 procédures stockées, certaines sont des copies de copies préfixées avec 1, 2, test, toto, tata etc...
J'aimerais trouver celles qui ne sont plus utilisées.

Mon problème est qu'en parcourant le net, je ne trouve que des scripts qui retourne des information de ce qui est dans le cache.
Alors que ce que je recherche c'est des données sur toutes les procédures stockées y compris celle qui n'ont pas été utilisées depuis plusieurs mois.

Y-a-t il une solution ?

Je ne trouve que des variantes de ça

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
SELECT 
	DB_NAME(qt.dbid) DBName
	,AO.type
	,AO.name
	,Qt.text AS Request
	,qs.execution_count AS Execution_Count
	,qs.total_worker_time/ISNULL(qs.execution_count, 1) AS AvgWorkerTime
	,qs.total_worker_time AS TotalWorkerTime
	,qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS AvgElapsedTime
	,qs.max_logical_reads
	,qs.max_logical_writes
	,qs.creation_time
	,DATEDIFF(Minute, qs.creation_time, GetDate()) AS Age_In_Cache
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
left outer join sys.all_objects AO on AO.object_id=QT.objectid
WHERE  DB_NAME(qt.dbid)='MaBase'
ORDER BY Execution_Count

[mikedavem]

Bonjour,

Personnellement je me tournerai vers les audits SQL Server pour faire cela.

++

[elsuket]

Bonjour,

Pareil que Mikedavem, surtout si puisque vous êtes en SQL Server 2012.
Ou alors une petite session d'événements étendus :

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
CREATE EVENT SESSION module_call ON SERVER
ADD	EVENT sqlserver.module_start
(
	   ACTION (sqlserver.database_id)
	   WHERE (sqlserver.database_id = 6) -- Pour connaitre le database_id : SELECT DB_ID('maBD')
)
ADD TARGET package0.asynchronous_bucketizer
(
	 SET	  filtering_event_name = 'sqlserver.module_start'
			  , source_type = 0 -- type de la source : 0: nom de l'événement, 1: nom de l'action
			  , source = 'object_name'
			  , slots = 1024 -- nombre maximal d'étapes dans l'histogramme
)
 
-- Démarrer la session
ALTER EVENT SESSION module_call ON SERVER
STATE = START
GO

On peut ensuite dépouiller ce que la session a collecté :

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
;WITH
	CTE AS
	(
		SELECT		CAST(T.target_data AS xml) AS target_data
		FROM		sys.dm_xe_sessions AS S
		INNER JOIN	sys.dm_xe_session_targets AS T
					ON S.address = T.event_session_address
		WHERE		S.name = 'module_call'
		AND		T.target_name = 'asynchronous_bucketizer'
	)
SELECT		bucket.value('./value[1]', 'sysname') AS module_name
		, bucket.value('(@count)[1]', 'int') AS call_count
		--, O.type_desc
FROM		CTE
CROSS APPLY	target_data.nodes('BucketizerTarget/Slot') AS BZ(bucket)
INNER JOIN	sys.objects AS O
			ON bucket.value('./value[1]', 'sysname') = O.name
WHERE		bucket.value('./value[1]', 'sysname') NOT LIKE 'sp?_MS%' ESCAPE '?'

Pour arrêter la session :

Code :

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

1
2
3
ALTER EVENT SESSION module_call ON SERVER
STATE = STOP
GO

On peut la redémarrer par la suite en remplaçant STOP par START.
Et pour la supprimer une fois arrêtée :

Code :

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

1
2
DROP EVENT SESSION module_call ON SERVER
GO

Pour en savoir plus sur les événements étendus avec SQL Server 2008 et suivants, vous pouvez vous référer à l'article que j'ai publié, avec l'aide de Mikedavem

@++

[Donpi]

Cool merci,

mais ça ne semble pas marché... le résultat est toujours vide

[elsuket]

Avez-vous changé le database_id par celui de laquelle vous souhaitez auditer ?
C'est une erreur que je fais souvent ...

Notez aussi que le dépouillement doit se faire dans le contexte de la base de données auditée (USE maBD).

@++

[Donpi]

Oui j'ai changer la db id.


C'est que si j’appelle la procédure A 10 fois de suite, je n'ai rien dans le résultat tant que je n'ai pas appelé la procédure B. À ce moment là je n'ai que le nombre d’exécutions de la procédure A, il faut que j'appelle une autre procédure pour avoir les données dans le résultat. Bon je peu vivre avec ça.

J'ai encore juste une question, est ce que c'est quelque chose qui peut tourner indéfiniment?
Car pour être sur, il faudrait que j'analyse 3 ou 4 mois.