SQL measure stored procedure execution time

Question

I would like to measure the time it take to execute a Stored Procedure in SQL Server. But instead of just measuring the entire stored procedure execution I would also like to measure the time each operation inside the stored procedure takes, so I could find bottlenecks.

In c# to accomplish such a thing when testing code execution time I would save the date before execution starts and when execution ends I would print the TimeSpan object that is given to me by subtracting the start time from the current time (after execution).

I was wondering how I could achieve such thing with SQL Server inside a stored procedure where I could print the time span I measure between operation within the stored procedure.

Thanks

Answer 1

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

EXECUTE <yourSP>;

GO

Answer 2

What worked for me was the suggestion posted here: https://www.codeproject.com/tips/1151457/a-simple-method-to-measure-execution-time-in-sql-s

Declare @StartTime DateTime2

SET @StartTime = SysUTCDateTime()

-- SQL Execution

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, SysUTCDateTime()) as varchar) + 'ms'

SET @StartTime = SysUTCDateTime()

-- Second SQL Execution

Print 'Time taken was ' + cast(DateDiff(millisecond, @StartTime, SysUTCDateTime()) as varchar) + 'ms'