One trick I’ve used for quite a while is to have stored procs “log” information to a table with the structure…
XX.SYS_PROC_AUD ( PROC_X, ACTIVITY_X, CREATED_TS, ERROR_NB )
…which is great for seeing what’s going on across the board, on a proc by proc basis, or looking for specific things. But what about when management request “metrics” about how things are running? Well, since I already log stop and start of the procs to the aforementioned table, you can use a WITH query to help get run times of procs and even between procs without resorting to Excel like so…
WITH rows AS ( SELECT ROW_NUMBER() OVER (ORDER BY CREATED_TS) AS rn, CREATED_TS, PROC_X FROM XX.SYS_PROC_AUD WHERE ( PROC_X = 'XX.FIRST_PROC_P' AND ACTIVITY_X = 'Starting Exec' ) OR ( PROC_X = 'XX.LAST_PROC_P' AND ACTIVITY_X = 'Finished Exec' ) ORDER BY CREATED_TS DESC FETCH FIRST 1000 ROWS ONLY WITH UR ) SELECT mc.CREATED_TS as START_TS, mp.CREATED_TS as END_TS, TIMESTAMPDIFF(2, CHAR(mp.CREATED_TS - mc.CREATED_TS)) AS SEC, TIMESTAMPDIFF(2, CHAR(mc.CREATED_TS - mpe.CREATED_TS)) AS SEC_BETWEEN_LAST FROM rows mc INNER JOIN rows mp ON mc.rn = mp.rn - 1 INNER JOIN rows mpe ON mc.rn = mpe.rn + 1 WHERE mc.PROC_X = 'XX.FIRST_PROC_P' ORDER BY mc.CREATED_TS DESC FETCH FIRST 100 ROWS ONLY WITH UR FOR READ ONLY
Note: I prefer to see the most recent things first so you may need to adjust your ordering appropriately if you don’t like that setup.
Thanks to this post for getting me started.Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon 🙂
You or someone you know looking to buy or sell?