0

Déterminer la fréquence horaire d’exécution d’une procédure stockée sous SQL Server

twitterlinkedinmail

Pour des raisons purement métier, il m’a été demandé le nombre d’exécutions horaires d’une procédure stockée “métier”. Le but était d’identifier les variations d’utilisation de la plateforme, sans se reposer sur des indicateurs trop techniques (charge CPU, batch request/sec , concurrent sessions, etc…)

Il y a différents moyens qui peuvent être plus ou moins fiables, dans certaines DMV ou avec le Data Collection, mais rien ne me convenait en terme de fiabilité et d’élégance.

Et puis j’ai repensé à quelque chose que j’utilise encore trop peu : les Evenements Etendus (Extended Events). Je m’en sers peu car, par habitude, la bonne vieille TRACE (et son outil dédié, le SQL Profiler) me conviennent pour la plupart des cas. Mais la Trace a un impact en terme de performance supérieur aux Extended Events. Et puis pour des comptages, il aurait fallu faire une usine à gaz, où on aurait stocké en table, puis exécuté un job qui aurait identifié les exécution de la procédure stockée, et insérer dans une table le résultat du comptage… Bref : pas élégant.

Les Extended Events ont beaucoup progressé depuis leur création, au point de couvrir l’intégralité du périmètre de ce que peut faire une Trace, sur les dernières version de SQL Server. Et bien plus encore !

Les Extended Events travaillent à bas niveau, et n’impactent pas autant le moteur en terme de performance. On peut aussi décider facilement de la destination de l’Extended Events : une table, un fichier, un ring buffer (pour consommation immédiate)… Mais aussi un compteur !

Nous voila donc à faire la manœuvre suivante :


CREATE EVENT SESSION [MySpCounter] ON SERVER
ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
ACTION(sqlserver.database_name,sqlserver.sql_text)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%exec dbo.GetUserByReput%')))
ADD TARGET package0.event_counter
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=ON)
GO
ALTER EVENT SESSION MySpCounter ON SERVER STATE = START ; 

Ici, je trace donc sur le serveur l’exécution de la procédure dbo.GetUserByRep (suivi d’un % pour récupérer tout ce qui pourrait être placé en paramètre).

Pour pouvoir exploiter le résultat de cette Extended Events, j’ai besoin d’une petite table qui contiendra les informations :


CREATE TABLE [dbo].[MySPCounter](
[id] [int] IDENTITY(1,1) NOT NULL,
[measure_date] [datetime] NOT NULL,
[value] [int] NOT NULL )

Celle-ci contient donc un identifiant (au cas où…), une date de mesure de l’Extended Event, et la valeur du compteur.

Ensuite, je fais un job dans l’agent qui ira lire la valeur de ce compteur, l’insérera dans la table et remet enfin le compteur à zéro. Ici, je ne mettrai pas le code de création du job, mais juste de son contenu :


declare @MyValue int
select @MyValue = execution_count FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'MySpCounter' and target_name = 'event_counter'

INSERT INTO msdb.dbo.MySPCounter values (getdate(), @MyValue)

alter event session MySpCounter ON server STATE = STOP ;
alter event session MySpCounter ON server STATE = START;

On retrouvera donc une table où à intervalle régulier, on aura le nombre d’exécution de notre procédure stockée ! Il n’y a plus qu’à connecter un Excel, un PowerBI ou l’outil que vous préférez, et vous pourrez faire de jolis graphiques qui raviront vos responsables métiers.

 

Continuez votre lecture sur le blog :

twitterlinkedinmail

Vincent Delabre

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.