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
Last activity: Jun 4, 2025, 02:03 AM