Sample Header Ad - 728x90

Get current execution metrics from sys.dm_exec_sessions

0 votes
1 answer
374 views
I've got a pretty complex batch processing procedure that takes hours to run on real data. I've found that some CPU and I/O counters are stored in the master.sys.dm_exec_sessions table, and so thought that maybe I can use them to measure which parts of the procedure are taking most resources. However, it seems that repeatedly querying this table within a single procedure always returns the same values. I know I can trigger update of these statistics by issuing the GO statement, but of course it is not possible to do that inside a procedure. Are there any other ways to read these counters that would make sure they are up to date? MWE: CREATE OR ALTER PROCEDURE dbo.MineBitcoins @Prefix INT AS BEGIN DECLARE @It INT = 0 WHILE COALESCE(TRY_CAST(LEFT(HASHBYTES('SHA2_256', HASHBYTES('SHA2_256', CONVERT(VARCHAR, @It))), @Prefix) AS INT), 1) 0 BEGIN PRINT @It SET @It = @It + 1 END SELECT @It END GO CREATE OR ALTER PROCEDURE dbo.MeasureTimeOfAStupidQuery @Prefix INT AS BEGIN SELECT cpu_time, reads, logical_reads, writes FROM master.sys.dm_exec_sessions WHERE session_id = @@SPID EXEC dbo.MineBitcoins @Prefix SELECT cpu_time, reads, logical_reads, writes FROM master.sys.dm_exec_sessions WHERE session_id = @@SPID END GO EXEC dbo.MeasureTimeOfAStupidQuery @Prefix = 3 (it takes about 30 seconds on my test database instance).
Asked by liori (289 rep)
Aug 25, 2021, 11:54 AM
Last activity: Jun 4, 2025, 02:03 AM