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.

[elsuket]

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.

Bien vu, effectivement j'ai le même problème avec le lot suivant :

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
CREATE PROCEDURE maProcedure_A
AS
BEGIN
	SET NOCOUNT ON
 
	SELECT 1
END
GO
 
CREATE PROCEDURE maProcedure_B
AS
BEGIN
	SET NOCOUNT ON
 
	EXEC dbo.maProcedure_A
END
GO
 
EXEC dbo.maProcedure_A
GO 10
 
EXEC dbo.maProcedure_B
GO 5

Le requête de dépouillement ne retourne rien ... mais c'est parce que la cible asynchronous_ring_buffer (ainsi que synchronous_ring_buffer)a été remplacée par histogram dans SQL Server 2012.

Voici donc la requête de dépouillement pour SQL Server 2012 :

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 = 'histogram'
	)
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('HistogramTarget/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 '?'

Ce qui fait qu'une définition correcte de la même session pour SQL Server 2012 est donc :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE EVENT SESSION module_call ON SERVER
ADD	EVENT sqlserver.module_start
(
	   ACTION (sqlserver.database_id)
	   WHERE (sqlserver.database_id = 5) -- Pour connaitre le database_id : SELECT DB_ID('maBD')
)
ADD TARGET package0.histogram
(
	 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
)
GO

Merci à vous de l'avoir relevé

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.

Oui, tout à fait. Pour ma part j'utilise la session depuis 8 mois sur tous les serveurs de production de mon employeur, et je collecte par un job qui exécute la requête de dépouillement tous les jours le nombre d'exécution de chaque procédure.
J'ai ensuite une autre requête qui me donne le nombre d'exécutions des procédures stockées par jour, semaine, mois, mais aussi celles qui ne sont plus exécutées et que l'on peut donc supprimer

@++

[mikedavem]

Je pense que les audits SQL Server pour la situation présente est plus adaptée d'autant plus que vous avez un nombre important d'objets à auditer.

Le format proposé en sortie vous permettra facilement de faire un reporting sur ce qui est réellement utilisé ou pas.

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
27
28
29
30
31
32
33
34
35
36
 
-- Création de l'audit + target
USE [master];
GO
 
CREATE SERVER AUDIT [Audit-File]
TO FILE 
(
 FILEPATH = N'D:\Audit'
 ,MAXSIZE = 5 GB
 ,MAX_FILES = 4
 ,RESERVE_DISK_SPACE = ON
)
WITH
(
 QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
-- WHERE (object_name = 'MyObject') -- Si on veut affiner le filtrage 
GO
 
ALTER SERVER AUDIT [Audit-File]
WITH (STATE = ON);
GO
 
-- Création de la spécification d'audit pour la base de données concernée
USE [maBase];
GO
 
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-StoredProcedureUsed] FOR SERVER AUDIT [Audit-File]
ADD (EXECUTE ON DATABASE::[maBase] BY [public])
GO
 
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-StoredProcedureUsed] 
WITH (STATE = ON);
GO

++

[elsuket]

Salut David,

Est-ce que tu peux montrer comment dépouiller l'audit ?

@++

[Donpi]

Et forcément avec ma misérable version Standard de SQL Server, je n'ai pas les audits

[mikedavem]

Et forcément avec ma misérable version Standard de SQL Server, je n'ai pas les audits

SQL Server 2012 propose des audits basiques pour la version standard mais ceux au niveau base de données ne sont pas inclus dans cette édition. Donc effectivement si vous êtes en standard cela ne va pas jouer.

Est-ce que tu peux montrer comment dépouiller l'audit ?

Oui, je dois le faire, excusez-moi mais je n'ai pas eu le temps hier soir

++

[elsuket]

C'est marrant que les XE soient en Standard et pas les Audits, qui reposent sur les XE

Le prix de la version Enterprise ayant explosé du fait du changement de méthode de facturation (au core et non plus au CPU), on comprend que les
entreprises soient réticentes à y passer.

Si je comprend que toutes les fonctionnalités ne soient pas incluses dans la Standard, je trouve odieux qu'un ne puisse pas pouvoir reconstruire les index en ligne ... mais c'est un autre débat.

@++

[mikedavem]

Voila pour l'extraction (pour le fun) qui nous permet de remonter les procédures stockées non utilisées et celles qui le sont avec leur nombre d'exécution.

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

C'est marrant que les XE soient en Standard et pas les Audits, qui reposent sur les XE

Les audits sur SQL Server 2012 sont bien disponibles en version standard mais la fonctionnalité est bridée.

Le prix de la version Enterprise ayant explosé du fait du changement de méthode de facturation (au core et non plus au CPU), on comprend que les
entreprises soient réticentes à y passer.

Tout dépend le nombre de coeurs que l'on a mais effectivement les processeurs devenant des octocores et plus le choix d'une édition Enterprise doit être réfléchie ... enfin tout dépend bien sûr les accords passés avec Microsoft

Si je comprend que toutes les fonctionnalités ne soient pas incluses dans la Standard, je trouve odieux qu'un ne puisse pas pouvoir reconstruire les index en ligne ... mais c'est un autre débat.

Oui c'est un autre débat en effet mais disons que les besoins de reconstruction d'index en ligne restent très faible. La plupart des entreprises peuvent se permettre d'avoir une fenêtre de maintenance qui leur permettre une reconstruction hors ligne.

++