SQL Server 2012 : Procedures stockées et Statistiques

Version imprimable

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:

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

18/06/2012, 12h12
mikedavem

Bonjour,

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

++

Bonjour,

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

Code:

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:

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:

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:

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 ;)

@++ ;)

19/06/2012, 17h06
Donpi

Cool merci,

mais ça ne semble pas marché... le résultat est toujours vide
19/06/2012, 18h15
elsuket

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

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

@++ ;)
20/06/2012, 07h53
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.

Citation:

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:

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:

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:

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é ;)

Citation:

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 ;)

@++ ;)

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:

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

21/06/2012, 06h04
elsuket

Salut David,

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

@++ ;)
21/06/2012, 07h48
Donpi

Et forcément avec ma misérable version Standard de SQL Server, je n'ai pas les audits :calim2:
21/06/2012, 07h49
mikedavem

Citation:

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.

Citation:

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 :aie:

++
21/06/2012, 13h17
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.

@++ ;)

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:

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
Citation:

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.

Citation:

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 ;)

Citation:

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

++