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 :
- Retrouver la requête à l’origine d’une erreur 8623 “The query processor ran out of internal resources and could not produce a query plan” (David Baffaleuf) [SQL Server]
- Limiter la PGA totale en 12c (Benjamin VESAN) [Oracle]
- SQLDIAG (épisode 1) (David Baffaleuf) [SQL Server]
- Elastic Job Agent : l’Agent SQL Server pour le PaaS Azure (Vincent Delabre) [AzureSQL Server]
- Formation Optimisation de requêtes (David Baffaleuf) [SQL Server]